Sponsored Content
Top Forums Programming DB2 - Performance Issue using MERGE option Post 303018332 by rbatte1 on Monday 4th of June 2018 07:03:36 AM
Old 06-04-2018
I expect that this is doing what you asked, and although it does seem a sensible query, it is not being very practical. I think that your request is going to build some huge temporary tables to work out the various clauses before applying any inserts/updates. You may even fill your temporary table tablespace before your MERGE completes this step.


You might have some success with indices though. What indices do you have on table schemaname.Customer_Staging and schemaname.Customer_Staging?
  • If you don't have a single index for the three columns you mention in the SELECT DISTINCT, then you might do a full table scan of schemaname.Customer_Staging.
  • If you don't have a single index for all the columns in the whole query (e.g A.Name, B.Name, A.Load_dt etc. ) then you might do a full table scan of the appropriate table.
Any reason to do a full table scan that these scales will be bad. An index might take a while to build and needs to have space, but then your MERGE should run better. I've had something that took me 20 minutes to build an index then the process I wanted ran in about an hour, after which I dropped the index again.because it was a one-off report. The run without the index was abandoned after over 22 hours (someone set if off and went home)

Is there some sort of query profiler you can use to consider this? MSSQLServer has one, Oracle has one so I'm sure that DB2 will have one, but I've not ever used it. You need to avoid a full table scan when dealing with this volume of data.



I hope that this helps,
Robin

Last edited by rbatte1; 06-04-2018 at 08:03 AM.. Reason: Spelling correction.
 

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... (5 Replies)
Discussion started by: shibz
5 Replies

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... (2 Replies)
Discussion started by: baskivs
2 Replies

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 ::::... (1 Reply)
Discussion started by: msp2244
1 Replies

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... (6 Replies)
Discussion started by: neelmani
6 Replies

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... (1 Reply)
Discussion started by: suresh_target
1 Replies

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... (1 Reply)
Discussion started by: bhaskar v
1 Replies

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 $? ... (13 Replies)
Discussion started by: vamsi.valiveti
13 Replies

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... (2 Replies)
Discussion started by: Perlbaby
2 Replies

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... (7 Replies)
Discussion started by: green_k
7 Replies
STAP-MERGE(1)						      General Commands Manual						     STAP-MERGE(1)

NAME
stap-merge - systemtap per-cpu binary merger SYNOPSIS
stap-merge [ OPTIONS ] [ INPUT FILENAMES ] DESCRIPTION
The stap-merge executable applies when the -b option has been used while running a stap script. The -b option will generate files per-cpu, based on the timestamp field. Then stap-merge will merge and sort through the per-cpu files based on the timestamp field. OPTIONS
The systemtap merge executable supports the following options. -v Verbose mode, displays three extra fields per set of collected data. [cpunumber,sequencenumberofdata,thelengthofthedataset] -o OUTPUT_FILENAME Specify the name of the file you would like the output to be redirected into. If this option is not specified than the output will be pushed to standard out. EXAMPLES
$ stap -v -b -e 'probe syscall.open { printf("%s(%d) open ", execname(), pid()) }' This should result in several stpd_cpu files (each labled with a number representing which cpu the file was produced from). $ stap-merge -v stpd_cpu0 stpd_cpu1 Running the stap-merge program in the same directory as the stap script earlier in the example, will produce an ordered sequence of packets with the three part label for each set of data. This result will be pushed through the standard output. An output file could have been specified using the "-o" option. FILES
Important files and their corresponding paths can be located in the stappaths(7) manual page. SEE ALSO
stapprobes(3stap), stappaths(7), staprun(8), stapvars(3stap), stapex(3stap), stap-server(8), gdb(1) BUGS
Use the Bugzilla link of the project web page or our mailing list. http://sourceware.org/systemtap/,<systemtap@sourceware.org>. STAP-MERGE(1)
All times are GMT -4. The time now is 02:57 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy