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
 

 

Need to help to fine tune my Query.

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

New User


Joined: 28 May 2007
Posts: 26
Location: Pune

PostPosted: Mon Jun 04, 2012 11:04 am    Post subject: Need to help to fine tune my Query.
Reply with quote

Hi Below SQL is taking huge CPU time. Can some help me to fine tune it?

Code:
SELECT A.INTCMNBR               
FROM TABLE1 A   
WHERE A.CID = 12345           
AND A.CTRY = 263               
AND A.CMNBR NOT IN           
( SELECT B.CM_NO           
  FROM TABLE2 B         
  WHERE B.CTRY_CD='263'     
  AND B.CORP=12345           
  AND B.CM_NO=A.CMNBR)
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1535
Location: Andromeda Galaxy

PostPosted: Mon Jun 04, 2012 11:09 am    Post subject:
Reply with quote

what have you tried so far?

Did you try executing an EXPLAIN??
Back to top
View user's profile Send private message
sreejeshcs

New User


Joined: 28 May 2007
Posts: 26
Location: Pune

PostPosted: Mon Jun 04, 2012 11:21 am    Post subject:
Reply with quote

No...
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1535
Location: Andromeda Galaxy

PostPosted: Mon Jun 04, 2012 11:23 am    Post subject:
Reply with quote

Please try that

In order to do that first create a plan_table
Back to top
View user's profile Send private message
sreejeshcs

New User


Joined: 28 May 2007
Posts: 26
Location: Pune

PostPosted: Mon Jun 04, 2012 11:25 am    Post subject:
Reply with quote

I dint get. can u explain in details reg plan_table?
Back to top
View user's profile Send private message
Prakash J

New User


Joined: 23 May 2012
Posts: 14
Location: India

PostPosted: Mon Jun 04, 2012 12:20 pm    Post subject: Reply to: Need to help to fine tune my Query.
Reply with quote

Sree,

1. Create the PLAN_TABLE with your User ID.

CREATE TABLE Userid.PLAN_TABLE LIKE DEFAULT.PLAN_TABLE;

2. EXPLAIN PLAN SET QUERYNO = 1 FOR [SQL Query];

3. Retrieve the information from PLAN_TABLE using
SELECT * FROM PLAN_TABLE WHERE QUERYNO = 1 and check for the information ACCESSTYPE, MATCHCOLS, ACCESSNAME PREFETCH etc.

For more information about these columns, please check out DB2 for z/OS Performance Monitoring and Tuning Guide.

- Prakash
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Jun 04, 2012 12:40 pm    Post subject:
Reply with quote

try this for both tables and show us the output:
Code:
SELECT
I.TBCREATOR, I.TBNAME, I.CREATOR, I.NAME
, I.UNIQUERULE,  I.CLUSTERRATIO
, I.FIRSTKEYCARD, I.FULLKEYCARD, K.COLSEQ,
     K.COLNAME,k.ordering
FROM SYSIBM.SYSINDEXES I
INNER JOIN SYSIBM.SYSKEYS K
ON I.CREATOR = K.IXCREATOR AND I.NAME = K.IXNAME
WHERE (I.TBCREATOR = &creator) AND (I.TBNAME like &tblike)
ORDER BY i.tbcreator, i.tbname, i.name, k.colseq
Back to top
View user's profile Send private message
Santhosh Menon

New User


Joined: 19 Jun 2012
Posts: 4
Location: USA

PostPosted: Wed Jun 20, 2012 7:19 am    Post subject:
Reply with quote

Sree,

Explain would be the best option.....

Also for a quick hit... try the below 2 options if u see a diff.
1) Changing Not IN to Not EXISTS
2) Changing the Query to a Left join.
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: Wed Jun 20, 2012 7:52 am    Post subject:
Reply with quote

Hello,

Did you notice that Sree has not provided the requested info and most likely has this running or moved on to something else.

Also, notice that Explain was mentioned above and the way to get started.

Lastly, if he cannot get an Explain, running the query provided by GuyC and posting the result would be helpful.
Back to top
View user's profile Send private message
Santhosh Menon

New User


Joined: 19 Jun 2012
Posts: 4
Location: USA

PostPosted: Wed Jun 20, 2012 8:21 am    Post subject:
Reply with quote

My bad. Just trying to help ! Will be more careful the next time
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: Wed Jun 20, 2012 8:41 am    Post subject:
Reply with quote

Hi Menon,

Quote:
My bad.
Nah, not so bad - no foul icon_smile.gif

It is good to want to help - we appreciate this!

Just wanted to mention that some topics are better candidates for a reply than others.
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 Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts DFSORT - Overriding default option TU... Steve Ironmonger DFSORT/ICETOOL 3 Tue Apr 04, 2017 3:54 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


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