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=*
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
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
Joined: 06 Jun 2008 Posts: 8696 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.
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.
