Performance Tuning In DB2 Query - Help

itjagadesh · New User Joined: 05 Dec 2006 Posts: 89 Location: chennai

Hi Team,
I need some tips on below query to improve performance.
Below mentioned query Unloads data from DB2 Table to flat file and it is having two DB2 tables INVOICE and INVC_PROCESS_LOG.
But below query takes 75 minutes to unload the data.How to reduce CPU time?

Is there anything needs to improve query performance for below mentioned?Any suggestion of this like just unload as flat file and then process in COBOL\JCL like?
Note --
No of records in above table as follow.
324266786
43783761

UNLOAD TABLESPACE
LOCK NO
QUIESCE NO
SELECT A.VENDOR_NBR,
A.INVOICE_ID,
A.INVOICE_NBR,
A.INVOICE_DATE,
A.TOTAL_AMT,
A.DIV_NBR,
A.STORE_NBR,
A.EDI_TRANS_SRC_CD,
A.INV_PROC_AREA_CODE,
DATE(B.PROCESS_STATUS_TS)
FROM TAB1.INVOICE A,
(SELECT C.INVOICE_ID
,MAX(C.PROCESS_STATUS_TS) AS PROCESS_STATUS_TS
FROM TAB1.INVC_PROCESS_LOG C
WHERE C.PROCESS_STAT_CODE = 9
AND NOT EXISTS
(SELECT 1
FROM TAB1.INVC_PROCESS_LOG D
WHERE D.PROCESS_STAT_CODE IN (13, 996, 997, 998, 999)
AND D.INVOICE_ID = C.INVOICE_ID
)
GROUP BY C.INVOICE_ID
) AS B
WHERE A.INVOICE_ID = B.INVOICE_ID
WITH UR
OUTDDN (SYSREC00)
FORMAT DSNTIAUL
LOADDDN SYSPUNCH LOADOPT (REPLACE, LOG NO, KEEPDICTIONARY)
let me know your suggestion to impove the performance by modifying query or any other method?

Thanks In advance
Jagadesh

enrico-sorichetti · Posted: Thu Nov 20, 2008 3:54 pm

deleted the post with attachment,
here is the query with the code tags

dick scherrer · Posted: Fri Nov 21, 2008 1:59 am

Hello,

itjagadesh · New User Joined: 05 Dec 2006 Posts: 89 Location: chennai

Hi dick scherrer,

Thanks

I verified my Job status again.Please find the details below for this particular Unload Step.

dick scherrer · Posted: Fri Nov 21, 2008 9:02 am

Hello Jagadesh,

You're welcome

This is from a job a ran a little while ago:
IEF375I JOB/J07525Y0/START 2008325.1908
IEF376I JOB/J07525Y0/STOP 2008325.1954 CPU 0MIN 16.38SEC

There is also a IEF373I/IEF374I message pair for the start/end of step.

Please post those messages from your job.

itjagadesh · New User Joined: 05 Dec 2006 Posts: 89 Location: chennai

Hi Dick,

Please find the information below

IEF373I STEP/DB2UNLD /START 2008319.1841
IEF374I STEP/DB2UNLD /STOP 2008319.2019 CPU 67MIN 09.71SEC

IEF375I JOB/APGB444 /START 2008319.1828
IEF376I JOB/APGB444 /STOP 2008319.2223 CPU 120MIN 47.90SEC

Thanks
Jagadesh

dick scherrer · Posted: Fri Nov 21, 2008 10:26 am

Hi Jagadesh,

Interesting - the problem step runs 1hr 38min (98 mins) and still gets 67+ minutes of cpu.

How many records are created in the unload file?

Have you had a chance to talk with the dba yet? There may be some measurement tools the dba has that could identify bottlenecks, but only the dba would know.

itjagadesh · New User Joined: 05 Dec 2006 Posts: 89 Location: chennai

Hi Dick,

The Job contains 12 steps and enitre 12 steps took 120 minutes and in that STEP2(Our Unload step alone) took nearly 70 minutes.

How many records are created in the unload file?

6260472

Have you had a chance to talk with the dba yet?
Right now not able to contact with DBA.Anyway thanks for your qucik response Dick .

Thanks
Jagadesh

dick scherrer · Posted: Fri Nov 21, 2008 10:45 am

Hi Jagadesh,

One last thought for tonight

How long does it take to simply unload the 300+ million row table (without the multi-table query)?

itjagadesh · New User Joined: 05 Dec 2006 Posts: 89 Location: chennai

Hi Dick,

i ran separately for unloading each table

TAB1.INVC_PROCESS_LOG
No OF Records - 335491349
TOTAL TCB CPU TIME= 2.46 (time to unload )

TAB1.INVOICE
No of records -- 45350708
TOTAL TCB CPU TIME= .52 (time to unload)..

So unload two table takes -- 3.38 minutes totally(2.46 + 0.52)
But above multiple query takes --- 70 minutes.

