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: 1529
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: 1529
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: 1278
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
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 Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Need points to tune IMS Checkpoint Pr... satish.ms10 IMS DB/DC 2 Fri Sep 30, 2016 4:12 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


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