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
 

 

any possibility to minimize CPU time

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: any possibility to minimize CPU time
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    Post subject:
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    Post subject: Reply to: any possibility to minimize CPU time
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10254
Location: italy

PostPosted: Mon Dec 14, 2009 6:07 pm    Post subject: Reply to: any possibility to minimize CPU time
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    Post subject:
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    Post subject: Reply to: any possibility to minimize CPU time
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    Post subject:
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    Post subject:
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    Post subject:
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    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 DSNACCOX (can it be run on 1 db/ts, t... SRICOBSAS DB2 3 Sat May 06, 2017 12:59 am
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts Application not run by time HH:MM tri... jzhardy IBM Tools 1 Sun Apr 09, 2017 3:22 pm
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm


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