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
 

 

Help Needed in DB2 query Tuning

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

New User


Joined: 11 May 2007
Posts: 20
Location: Canada

PostPosted: Mon Jun 01, 2009 9:01 am    Post subject: Help Needed in DB2 query Tuning
Reply with quote

Hi,

I have the below cursor which needs to fine tuned. Will it help if I split the Query. i.e Having the Subquery as a different cursor and CUR1 as simple Sql. Any other suggestions plz let me know


DECLARE CUR1 CURSOR FOR
SELECT F1
,F2
,F3
,F4
FROM TAB1
WHERE F1 IN
(SELECT DISTINCT D2
FROM TAB2
WHERE D1 = :WS-D1
AND D2 > '' -
AND D3 = :WS-D3
AND D4 = :WS-D4
AND D5 > 0
AND D6 > 0
)
AND F5 = :WS-F5
AND F6 <> '10'
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: Mon Jun 01, 2009 11:46 am    Post subject:
Reply with quote

Hello,

How does the subquery perform when it is not a subquery?

Depending on the volume of data needed to be examined to satisfy the subquery, the performance of the outer select may be dramatically different.

Suggest you work with your dba to look at the requirement and make sure your queries and your table/index definitions will properly support this requirement.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jun 01, 2009 6:23 pm    Post subject:
Reply with quote

Try joining TAB1 and TAB2 using F1 and D2 instead of a subquery... but this depends on the respective index definitions and vol of data ... if you could post all data we might be able to help ...
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Jun 02, 2009 3:11 pm    Post subject:
Reply with quote

One quick observation. You can remove the Distinct from your subquery.
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
This topic is locked: you cannot edit posts or make replies. SORT trick needed bshkris SYNCSORT 6 Tue May 02, 2017 4:35 am
No new posts Batch job tuning sgandhla Testing & Performance analysis 5 Fri Mar 24, 2017 9:41 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


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