|
View previous topic :: View next topic
|
| Author |
Message |
Gunapala CN
New User
Joined: 13 Oct 2016
Posts: 16
Location: India
|
|
|
|
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 |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10902
Location: italy
|
|
|
|
| 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 |
|
|
Gunapala CN
New User
Joined: 13 Oct 2016
Posts: 16
Location: India
|
|
|
|
| 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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10902
Location: italy
|
|
|
|
| Quote: |
| you dont have to understand my query. |
so why did You post it ? 
| 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 |
|
|
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1323
Location: Vilnius, Lithuania
|
|
|
|
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 |
|
|
Gunapala CN
New User
Joined: 13 Oct 2016
Posts: 16
Location: India
|
|
|
|
| enrico-sorichetti wrote: |
| Quote: |
| you dont have to understand my query. |
so why did You post it ?
hehe 
| 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 |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
| 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 |
|
|
Gunapala CN
New User
Joined: 13 Oct 2016
Posts: 16
Location: India
|
|
|
|
| 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 |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10902
Location: italy
|
|
|
|
they are probably preparing for a 5 digits year
it always wise to plan in advance the big changes  |
|
| Back to top |
|
|
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1323
Location: Vilnius, Lithuania
|
|
|
|
| enrico-sorichetti wrote: |
they are probably preparing for a 5 digits year
it always wise to plan in advance the big changes |
No, it shows their utter incompetence! |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
