Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 Reduce execution time

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

New User


Joined: 21 Jul 2010
Posts: 23
Location: chennai

PostPosted: Mon Oct 31, 2011 5:40 pm    Post subject: DB2 Reduce execution time
Reply with quote

Hi All,
Please anyone help me in my requirement. I have a query as
Code:
EXEC SQL DECLARE CLR_SZ_ALLOC_CSR CURSOR FOR               
          SELECT A.PACK_MULTIPLE,                         
                 A.SZ_SCALE_CD,                           
                 B.SIZE_NBR,                               
                 A.PACK_PO_LINE_ITM,                       
                 D.LOC_NBR,                               
                 A.SZ_GRP_SCALE_PACKS,                     
                (CASE WHEN B.PO_SZ_SCALE_QTY = 0 THEN     
               ( A.SZ_GRP_SCALE_PACKS * B.PO_SZ_SCALE_PCT )
                 ELSE                                     
               ( A.SZ_GRP_SCALE_PACKS * B.PO_SZ_SCALE_QTY )
                 END),                                     
                 D.SCALE_LOC_PACKS,                       
                 (CASE WHEN A.PO_SZ_SCL_PCT_USED = 0 THEN 
                        0                                 
                 ELSE                                     
                (CASE WHEN B.PO_SZ_SCALE_QTY = 0 THEN     
                  (CASE WHEN B.PO_SZ_SCALE_PCT = 0 THEN   
                        0                                 
                   ELSE                                   
                   INTEGER((D.SCALE_LOC_UNITS             
                   / (INTEGER((A.PO_SZ_SCL_PCT_USED / 100)+.9))
                  *  DECIMAL(( B.PO_SZ_SCALE_PCT / 100 ),5,2)))
                      END)                                     
                    ELSE                                       
                    INTEGER((D.SCALE_LOC_UNITS                 
                   / (CASE WHEN A.PO_SZ_SCL_PCT_USED = 0 THEN 
                                1                             
                      ELSE                                     
                              ( A.PO_SZ_SCL_PCT_USED * .01)   
                      END)                                     
                 * ( B.PO_SZ_SCALE_QTY * .01)) + .50)         
                    END)                                       
                    END),                                     
                    B.PO_SZ_SCALE_QTY,                         
                    B.PO_SZ_SCALE_PCT,                         
                    A.LAST_UPD_TS                             
              FROM  PO_SZ_GRP_SCALE   A,                       
                    PO_SZ_SCALE       B                       
LEFT OUTER JOIN (SELECT E.LOC_NBR,                           
                        E.SCALE_LOC_PACKS,                   
                        E.SCALE_LOC_UNITS,                   
                        E.SZ_SCALE_CD,                       
                        E.PACK_MULTIPLE,                     
                        E.CLR_PO_LINE_ITM,                   
                        E.PID_PO_LINE_ITM,                   
                        E.SHPG_NBR,                         
                        E.PO_NBR,                           
                        E.PARTITION_KEY                     
                   FROM PO_SGRP_SCALE_LOC E                 
                  WHERE E.LOC_NBR   = :BUYSSCL-LOC-NBR       
                  AND  E.PO_NBR     = :BUYSGSC-PO-NBR       
                  AND  E.PARTITION_KEY                       
                                    = :BUYSGSC-PARTITION-KEY
                  AND  E.SHPG_NBR                           
                                    = :BUYSGSC-SHPG-NBR     
                  AND  E.PID_PO_LINE_ITM                     
                                   = :BUYSGSC-PID-PO-LINE-ITM
                  AND  E.CLR_PO_LINE_ITM                     
                                   = :BUYSGSC-CLR-PO-LINE-ITM
                    ) AS D                               
            ON D.PARTITION_KEY   = B.PARTITION_KEY       
           AND D.PO_NBR          = B.PO_NBR               
           AND D.SHPG_NBR        = B.SHPG_NBR             
           AND D.PID_PO_LINE_ITM = B.PID_PO_LINE_ITM     
           AND D.CLR_PO_LINE_ITM = B.CLR_PO_LINE_ITM     
           AND D.PACK_MULTIPLE   = B.PACK_MULTIPLE       
           AND D.SZ_SCALE_CD     = B.SZ_SCALE_CD         
       WHERE  A.PARTITION_KEY   = :BUYSGSC-PARTITION-KEY 
         AND  A.PO_NBR          = :BUYSGSC-PO-NBR         
         AND  A.SHPG_NBR        = :BUYSGSC-SHPG-NBR       
         AND  A.PID_PO_LINE_ITM = :BUYSGSC-PID-PO-LINE-ITM
         AND  A.CLR_PO_LINE_ITM = :BUYSGSC-CLR-PO-LINE-ITM
         AND  A.SZ_GRP_SCALE_UNITS > 0                   
         AND  B.PARTITION_KEY   = A.PARTITION_KEY         
         AND  B.PO_NBR          = A.PO_NBR               
         AND  B.SHPG_NBR        = A.SHPG_NBR             
         AND  B.PID_PO_LINE_ITM = A.PID_PO_LINE_ITM       
         AND  B.CLR_PO_LINE_ITM = A.CLR_PO_LINE_ITM       
         AND  B.PO_LINE_ITM     = :BUYSZSC-PO-LINE-ITM   
         AND  B.PACK_MULTIPLE   = A.PACK_MULTIPLE         
                 AND  B.SZ_SCALE_CD     = A.SZ_SCALE_CD
              ORDER BY D.LOC_NBR,                     
                       A.PACK_PO_LINE_ITM DESC,       
                       A.PACK_MULTIPLE,               
                       A.SZ_SCALE_CD,                 
                       B.SIZE_NBR                     
              FOR FETCH ONLY WITH UR                   
     END-EXEC.                                         

