IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need help on query tuning


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
nileshyp

New User


Joined: 22 Jun 2005
Posts: 65
Location: Mumbai

PostPosted: Fri Mar 16, 2012 8:22 am
Reply with quote

Hi

I am running a with a join which is taking too long to execute whereas When I give separate queries I get below result instantly


Code:

---------+---------+---------+---------+---------+---------+
  SELECT COUNT(*) FROM GCMTEBT.PH01T19_PROD               
  WHERE PRD_BUS_STC_CD NOT IN ('1');                       
---------+---------+---------+---------+---------+---------+
                                                           
---------+---------+---------+---------+---------+---------+
      21987                                                 
DSNE610I NUMBER OF ROWS DISPLAYED IS 1                     
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+
  SELECT COUNT(*) FROM GCMTEBT.PH01T05_COST               
  WHERE PRD_STA_CD NOT IN ('CLOSED');                       
---------+---------+---------+---------+---------+---------+
                                                           
---------+---------+---------+---------+---------+---------+
     241071                                               


Where as when I give below query it takes tremendous time to execute.


Code:

SELECT A.PRD_LNG_NAM                                 
       ,A.PRD_NO                                     
       ,A.PRD_BUS_STC_CD                             
       ,A.PRD_TX_NO                                   
       ,B.PRD_STA_CD                                 
FROM GCMTEBT.PH01T19_PROD A, GCMTEBT.PH01T05_COST B
WHERE A.PRD_BUS_STC_CD NOT IN ('1')                   
AND B.PRD_STA_CD NOT IN ('CLOSED')                   
AND A.PFT_CTR_CD = B.PFT_CTR_CD;                     


Could some let me know the way out to fine tune this query so that it will not take such a long time to execute.

Thanks & Regards,
Neil[/code]
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri Mar 16, 2012 9:27 am
Reply with quote

Hi,

Which columns are primaray key for both tables and also which columns are having indexes?

I am assuming that you does not want to select the rows which satisfies the below criteria

Code:
A.PRD_BUS_STC_CD  = 1  or  B.PRD_STA_CD = 'CLOSED'


I have not tested this,but try with below query and see if it works for you

Code:
SELECT A.PRD_LNG_NAM                                 
       ,A.PRD_NO                                     
       ,A.PRD_BUS_STC_CD                             
       ,A.PRD_TX_NO                                   
       ,B.PRD_STA_CD                                 
FROM GCMTEBT.PH01T19_PROD A,
    GCMTEBT.PH01T05_COST B
WHERE A.PFT_CTR_CD = B.PFT_CTR_CD
AND NOT EXISTS
( SELECT A1.PFT_CTR_CD
      FROM GCMTEBT.PH01T19_PROD A1
              ,GCMTEBT.PH01T05_COST B1
     WHERE  A1.PFT_CTR_CD = A.PFT_CTR_CD
         AND  B1.PFT_CTR_CD = A1.PFT_CTR_CD
         AND  (A.PRD_BUS_STC_CD  = 1   OR
                   B1.PRD_STA_CD = 'CLOSED'))


Regards,
Chandan
Back to top
View user's profile Send private message
nileshyp

New User


Joined: 22 Jun 2005
Posts: 65
Location: Mumbai

PostPosted: Fri Mar 16, 2012 12:09 pm
Reply with quote

Hi Chandan,

Thanks for the help. However I tried running query you suggested above but I am getting result as follows

Code:

DSNE626I MAXIMUM OUTPUT LINES FOR SELECT STATEMENT REACHED (999999), PROCESSING FOR CURRENT SELECT STATEMENT TERMINATED
DSNE610I NUMBER OF ROWS DISPLAYED IS 999999                         
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0


I guess result is not proper considering individual queries return 21987 & 241071 respectively.

I would appreciate if someone could help me on the above issue.

Thanks & Regards,
Neil
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Mar 16, 2012 3:21 pm
Reply with quote

What are different values possible for the below columns.

A.PRD_BUS_STC_CD
B.PRD_STA_CD
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri Mar 16, 2012 3:27 pm
Reply with quote

Hi Neil,

Sorry I guess I did a typo error in above query

it needs to be A1.PRD_BUS_STC_CD = 1

corrected query is below

Code:
SELECT A.PRD_LNG_NAM                                 
       ,A.PRD_NO                                     
       ,A.PRD_BUS_STC_CD                             
       ,A.PRD_TX_NO                                   
       ,B.PRD_STA_CD                                 
FROM GCMTEBT.PH01T19_PROD A,
    GCMTEBT.PH01T05_COST B
WHERE A.PFT_CTR_CD = B.PFT_CTR_CD
AND NOT EXISTS
( SELECT A1.PFT_CTR_CD
      FROM GCMTEBT.PH01T19_PROD A1
              ,GCMTEBT.PH01T05_COST B1
     WHERE  A1.PFT_CTR_CD = A.PFT_CTR_CD
         AND  B1.PFT_CTR_CD = A1.PFT_CTR_CD
         AND  (A1.PRD_BUS_STC_CD  = 1   OR
                   B1.PRD_STA_CD = 'CLOSED'))


