Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Performance Tuning In DB2 Query - Help

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Thu Nov 20, 2008 2:57 pm    Post subject: Performance Tuning In DB2 Query - Help
Reply with quote

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
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Thu Nov 20, 2008 3:54 pm    Post subject: Reply to: Performance Tuning In DD2 Query - Help
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Nov 21, 2008 1:59 am    Post subject:
Reply with quote

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
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Fri Nov 21, 2008 8:41 am    Post subject: Reply to: Performance Tuning In DD2 Query - Help
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Nov 21, 2008 9:02 am    Post subject:
Reply with quote

Hello Jagadesh,

You're welcome icon_smile.gif

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
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Fri Nov 21, 2008 9:38 am    Post subject: Reply to: Performance Tuning In DD2 Query - Help
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Nov 21, 2008 10:26 am    Post subject:
Reply with quote

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
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Fri Nov 21, 2008 10:37 am    Post subject: Reply to: Performance Tuning In DD2 Query - Help
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Nov 21, 2008 10:45 am    Post subject:
Reply with quote

Hi Jagadesh,

One last thought for tonight icon_smile.gif

How long does it take to simply unload the 300+ million row table (without the multi-table query)?
Back to top
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Fri Nov 21, 2008 11:54 am    Post subject: Reply to: Performance Tuning In DB2 Query - Help
Reply with quote

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
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Fri Nov 21, 2008 1:49 pm    Post subject:
Reply with quote

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 icon_biggrin.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Nov 21, 2008 9:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Tue Nov 25, 2008 12:28 pm    Post subject: Reply to: Performance Tuning In DB2 Query - Help
Reply with quote

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
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Tue Nov 25, 2008 1:31 pm    Post subject: Reply to: Performance Tuning In DB2 Query - Help
Reply with quote

How about doing an explain on your query and checking which all index is getting used
Back to top
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Tue Nov 25, 2008 5:04 pm    Post subject: Reply to: Performance Tuning In DB2 Query - Help
Reply with quote

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
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Tue Nov 25, 2008 5:18 pm    Post subject:
Reply with quote

Check this links

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

http://endwaymainframe.blogspot.com/2006/02/using-explain-in-db2.html
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Nov 25, 2008 10:13 pm    Post subject:
Reply with quote

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
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Wed Dec 10, 2008 10:18 pm    Post subject: Reply to: Performance Tuning In DB2 Query - Help
Reply with quote

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
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Mon Dec 15, 2008 7:43 pm    Post subject: Reply to: Performance Tuning In DB2 Query - Help
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us