This is the query we are using in our mainframe program. It is talking long time to execute. But according to our requirement we need to alter this query and make to execute faster with same result. Please help me. Thanks in advance........
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10327
Location: italy

PostPosted: Mon Oct 31, 2011 6:05 pm    Post subject: Reply to: DB2 Reduce execution time
Reply with quote

Quote:
It is talking long time to execute.

define in proper IT terms long time please ...
You just whined about something not behaving according to Your/Somebody's expectations
and providing nothing to work on
the only reasonable advice You might get is ...
do not drink too much coffee while waiting icon_cool.gif

Quote:
But according to our requirement we need to alter this query and make to execute faster with same result.

more productive to get rid the idiot who set the requirement ...
what if it is correct for the <thing> to take for execution what You call a long time ? icon_evil.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Mon Oct 31, 2011 6:23 pm    Post subject: Reply to: DB2 Reduce execution time
Reply with quote

And it is in the DFSORT forum because....?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10327
Location: italy

PostPosted: Mon Oct 31, 2011 6:27 pm    Post subject: Reply to: DB2 Reduce execution time
Reply with quote

that' s probably the reason for the long <time> icon_wink.gif

moved where it belongs
and edited for readability adding the code tags
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 Oct 31, 2011 9:31 pm    Post subject:
Reply with quote

Hello,

Quote:
But according to our requirement we need to alter this query and make to execute faster with same result
There is no reason to believe that any "monster query" will run acceptably.

Suggest you experiment with breaking this into more than one big sql statement and make sure the individual components all perform acceptably. It is in a program anyway, so having something that runs faster and is easier to work with will be better for everyone.

There is neither business nor technical reason to jam everything into one statement as this is. And NO, it is not "the requirement". The requirement is to correctly process the data using an acceptable amount of resources.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Oct 31, 2011 9:56 pm    Post subject:
Reply with quote

harish5002,

This things will help you get started,
Check whether index exists for all the columns in the WHERE clause
Does the Clustering index exists on the columns in the order by
What is the cardinality of the columns in the WHERE clause ?
How many number of rows exist in the table ?
How much rows does the query retreive ?
Finally, did you do an explain ?

Thanks,
Sushanth
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. 00D3003B - time outs in DB2 when invo... chavinash2004 DB2 2 Mon Oct 09, 2017 4:39 pm
No new posts VSAM RLS Wait time blayek JCL & VSAM 2 Mon Oct 02, 2017 9:05 pm
No new posts Speed-up/reduce CPU use of code prino PL/I & Assembler 0 Tue Sep 26, 2017 12:05 am
No new posts Comparing 2 Files using Current time arunsoods SYNCSORT 9 Fri Sep 22, 2017 6:00 pm
No new posts Regarding time parameter shanthi gude JCL & VSAM 7 Mon Sep 04, 2017 2:31 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us