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
 

 

What are the way we can improve CPU performance in DB2 qry

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

New User


Joined: 13 Oct 2016
Posts: 16
Location: India

PostPosted: Mon Oct 24, 2016 2:16 pm    Post subject: What are the way we can improve CPU performance in DB2 qry
Reply with quote

Hi All,

i'm doing performance fix for old legacy system code, where one cursor has many sub queries and exists clauses.
i tried to replace that logic using icetool with minimum unload queries in jcls' but it not giving expected savings.

therefore i wanted to know is there anyway below query can be write different way.
Code:
//UNLD.GUN  EXEC ZPLNIMS,MBR=IKJEFT01,COND=(4,LT)         
//STEPLIB  DD DSN=DB10.RUNLIB.LOAD,DISP=SHR             
//SYSTSIN  DD *                                         
DSN SYSTEM(DB10)                                         
RUN PROGRAM(DSNTIAUL) PARMS('SQL')                       
END                                                     
//SYSIN     DD *                                         
          SELECT  .GUN.CR_PART_NO                         
                , .GUN.RO_NO                             
                , .GUN.SG_CD                             
                , .GUN.BPR_TPN                           
                , .GUN.EXP_STEP_EFF_DT                   
                , .GUN.EXP_STEP_END_DT                   
                , .GUN.STEP_TYPE_NUM                     
                , .GUN.STEP_ID                           
              , BSC.STEP_CLASS                           
              , .GUN.STEP_UPLIFT_PC                       
              , VOL.STEP_VOLUME                           
              , BSC.UPLIFT_TYPE                           
              , BSC.MERGE_RULE                           
              , .GUN.RESOLVE_IND                           
              , .GUN.RESOLVE_ACTION_IND                   
              , .GUN.NORMAL_STEP_CF                       
              , .GUN.SHDW_STEP_CF                         
              , .GUN.WTHR_STEP_CF                         
              , .GUN.LAST_UPDT_TSTAMP                     
              , .GUN.STEP_REASON                           
              , .GUN.WTHR_SENS_FLAG                       
              , .GUN.CONTINUE_NWS                         
           FROM GDB2JJPL.VXJJ0BSC BSC,                   
                GDB2JJPL.VXJJ0.GUN .GUN                     
         LEFT OUTER JOIN GDB2JJPL.VXJJ0VOL VOL           
              ON .GUN.CR_PART_NO  = VOL.CR_PART_NO         
             AND .GUN.RO_NO       = VOL.RO_NO   
               AND .GUN.BPR_TPN     = VOL.BPR_TPN                   
               AND .GUN.STEP_ID     = VOL.STEP_ID                   
           WHERE .GUN.CR_PART_NO                                     
//          DD DSN=JKL.JKPARTNO.TEMP.CARD.P0A,DISP=SHR             
//          DD *                                                   
           AND   .GUN.EXP_STEP_END_DT                               
//          DD DSN=&&CARD2,DISP=(OLD,PASS)                         
//          DD *                                                   
           AND   .GUN.LAST_UPDT_TSTAMP                               