Also for a given PFT_CTR_CD how many rows will be present in with
Code:
A.PFT_CTR_CD  = B.PFT_CTR_CD


Regards,
Chandan
Back to top
View user's profile Send private message
nileshyp

New User


Joined: 22 Jun 2005
Posts: 65
Location: Mumbai

PostPosted: Fri Mar 16, 2012 3:43 pm
Reply with quote

Hi Chandan,

I tried given solution as well. It returned 0 rows icon_sad.gif

Code:

DSNE610I NUMBER OF ROWS DISPLAYED IS 0                     
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100


How come separate queries gives rows but join does not work?

Regards,
Neil
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Fri Mar 16, 2012 5:05 pm
Reply with quote

Hi Neil,

Can you provide sample data from your both tables showing which rows are expected in output.

Regards,
Chandan
Back to top
View user's profile Send private message
Parthiban DS

New User


Joined: 07 Aug 2011
Posts: 5
Location: India

PostPosted: Wed Mar 21, 2012 8:01 pm
Reply with quote

My understanding is, for every row in PH01T19_PROD, multiple records will be present in PH01T05_COST and I think your requirement is to extract matching records with below condition.

A.PFT_CTR_CD = B.PFT_CTR_CD
AND
B.PRD_STA_CD NOT IN ('CLOSED')
AND
A.PRD_BUS_STC_CD NOT IN ('1')

Please try below Query.

Code:

 SELECT COUNT(*)
   FROM GCMTEBT.PH01T19_PROD A
  WHERE EXISTS
       (SELECT 1
          FROM GCMTEBT.PH01T05_COST B
         WHERE A.PFT_CTR_CD = B.PFT_CTR_CD
           AND B.PRD_STA_CD NOT IN ('CLOSED')
       )
    AND A.PRD_BUS_STC_CD NOT IN ('1')
   WITH UR;


Let me know the result.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Thu Mar 22, 2012 12:28 am
Reply with quote

nileshyp wrote:
Hi

I am running a with a join which is taking too long to execute whereas When I give separate queries I get below result instantly


Code:

---------+---------+---------+---------+---------+---------+
  SELECT COUNT(*) FROM GCMTEBT.PH01T19_PROD               
  WHERE PRD_BUS_STC_CD NOT IN ('1');                       
---------+---------+---------+---------+---------+---------+
                                                           
---------+---------+---------+---------+---------+---------+
      21987                                                 
DSNE610I NUMBER OF ROWS DISPLAYED IS 1                     
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+
  SELECT COUNT(*) FROM GCMTEBT.PH01T05_COST               
  WHERE PRD_STA_CD NOT IN ('CLOSED');                       
---------+---------+---------+---------+---------+---------+
                                                           
---------+---------+---------+---------+---------+---------+
     241071                                               


Where as when I give below query it takes tremendous time to execute.


Code:

SELECT A.PRD_LNG_NAM                                 
       ,A.PRD_NO                                     
       ,A.PRD_BUS_STC_CD                             
       ,A.PRD_TX_NO                                   
       ,B.PRD_STA_CD                                 
FROM GCMTEBT.PH01T19_PROD A, GCMTEBT.PH01T05_COST B
WHERE A.PRD_BUS_STC_CD NOT IN ('1')                   
AND B.PRD_STA_CD NOT IN ('CLOSED')                   
AND A.PFT_CTR_CD = B.PFT_CTR_CD;                     


Could some let me know the way out to fine tune this query so that it will not take such a long time to execute.

Thanks & Regards,
Neil[/code]


Try this

Code:


SELECT A.PRD_LNG_NAM                                 
       ,A.PRD_NO                                     
       ,A.PRD_BUS_STC_CD                             
       ,A.PRD_TX_NO                                   
       ,B.PRD_STA_CD                                 
FROM GCMTEBT.PH01T19_PROD A, GCMTEBT.PH01T05_COST B
where A.PFT_CTR_CD = B.PFT_CTR_CD
AND A.PRD_BUS_STC_CD <> '1'                   
AND B.PRD_STA_CD <> 'CLOSED'
WITH UR;

 


Check few things:

1. Is the index defined on the columns used in the join clause
A.PFT_CTR_CD = B.PFT_CTR_CD for the tables A and B
2. Why so you want to use IN clause when you have only 1 value in it. IN clause is one of the slowest in SQL.

Try if you get any difference le us know.
Back to top
View user's profile Send private message
bauer

New User


Joined: 03 Mar 2009
Posts: 28
Location: germany

PostPosted: Thu Mar 22, 2012 12:56 pm
Reply with quote

What are the results of Explain Command ?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
Search our Forums:

Back to Top