Thanks
Jagadesh

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

Jagadesh,

You are using a corelated query a MAX function etc which conume a good amount of time Your correlated subquery refers to at least one column of the outer query. Correlated subquery is evaluated for each qualified row of the outer query that is referred

How about using a Join instead of Subqueries. Joins are more prefered over subqueries in terms of performance

I would suggest a creation of index on columns which you do a group by or you do a join .In your case I guess an index on INVOICE_ID on tables would improve the performance

Suggestions welcome

dick scherrer · Posted: Fri Nov 21, 2008 9:58 pm

Hello,

As Prasanth suggests, joins would probably perform better than subqueries. Proper index(es) are also extremely important.

If the process still takes too long, you might consider "driving" the process with qsam data and then reference the database rather than do the entire task in one complex query.

itjagadesh · New User Joined: 05 Dec 2006 Posts: 89 Location: chennai

Hi Dick/prshant,

Thanks for your info above.
Please find the attached document contains Index details for two table
Table1 contains 5 index and Table 2 contains 2 index.
Can we do something on this ?

Query -
In above Table1 contains 5 index and table2 contains 2 index..at the time of run which table will be used by DB2?

Thanks
Jagadesh

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

How about doing an explain on your query and checking which all index is getting used

itjagadesh · New User Joined: 05 Dec 2006 Posts: 89 Location: chennai

Hi Prashant,

How about doing an explain on your query and checking which all index is getting used?

Really iam not clear on this.can u tell me above above?

Sorry for inconvenience

Thanks
Jagadesh

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

Check this links

publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/rexpl.htm

endwaymainframe.blogspot.com/2006/02/using-explain-in-db2.html

ashimer · Posted: Tue Nov 25, 2008 10:13 pm

Hi,

The Query which you are joining with INVOICE is taking more time because of the NOT EXISTS clause and that too with a correlated subquery ... if im not wrong you want all invoice id and max of TS where the process code is 9 and the process code is not in (13, 996, 997, 998, 999) ..pls correct me if i am wrong ...if this is the case rewrite your query as

itjagadesh · New User Joined: 05 Dec 2006 Posts: 89 Location: chennai

Hi Ashimer,

I tried with below query but im getting different output.
Due to below query change i guess...

Modidfed Query--
(SELECT
C.INVOICE_ID
,MAX(C.PROCESS_STATUS_TS) AS PROCESS_STATUS_TS
FROM TAB1.INVC_PROCESS_LOG C
WHERE C.PROCESS_STAT_CODE = 9
AND C.PROCESS_STAT_CODE
NOT IN (13, 996, 997, 998, 999)
GROUP BY C.INVOICE_ID
) AS B

Original Query--

(SELECT C.INVOICE_ID
,MAX(C.PROCESS_STATUS_TS) AS PROCESS_STATUS_TS
FROM TAB1.INVC_PROCESS_LOG C
WHERE C.PROCESS_STAT_CODE = 9
AND NOT EXISTS
(SELECT 1
FROM TAB1.INVC_PROCESS_LOG D
WHERE D.PROCESS_STAT_CODE IN (13, 996, 997, 998, 999)
AND D.INVOICE_ID = C.INVOICE_ID
)
GROUP BY C.INVOICE_ID
) AS B

itjagadesh · New User Joined: 05 Dec 2006 Posts: 89 Location: chennai

Hi Team,
can anyone give me the alternate query for below ...

UNLOAD TABLESPACE
LOCK NO
QUIESCE NO
SELECT A.VENDOR_NBR,
A.INVOICE_ID,
A.INVOICE_NBR,
A.INVOICE_DATE,
A.TOTAL_AMT,
A.DIV_NBR,
A.STORE_NBR,
A.EDI_TRANS_SRC_CD,
A.INV_PROC_AREA_CODE,
DATE(B.PROCESS_STATUS_TS)
FROM TAB1.INVOICE A,
(SELECT C.INVOICE_ID
,MAX(C.PROCESS_STATUS_TS) AS PROCESS_STATUS_TS
FROM TAB1.INVC_PROCESS_LOG C
WHERE C.PROCESS_STAT_CODE = 9
AND NOT EXISTS
(SELECT 1
FROM TAB1.INVC_PROCESS_LOG D
WHERE D.PROCESS_STAT_CODE IN (13, 996, 997, 998, 999)
AND D.INVOICE_ID = C.INVOICE_ID
)
GROUP BY C.INVOICE_ID
) AS B
WHERE A.INVOICE_ID = B.INVOICE_ID
WITH UR
OUTDDN (SYSREC00)
FORMAT DSNTIAUL
LOADDDN SYSPUNCH LOADOPT (REPLACE, LOG NO, KEEPDICTIONARY)

previous query posted in this forum im getting different output.
can anyone please help me on this to get same output.

Thanks
Jagadesh