//          DD DSN=&&CARD3,DISP=(OLD,PASS)                         
//          DD *                                                   
           AND   .GUN.EXP_STEP_EFF_DT <= '9999-12-31'               
           AND   .GUN.STEP_TYPE_NUM    = BSC.STEP_TYPE_NUM           
           AND EXISTS                                               
                (SELECT 1 FROM                                     
                 GDB2JJPL.VXJJ0XSU XSU                             
                 WHERE XSU.CR_PART_NO = .GUN.CR_PART_NO             
                 AND   XSU.RO_NO      = .GUN.RO_NO                   
                 AND  SUBSTR(XSU.SG_CD,1,1) = SUBSTR(.GUN.SG_CD,1,1)
           ORDER BY 1,2,3,4,5,6                               
                  WITH UR;                                   
 //SYSPUNCH DD DUMMY                                         
 //SYSREC00 DD DSN=JKL.VXJJ0.GUN.UNLOAD.P0A,DISP=(,CATLG),     
 //            UNIT=DASD,SPACE=(CYL,(25,3),RLSE)             
 //SYSTSPRT DD SYSOUT=*                 





regards,
Guna
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Oct 24, 2016 2:56 pm    Post subject: Reply to: What are the way we can improve CPU performance in DB2 qry
Reply with quote

Quote:
What are the way we can improve CPU performance ...

by convincing the powers of Your organisation to by a more performing/powerful cpu

c/performance/utilisation/

do You realise that a forum is for quickies ... ?

Your query is too complicated for anybody to spend his time analysing it,
furthermore the info provided is incomplete
the query uses some dataset whose content is not shown

Code:
//          DD DSN=JKL.JKPARTNO.TEMP.CARD.P0A,DISP=SHR                                 
//          DD DSN=&&CARD2,DISP=(OLD,PASS)                                         
//          DD DSN=&&CARD3,DISP=(OLD,PASS)                         


complicating even more the task
and making the chances of getting an answer pretty slim
Back to top
View user's profile Send private message
Gunapala CN

New User


Joined: 13 Oct 2016
Posts: 16
Location: India

PostPosted: Mon Oct 24, 2016 3:10 pm    Post subject: Re: Reply to: What are the way we can improve CPU performance in DB2 qry
Reply with quote

enrico-sorichetti wrote:
do You realise that a forum is for quickies ... ?

Your query is too complicated for anybody to spend his time analysing it,
furthermore the info provided is incomplete
the query uses some dataset whose content is not shown

Code:
//          DD DSN=JKL.JKPARTNO.TEMP.CARD.P0A,DISP=SHR                                 
//          DD DSN=&&CARD2,DISP=(OLD,PASS)                                         
//          DD DSN=&&CARD3,DISP=(OLD,PASS)                         


complicating even more the task
and making the chances of getting an answer pretty slim


Sorry for that !! those data sets are having data :
JKL.JKPARTNO.TEMP.CARD.P0A = between 01 to 05
&&CARD2,DISP = '05'
&&CARD3,DISP = time stamp

you dont have to understand my query. could you please help to understand what are general ways of writing or using query to improve CPU.

regards,
Guna
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Oct 24, 2016 3:29 pm    Post subject: Reply to: What are the way we can improve CPU performance in DB2 qry
Reply with quote

Quote:
you dont have to understand my query.

so why did You post it ? icon_evil.gif

Quote:
what are general ways of writing or using query to improve CPU.


that info is available in the manuals, search the IBM redbooks collection

and if You want to be taken seriously learn to use the right terminology
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Mon Oct 24, 2016 4:19 pm    Post subject:
Reply with quote

Please move this post, nothing to do with DFSORT!

And locking it would also be useful, as it's unlikely to go anywhere.
Back to top
View user's profile Send private message
Gunapala CN

New User


Joined: 13 Oct 2016
Posts: 16
Location: India

PostPosted: Mon Oct 24, 2016 4:38 pm    Post subject: Re: Reply to: What are the way we can improve CPU performance in DB2 qry
Reply with quote

enrico-sorichetti wrote:
Quote:
you dont have to understand my query.

so why did You post it ? icon_evil.gif

hehe icon_smile.gif

Quote:
what are general ways of writing or using query to improve CPU.


that info is available in the manuals, search the IBM redbooks collection

and if You want to be taken seriously learn to use the right terminology


Okay thank you icon_smile.gif
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Mon Oct 24, 2016 5:23 pm    Post subject:
Reply with quote

Quote:
it not giving expected savings.
What are you expecting to save?

If your site is not running a specialty engine, adding one will help CPU performance since some of the DB2 work will be offloaded to the engine. Convincing your management that the savings is worth the $40,000 to $100,000 that the specialty engine costs, however, may be difficult.

Have you talked to your site support group about the query? They may have some suggestions for improvement, and they have more tools to investigate the query than we do.
Back to top
View user's profile Send private message
Gunapala CN

New User


Joined: 13 Oct 2016
Posts: 16
Location: India

PostPosted: Mon Oct 24, 2016 6:07 pm    Post subject:
Reply with quote

Robert Sample wrote:
Quote:
it not giving expected savings.
What are you expecting to save?

its based on number of records, basically we are experimenting performance improvement of existing COBOL DB2 module. these are old existing modules which are taking more time day by when more input data has increased. hence we are trying to Save the CPU time by rewriting old big queries and few are putting in jcl's using ICETOOL help.

If your site is not running a specialty engine, adding one will help CPU performance since some of the DB2 work will be offloaded to the engine. Convincing your management that the savings is worth the $40,000 to $100,000 that the specialty engine costs, however, may be difficult.

Have you talked to your site support group about the query? They may have some suggestions for improvement, and they have more tools to investigate the query than we do.


yes, we are planning to work with DB2 team to get more details on this.

Thanks a lot for your information icon_smile.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Mon Oct 24, 2016 8:03 pm    Post subject:
Reply with quote

Why do you need
Code:
AND   .GUN.EXP_STEP_EFF_DT <= '9999-12-31'
with or without it is going to give you same results?
How about looking into explain?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Oct 24, 2016 8:08 pm    Post subject: Reply to: What are the way we can improve CPU performance in DB2 qry
Reply with quote

they are probably preparing for a 5 digits year
it always wise to plan in advance the big changes icon_wink.gif
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Mon Oct 24, 2016 9:46 pm    Post subject: Re: Reply to: What are the way we can improve CPU performance in DB2 qry
Reply with quote

enrico-sorichetti wrote:
they are probably preparing for a 5 digits year
it always wise to plan in advance the big changes icon_wink.gif

No, it shows their utter incompetence!
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 PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts DISP=(SHR,PASS) performance opinion steve-myers JCL & VSAM 1 Wed Dec 02, 2015 11:53 pm
No new posts Performance tuning of Online system bipinpeter All Other Mainframe Topics 2 Thu Nov 26, 2015 2:29 pm
This topic is locked: you cannot edit posts or make replies. Performance Engineer - Computer science Express Mainframe Jobs 0 Wed Nov 25, 2015 3:39 pm
No new posts VSAM read performance jerryte JCL & VSAM 6 Mon Nov 09, 2015 9:13 pm


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