DB2 - Performance Issue using MERGE option Post: 303018330

Sponsored Content

Top Forums Programming DB2 - Performance Issue using MERGE option Post 303018330 by Perlbaby on Monday 4th of June 2018 05:39:54 AM

06-04-2018

Registered User

DB2 - Performance Issue using MERGE option

Dear Team,
I am using DB2 v10.5 and trying to load huge data using MERGE option ( 10-12 Million) using below query. Basically it loads data from staging to target . Staging table schemaname.Customer_Staging has 12 Million records . Runs for 25 Mins for just select query. But while doing merge (first time load) , it has to insert all records and below process is getting hanged after running 1.5-2 hrs (With No inserts)

HTML Code:

   MERGE INTO schemaname.Customer_Table A
    USING ( SELECT DISTINCT B.Name , B.CUST_ID, B.ORG_ID from schemaname.Customer_Staging B 
    WHERE (B.Name is not null and length(B.Name)>0) and (B.CUST_ID is not null and length(B.CUST_ID)>0) and (B.ORG_ID is NOT null and length(B.ORG_ID) > 0)) B
     ON B.Name = A.Name and B.CUST_ID=A.CUST_ID and B.ORG_ID=A.ORG_ID and A.Load_dt is null
    WHEN NOT MATCHED THEN INSERT (Name,CUST_ID,Load_dt,ORG_ID)  
    VALUES(B.Name,B.CUST_ID,current timestamp - current timeZone,B.ORG_ID);

DDL columns for both staging and Target is same .
Included Unique index for three columns Name,CUST_ID,ORG_ID using

Code:

ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC option

Please can someone help how to tweak this query or use best approach.
any help appreciated
Thanks

Perlbaby

View Public Profile for Perlbaby

Find all posts by Perlbaby

9 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Performance issue

Hello all, I just stuck up in an uncertain situation related to network performance... I am trying to access one of my remote client unix machine from a distant location.. The client machine is Ultra-5_10 , with SunOS 5.5.1 The ndd result ( hme1 )shows that the machine is hooked to a...

2. Shell Programming and Scripting

DB2 Connect issue

Hi i m trying to connect DB2 via unix. it is successfully connect. but the connect is getting disconnect . below is the query , countvalue=$(db2 "connect to <Database> user <username> using <Password>" | db2 -x 'select count(*) from <tablename>' ); echo $countvalue while...

3. Shell Programming and Scripting

Unix and db2 where condition issue(new line)

Hi I am extracting a column value(DESCRIPTION) from one table and passing it to another db2 statement in a shell code to fetch some value(ID) but the value when passed in where condition is taking as newline+value. Please find the out put when executed: + echo description is ::::...

4. Shell Programming and Scripting

issue with grep -B option

hi friends, i have a file where every word is present in a new line for example: more file1: i want to fetch previous line wherever i am getting "as" as a keyword. i tried at home the follwing code in linex: grep -B 1 "as" file1 ouput: caste caste1 it was working!! but now i am...

5. Shell Programming and Scripting

/db2home full issue in db2

Hi all, I am new for linux environment, and i am working as a DBA. I am facing some issues in OS level: In our dev boxes /db2home under this directory i'm not finding any folder but it's showing 98% used . /dev/dm-14 5.0G 4.6G 115M 98% /db2home # ls -lrt total 16...

6. Shell Programming and Scripting

Issue on executing db2 queries through shell script

hi i am trying to execute db2 queries through shell script. it's working fine but for few queries is not working ( those queries are taking time so the script is not waiting to get the complete the execution of that query ) could you please any one help me on this is there any wait...

7. Shell Programming and Scripting

UNIX with DB2 error status Issue

I have a shell script main.ksh We are calling dbscript.ksh from main.ksh I am using select statement in dbscript.ksh but there is a problem with the select statement in dbscript.ksh but still echo $? is showing as zero. I am using DB2 commands in dbscript.ksh Main.ksh dbscript.ksh echo $? ...

8. Programming

DB2 - Determine Cost Savings in USD - After performance Tuning

Dear Team Have this interesting question on how to determine cost savings(USD) based on performance tuning in Db2 I am using DB2 v10.5 . I worked on db2 procedure that loaded 20Million records in just 2 Mins. ETL execution time reduced from 30 Mins to 2 Mins. From 15 Hrs Monthly to 1...

9. Shell Programming and Scripting

Best performance to merge two files

Hi Gurus, I need to merge two files. file1 (small file, only one line) this is first linefile2 (large file) abc def ghi ... I use below command to merge the file, since the file2 is really large file, the command read whole file2, the performance is not good. cat file1 > file3...

LEARN ABOUT DEBIAN

cobertura-merge

COBERTURA-MERGE(1)					      General Commands Manual						COBERTURA-MERGE(1)

NAME

       cobertura-merge -- merge multiple data files into a single data file

SYNOPSIS

       cobertura-merge [--datafile file]  datafile [...]

DESCRIPTION

       cobertura-merge can be run after your JUnit tests to merge multiple data files into a single data file.

OPTIONS

       --datafile file
		 Specify  the  name  of the file containing metadata about your classes. This is the "destination" file into which the contents of
		 the other data files will be merged. Default value: "./cobertura.ser".

SEE ALSO

       junit(1), cobertura-instrument(1), cobertura-report(1), cobertura-check(1).

AUTHOR

       This manual page was written by Miguel Landaeta <miguel@miguel.cc> for the Debian system (but  may  be  used  by  others).   Permission	is
       granted	to copy, distribute and/or modify this document under the terms of the terms of GNU General Public License, Version 2 or any later
       version published by the Free Software Foundation.

       On Debian systems, the complete text of the GNU General Public License can be found in /usr/share/common-licenses/GPL.

																COBERTURA-MERGE(1)