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

any possibility to minimize CPU time


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

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Mon Dec 14, 2009 1:46 pm
Reply with quote

I am running batch query

Code:
          SELECT T1.WK_ORD_NBR,                           
                 SUBSTR(DIGITS(T1.SEQ_NBR),2,4),         
                 COALESCE(T2.MSA_PART_NBR,'P999999'),     
                 SUBSTR(DIGITS(T1.SEQ_NBR),2,4),         
                 COALESCE(MAX(T1.SERV_CD),'            '),
                 COALESCE(MAX(T1.MISS_DSC_IND),0),               
                 COALESCE(MAX(T1.INVL_DSC_IND),0),               
                 COALESCE(MAX(T1.AVDBL_DSC_IND),' '),             
                 COALESCE(MAX(T1.EQP_SER_NBR),'             '),     
                 COALESCE(MAX(T1.VLD_SER_NBR_IND),0),               
                 COALESCE(MAX(T1.NO_SERV_W_PRTS_IND),0),           
                 COALESCE(MAX(T2.LINE_PART_QTY),0),       
                 COALESCE(MAX(T2.UNT_COST_AMT),0),       
                 COALESCE(MAX(T2.LI_TOTAL_COST),0)       
          FROM ISDBA.VSR_OPS_TECN_DTL T1                 
                 LEFT OUTER JOIN                         
               ISDBA.VSV_SO_LINE_PART T2                 
                 ON T2.WK_ORD_NBR = T1.WK_ORD_NBR         
                    AND T2.SNL_SEQ_NBR = T1.SEQ_NBR       
                   GROUP BY T1.WK_ORD_NBR,   
                                   T1.SEQ_NBR,     
                                   T2.MSA_PART_NBR,
                                   T2.SEQ_NBR       
                    ORDER BY T1.WK_ORD_NBR,   
                                    T1.SEQ_NBR,     
                                    T2.MSA_PART_NBR,
                                    T2.SEQ_NBR;         

Code'd
Please suggest is there any possibility to reduce CPU time. I am using Coalesce to avoaid null values.
Thanks in advance
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Dec 14, 2009 2:58 pm
Reply with quote

can you do an explain on this query and post the results here ... post the indexes for the two tables ..
Back to top
View user's profile Send private message
sonali12_9

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Mon Dec 14, 2009 6:05 pm
Reply with quote

Actually i am trying to convert one focus program to cobol.
After retrieving ,data is written to o/p files and one report is generated
Query is
Code:
SELECT
       T1.WK_ORD_NBR
     , T1.SEQ_NBR             AS EQP_SEQ_NBR
     , T2.MSA_PART_NBR        AS PRT_NBR
     , T2.SEQ_NBR             AS PRT_SEQ_NBR
-*-
     , MAX(T1.SERV_CD)             AS SERV_CD
     , MAX(T1.MISS_DSC_IND)        AS MISS_DSC_IND
     , MAX(T1.INVL_DSC_IND)        AS INVL_DSC_IND
     , MAX(T1.AVDBL_DSC_IND)       AS AVDBL_DSC_IND
     , MAX(T1.EQP_SER_NBR)         AS EQP_SER_NBR
     , MAX(T1.VLD_SER_NBR_IND)     AS VLD_SER_NBR_IND
     , MAX(T1.NO_SERV_W_PRTS_IND)  AS NO_SERV_W_PRTS_IND
     , MAX(T2.LINE_PART_QTY)       AS LINE_PART_QTY
     , MAX(T2.UNT_COST_AMT)        AS UNT_COST_AMT
     , MAX(T2.LI_TOTAL_COST)       AS LI_TOTAL_COST
  FROM ISDBA.VSR_OPS_TECN_DTL T1
         LEFT OUTER JOIN
       ISDBA.VSV_SO_LINE_PART T2
          ON T2.WK_ORD_NBR = T1.WK_ORD_NBR
         AND T2.SNL_SEQ_NBR = T1.SEQ_NBR
GROUP BY
       T1.WK_ORD_NBR
     , T1.SEQ_NBR
     , T2.MSA_PART_NBR
     , T2.SEQ_NBR
ORDER BY
       T1.WK_ORD_NBR
     , T1.SEQ_NBR
     , T2.MSA_PART_NBR
     , T2.SEQ_NBR
FOR FETCH ONLY.

Index for VSR_OPS_TECN_DTL  (view)
WK_ORD_NBR
TECN_ID   
SEQ_NBR    and
for VSV_SO_LINE_PART ( view)
WK_ORD_NBR
SEQ_NBR   
SNL_SEQ_NBR

Code'd

I used coalesce just to replace null value with zeros.
Even if i run query as it is(without using coalesce) there is hardly any difference in time. My objective is to reduce the time.
Please let me know if any other info is required
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Dec 14, 2009 6:07 pm
Reply with quote

why do You feel that the time taken by the query is <improper> or <excessive> ?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Dec 14, 2009 6:12 pm
Reply with quote

Changes in your select clause is not going to change the timings as these are done only after the rows satisfying the where clause are bought into temp space ... views usually perform pretty well stand-alone and they can insulate the developer from requiring any knowledge about the data . But when multiple views are joined the access path can be less than optimum .. try joining the base tables directly for your query ..

you might even consider removing the order by .. group by and order by has the same sequence of columns.
Back to top
View user's profile Send private message
sonali12_9

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Tue Dec 15, 2009 12:27 pm
Reply with quote

I tried by joining tables not views and removed order by clause but it didnt make any difference.

Answer to " why i feel this query is taking much time?'

Normally focus takes more time than cobol so i was converting this program to cobol but somehow it is not working.Infact batch query is taking more time.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Dec 15, 2009 3:18 pm
Reply with quote

How do you compare cobol and focus ?
You new query is upon DB2 and this is should be the actual time it takes in DB2. Now what you need to focus ( look upon ) is on reducing the DB2 time by improving your query or indexes.
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Tue Dec 15, 2009 6:19 pm
Reply with quote

I suspect that one reason that the COBOL query takes longer is because during the SQL conversion the "FOR FETCH ONLY" clause was (apparently) dropped.
Back to top
View user's profile Send private message
senthilssg

New User


Joined: 09 Dec 2005
Posts: 64
Location: USA

PostPosted: Tue Dec 29, 2009 5:11 am
Reply with quote

Check with your DBA and request them to check where all the time is spending. It is in DB2 or in application.

If it is in DB2, please do explain see the access path. Check if there is any tablespace scan or non-matching index scan for any one of the table.

Also need to look on the index structure and explain output to provide further recommendation / suggestions.

Can you also mention where you are running this query. If it is test environment, please check RUNSTATS in test environment is same like production for these tables. Please check with DBA, do REORG, RUNSTATS for these tables if required.


Thanks
Senthil
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 To get the the current time DFSORT/ICETOOL 13
No new posts RC query -Time column CA Products 3
No new posts C Compile time time stamps Java & MQSeries 10
No new posts Parallelization in CICS to reduce res... CICS 4
No new posts Insert system time/date (timestamp) u... DFSORT/ICETOOL 5
Search our Forums:

Back to Top