View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
deleted the post with attachment,
here is the query with the code tags
Code: |
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)
|
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
But below query takes 75 minutes to unload the data.How to reduce CPU time? |
Is the 75 minutes wall time or cpu time? If it is cpu time, what is the wall time?
300+ million rows will take some time. If the wall time is 75 minutes, that is only 1.25 hours - which may not be completely out of line.
Have you talked with your dba about how (well) this process performs internally? |
|
Back to top |
|
|
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.
Code: |
RC EXCP CONN TCB CPUTIME SRB CLOCK SERV PG PAGE
04 15540 3731 75.94 .00 120.8 45754K 0 0 |
For Entire JOB
TOTAL TCB CPU TIME= 127.95
TOTAL ELAPSED TIME= 244.2
For this Unload Step -- CPU TIME - 75.94
Jagadesh |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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. |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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. |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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)? |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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. |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005 Posts: 306
|
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
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
Code: |
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 C.PROCESS_STAT_CODE
NOT IN (13, 996, 997, 998, 999)
GROUP BY C.INVOICE_ID
) AS B
WHERE A.INVOICE_ID = B.INVOICE_ID
|
|
|
Back to top |
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
|