Update 5/12/2016 : Add more fields (MQ time, DB2 time ...)
This rexx reads the unloaded dataset written from the standard program DFH$MOLS which print SMF110 CICS Performance record.
The output of DFH$MOLS unfortunately is not easy for analysis
This Rexx provide a CSV dataset (I like Excel) , with Transaction Id, Elapse and CPU time associated with the number of DB2 requests. This is useful to have quickly a performance indicator of your Information System to compare(Which is mainly CICS and DB2 ...). As SMF110 includes DB2 time, all your consumption is here.
More detailed than SMF30
More convenient than SMF101 (in my shop SMF101 is not collected for CICS transactions because it is really huge)
Prereq : Execute DFHMNDUP then DFH$MOLS with the UNLOAD option
These rexx are now in CBTTAPE #941 - You can go to cbttape.org to download the xmit file.
/*Rexx*/ 00010000
numeric digits 15 00020000
/*-------------------------------------------------------------*/ 00030000
/* Report Transaction performance Data */ 00040000
/* Validated for CICS TS 5.3 */ 00040106
/* Extract smf 110 records output from dfh$mols unload file */ 00041000
/* ndt.db2@gmail.com */ 00050000
/* 10 Jun 2016 Release 1.0 for CICS 5.2 */ 00060012
/* 23 Nov 2016 Release 1.1 for CICS 5.3 */ 00070012
/*-------------------------------------------------------------*/ 00124000
/*Comment : Change datasets high level identifier */ 00125000
/*-------------------------------------------------------------*/ 00126000
ARG hlq 00127000
reci=0 00140001
reco=0 00140101
mintime='24:60:00' 00140207
maxtime='00:00:00' 00140307
totcpu=0 00140407
totelap=0 00140509
totdb2r=0 00140609
00141001
/* Input file : SMF extract sorted */ 00150000
oufl = hlq !! '.report.cicmol' 00160000
"ALLOC DD(INP) DS('"oufl"') , 00170000
SHR REU bufno(20)" 00180000
00190000
/* Report dataset on output */ 00200000
oufl = "'" !! hlq !! '.report.tran' !! "'" 00210000
"DELETE" oufl "PURGE" 00220000
00230000
"ALLOC FI(OUFL) DA("oufl") NEW CATALOG REUSE" , 00240000
"LRECL(600) RECFM(V B) TRACKS SPACE(600,600)" 00250000
rcalloc = rc 00260000
if rcalloc <> 0 then Do 00270000
say "**********************************************" 00280000
say " Error allocating Tran Report file" rcalloc 00290000
say " Abnormal end " 00300000
say "**********************************************" 00310000
Exit 8 00320000
end 00330000
/* WRITE report header */ 00360000
CALL write_header 00370000
00380000
/* START PROCESSING */ 00390000
DO FOREVER 00400000
/* read input */ 00410000
"EXECIO 1 DISKR INP" 00420000
IF RC > 0 THEN DO 00430000
if rc = 2 then 00440000
do 00450000
SAY 'End of Input file rc=' RC 00460000
rcalloc = rc 00470000
end 00480000
else do 00490000
SAY 'Error while reading Input file rc=' RC 00500000
rcalloc = 8 00510000
end 00520000
leave 00530000
END 00540000
PARSE PULL INPUT_REC 00550000
reci=reci+1 00560000
Call DECODE 00590000
Call write_report 01030000
END 01060000
/* End of processing - close file */ 01070000
"EXECIO" queued() "DISKW OUFL ( FINIS" 01080000
rcwrite = rc 01090000
if rcwrite<> 0 then Do 01100000
say "**********************************************" 01110000
say " Error writting OUFL file: " rcwrite 01120000
say " Abnormal end " 01130000
say "**********************************************" 01140000
Exit 8 01150000
end 01160000
"EXECIO 0 DISKR INP (STEM INL. FINIS" 01170000
"FREE DD(INP)" 01180000
"FREE DD(OUFL)" 01190000
01200000
say "Input records =" reci 01210000
say "Output records=" reco 01220000
say 'Periode processed' tsdate ' betweeen ' mintime maxtime 01230008
say ' Total Transactions ' reci 01231007
say ' Total cpu = ' totcpu 01231107
say ' Total Elapsed ' totelap 01231209
say ' Total db2requests = ' totdb2r 01232007
01290000
EXIT rcalloc 01300000
01310000
01350000
/* MAP from dfhsamp(DFHMNPDA) */ 01360000
decode: 01370000
jobname = SUBSTR(INPUT_REC,1,8) 01390002
applid = SUBSTR(INPUT_REC,9,8) 01401002
SysId = SUBSTR(INPUT_REC,25,4) 01404005
/* ...*/ 01405000
01410000
TranId = SUBSTR(INPUT_REC,93,4) 01410402
UserId = SUBSTR(INPUT_REC,101,8) 01410602
/* task start PDRATTT*/ 01410802
tunits = SUBSTR(INPUT_REC,113,8) 01410902
call stck tunits 01411002
startt=tstime 01411102
startts=tots 01411205
/* task stop PDRDETT*/ 01411402
tunits = SUBSTR(INPUT_REC,121,8) 01411503
call stck tunits 01411602
stopt=tstime 01411702
stopts=tots 01411805
/* pdrdist/ transaction dispatch time */ 01413010
offs = 2329 /* check with the manual corresponding to */ 01414011
/* the CICS version */ 01414111
usrdisp = c2x(SUBSTR(INPUT_REC,offs,8)) 01414211
usrdisp = x2d(SUBSTR(usrdisp,1,13)) * 0.000001 01414311
/* pdrcput/ usrcput tcb cpu transaction */ 01414511
offs = offs+12 01414611
usrcput = c2x(SUBSTR(INPUT_REC,offs,8)) 01414711
usrcput = x2d(SUBSTR(usrcput,1,13)) * 0.000001 01414811
/* PDRSUST User suspend time */ 01414911
offs = offs+36 01415011
usrsusp = c2x(SUBSTR(INPUT_REC,offs,8)) 01415111
usrsusp = x2d(SUBSTR(usrsusp,1,13)) * 0.000001 01415211
/* PDRDWT User Dispatch Wait time */ 01415311
offs = offs+12 01415411
usrwt = c2x(SUBSTR(INPUT_REC,offs,8)) 01415511
usrwt = x2d(SUBSTR(usrwt ,1,13)) * 0.000001 01415611
/* PDRQRDSP QR Dispatch time */ 01415711
offs = offs+12 01415811
usrqrdp = c2x(SUBSTR(INPUT_REC,offs,8)) 01415911
usrqrdp = x2d(SUBSTR(usrqrdp,1,13)) * 0.000001 01416011
/* PDRQRCPU QR CPU time */ 01416111
offs = offs+12 01416211
usrqrcpu = c2x(SUBSTR(INPUT_REC,offs,8)) 01416311
usrqrcpu = x2d(SUBSTR(usrqrcpu,1,13)) * 0.000001 01416411
/* PDRMSDSP Other mode Disp time */ 01416511
offs = offs+12 01416611
usrotdp = c2x(SUBSTR(INPUT_REC,offs,8)) 01416711
usrotdp = x2d(SUBSTR(usrotdp ,1,13)) * 0.000001 01416811
/* PDRMSCPU Other mode Cpu time */ 01416911
offs = offs+12 01417011
usrotcpu = c2x(SUBSTR(INPUT_REC,offs,8)) 01417111
usrotcpu = x2d(SUBSTR(usrotcpu,1,13)) * 0.000001 01417211
/* PDRRODSP RO mode Disp time */ 01417311
offs = offs+12 01417411
usrROdp = c2x(SUBSTR(INPUT_REC,offs,8)) 01417511
usrROdp = x2d(SUBSTR(usrrodp ,1,13)) * 0.000001 01417611
/* PDRROCPU RO mode Cpu time */ 01417711
offs = offs+12 01417811
usrrocpu = c2x(SUBSTR(INPUT_REC,offs,8)) 01417911
usrrocpu = x2d(SUBSTR(usrrocpu,1,13)) * 0.000001 01418011
/* PDRKY8DS Key8 mode Disp time */ 01418111
offs = offs+12 01418211
usrk8dp = c2x(SUBSTR(INPUT_REC,offs,8)) 01418311
usrk8dp = x2d(SUBSTR(usrk8dp ,1,13)) * 0.000001 01418411
/* PDRKY8CP Key8 mode Cpu time */ 01418511
offs = offs+12 01418611
usrk8cpu = c2x(SUBSTR(INPUT_REC,offs,8)) 01418711
usrk8cpu = x2d(SUBSTR(usrk8cpu,1,13)) * 0.000001 01418811
/* PDRKY9DS Key9 mode Disp time */ 01418911
offs = offs+12 01419011
usrk9dp = c2x(SUBSTR(INPUT_REC,offs,8)) 01419111
usrk9dp = x2d(SUBSTR(usrk9dp ,1,13)) * 0.000001 01419211
/* PDRKY9CP Key9 mode Cpu time */ 01419311
offs = offs+12 01419411
usrk9cpu = c2x(SUBSTR(INPUT_REC,offs,8)) 01419511
usrk9cpu = x2d(SUBSTR(usrk9cpu,1,13)) * 0.000001 01419611
/* PDRL8CPU L8 mode Cpu time */ 01419711
offs = offs+12 01419811
usrl8cpu = c2x(SUBSTR(INPUT_REC,offs,8)) 01419911
usrl8cpu = x2d(SUBSTR(usrl8cpu,1,13)) * 0.000001 01420011
/* PDRL9CPU L9 mode Cpu time */ 01420111
offs = offs+12 01420211
usrl9cpu = c2x(SUBSTR(INPUT_REC,offs,8)) 01420311
usrl9cpu = x2d(SUBSTR(usrl9cpu,1,13)) * 0.000001 01420411
/* PDRS8CPU S8 mode Cpu time */ 01420511
offs = offs+12 01420611
usrs8cpu = c2x(SUBSTR(INPUT_REC,offs,8)) 01420711
usrs8cpu = x2d(SUBSTR(usrs8cpu,1,13)) * 0.000001 01420811
/* PDRX8CPU X8 mode Cpu time */ 01420911
offs = offs+12 01421011
usrx8cpu = c2x(SUBSTR(INPUT_REC,offs,8)) 01421111
usrx8cpu = x2d(SUBSTR(usrx8cpu,1,13)) * 0.000001 01421211
/* PDRX9CPU X9 mode Cpu time */ 01421311
offs = offs+12 01421411
usrx9cpu = c2x(SUBSTR(INPUT_REC,offs,8)) 01421511
usrx9cpu = x2d(SUBSTR(usrx9cpu,1,13)) * 0.000001 01421611
/* PDRT9CPU T9 mode Cpu time */ 01421711
offs = offs+12 01421811
usrt9cpu = c2x(SUBSTR(INPUT_REC,offs,8)) 01421911
usrt9cpu = x2d(SUBSTR(usrt9cpu,1,13)) * 0.000001 01422011
01422111
/* response time */ 01422210
resp=stopts-startts 01422310
if resp < 0 then 01422410
do 01422510
resp=resp* (-1) 01422610
end 01423010
/* sometimes start time is greater than stop time , swap values */ 01423114
if startt > stopt then 01423215
do 01423314
m=startt 01423414
startt=stopt 01423514
stopt =m 01423614
end 01423714
/* PDRDB2RC db2 requests */ 01423814
offs = 2133 01424013
db2reqt = c2d(SUBSTR(INPUT_REC,offs,4)) 01425011
offs = offs + 4 01426013
mqreqt = c2d(SUBSTR(INPUT_REC,offs,4)) 01440011
Return 02290000
06800005
write_header: 06801005
say 'CSV file ' oufl ' will be produced' 06810000
queue "Date,Sysid,Applid,Tran,userid,startt,stopt,Resp,CPU,", 06820014
"DB2req,MQreq,", 06830014
"UsrDisp,", 06911014
"UsrSusp, " , 06911114
"UsrWait," , 06911214
"usrQRdp, " , 06911314
"usrQRcpu," , 06911414
"usrOTdp ," , 06911514
"usrOTcpu," , 06912014
"usrROdp ," , 06913010
"usrROcpu," , 06914010
"usrK8dp ," , 06915014
"usrK8cpu," , 06916014
"usrK9dp ," , 06917014
"usrK9cpu," , 06918014
"usrL8cpu," , 06919014
"usrL9cpu," , 06919114
"usrS8cpu," , 06919214
"usrX8cpu," , 06920014
"usrX9cpu," , 06930014
"usrT9cpu" 06940014
06950000
"EXECIO" queued() "DISKW OUFL" 06951010
return 06952010
06960010
write_report: 06961010
if startt < mintime then mintime=startt 06970007
if stopt > maxtime then maxtime=stopt 06970107
totcpu=totcpu + usrcput 06970207
totdb2r = totdb2r + db2reqt 06970307
totelap=totelap+resp 06970409
reco= reco+ 1 06971007
/*rows in excel format */ 06980000
queue tsdate !! ',' !! sysid !! ',' , 06990005
!! applid !! ',' , 07000005
!! Tranid !! ',' , 07010005
!! userid !! ',' , 07020005
!! '"' !! startt !! '"' !! ',' , 07030016
!! '"' !! stopt !! '"' !! ',' , 07031016
!! resp !! ',' , 07050005
!! usrcput !! ',' , 07060005
!! db2reqt !! ',' , 07070010
!! mqreqt !! ',' , 07080010
!! usrdisp !! ',' , 07090010
!! usrsusp !! ',' , 07160010
!! usrwt !! ',' , 07190010
!! usrqrdp !! ',' , 07220010
!! usrqrcpu !! ',' , 07250010
!! usrotdp !! ',' , 07280010
!! usrotcpu !! ',' , 07310010
!! usrROdp !! ',' , 07330010
!! usrROcpu !! ',' , 07370010
!! usrk8dp !! ',' , 07382010
!! usrk8cpu !! ',' , 07385010
!! usrk9dp !! ',' , 07388010
!! usrk9cpu !! ',' , 07389210
!! usrl8cpu !! ',' , 07389510
!! usrl9cpu !! ',' , 07389810
!! usrs8cpu !! ',' , 07390110
!! usrx8cpu !! ',' , 07390410
!! usrx9cpu !! ',' , 07390710
!! usrt9cpu 07391010
07400010
"EXECIO" queued() "DISKW OUFL" 07401010
return 07410000
stck: 07420000
Arg TUNITS 07430000
TIMESTAMP = Copies(0,26) /* force result length=26 */ 07440000
Address linkpgm "BLSUXTOD TUNITS TIMESTAMP" 07450000
/* variable Timestamp has the value of timestamp */ 07460000
TSDate=substr(timestamp,1,10) 07470000
TSTime=substr(timestamp,12,15) 07480005
hh=substr(tstime,1,2) 07481105
mm=substr(tstime,4,2) 07482005
ss=substr(tstime,7,2) 07483005
cc=substr(tstime,10,6)*0.000001 07484005
tots=hh*3600+60*60+ss+cc 07485005
return 07490000
JCL :
//Your jobcard
//* INPUT : &HLQ..SMFEXTS (EXTRACT SMF STATS ONLY) 00030000
//* VOIR JOB EXTSMF 00040000
//* X'64' = 100 00050000
//* 00060000
// SET HLQ=SYSTMP.WSYNGUD 00070000
//* 00070200
//* REX110 00120000
//* INPUT : &HLQ..REPORT.CICMOL 00130002
//* OUTPUT1: &HLQ..REPORT.TRAN 00140001
//* ARGUMENT 1 : PREFIX FOR DATASET NAME (HLQ VALUE) 00160000
//REX110 EXEC PGM=IKJEFT01,DYNAMNBR=5,REGION=0M,COND=(4,LT), 00170003
// PARM='REX110PE &HLQ' 00180002
//SYSEXEC DD DISP=SHR,DSN=your_pds 00190000
//SYSTSPRT DD SYSOUT=* 00200000
//SYSPRINT DD SYSOUT=* 00210000
//SYSTSIN DD DUMMY 00220000
// 00230000
Thursday, June 23, 2016
REXX READS to read IFCID401 Static Statement Cache and provide a CSV dataset for analysis
Update 17/01/2018
Add some more fields and functionnality, as i start using it to :
- Have a (cheap) picture of the running packages at a moment.
- Report and select the top 5 packages that should be looked at
IFCID401 enables what IBM calls "Statement level monitoring" , with the column StmId provided (QW0401ID) , you can find easily your culprit SQL text.
SELECT STATEMENT FROM SYSIBM.SYSPACKSTMT WHERE
COLLID = Given_Collid
AND NAME = Given_Pkg_Name
AND HEX(CONTOKEN) = Given_PkgToken
AND STMT_ID = Given_StmId
(Your sql text will be truncated depending on the program you use to select, If using SPUFI change the column width default - DSNTEP2 default is 120 bytes for a char column)
IFCID400 must be started, otherwise the data returned is not complete
This Rexx first starts IFCID400, then starts IFCID401.
I don't have a technical explication of the purpose of the switch on IFCID400 (and the same for IFCID318 for the dyn. statement cache). So if some one knows, please drop me a line ... Thanks
When a package is just executed, we have an image of its (statistics > 0) , but when there is a long time after its execution, the package is still readable but with all stats field = 0.
You have here the picture of the same package (with 2 sql statements = 2 records) at different iteration
Get the code here : (Update 27/05/2020)
https://github.com/ndt98/Db2z/blob/master/ifc401.rex
JCL to execute the RExx above
//IFC401 EXEC PGM=IKJEFT01,DYNAMNBR=25,ACCT=SHORT, 00040000
// REGION=4096K 00050000
//* 2 E PARAMETRE NB ITERATION 00060027
//* 3 E PARAMETRE NB SECONDS WAIT BETW. ITERATION 00060127
//* 4 E PARAMETRE GPMIN (FILTER) 00060227
//STEPLIB DD DSN=SYSPRM.WSYNGUD.LOAD,DISP=SHR 00061000
//SYSEXEC DD DISP=SHR,DSN=SYSPRM.WSYNGUD.REXX 00070000
//SYSUDUMP DD SYSOUT=* 00120000
//SYSTSPRT DD SYSOUT=* 00130000
//SYSOUT DD SYSOUT=* 00140000
//SYSTSIN DD * 00150000
IFC401 SYSTMP.WSYYYXX DBP0 5 300 10 00151027
Friday, May 6, 2016
A rexx program to put a pds in sequential format
I use my PC as a "data store" , where i have anything on it ( Manuals, Redbooks, documents, presentations ...). And with the help of a search software (Archivarius seems to be the best search software that i've found after multiple tests) , i am able to find anything on my PC (A sort of Google search on my PC) .
So , it is interesting for me to be able to retrieve data that are on PDS from my search software.
This Rexx reads a PDS , put it in a sequential format , so i can ftp it on my PC. It provides also jcl that you can use to reload the sequential into a PDS format.
It is adapted from a Rexx that i've found on the web (unfortunately i am not able to find the first author...should be from the site of The American Programmer )
/* REXX PDS2SEQ */
/* UNLOAD PDS MEMBERS TO SEQUENTIAL */
/* WITH ./ ADD NAME= COMMANDS FOR MEMBERS */
ARG PDS outp
If PDS = "" then do
Say "Please type in the name of the input PDS without quotes"
Pull PDS
If PDS = "" then exit
end
If outp= "" then do
Say "Please type in the name of the output without quotes"
Say "sequential dataset that will hold data"
Pull outp
If outp = "" then exit
end
outp = "'" || outp || "'"
IF SYSDSN(outp) = "OK" then
"DELETE" outp "PURGE"
SAY "Creating " outp
"ALLOC DDN(outdd) MOD REUSE SPACE(300,150) TRACKS",
"LRECL(80) BLKSIZE(800) RECFM(F B)",
"DSN("outp")"
Say "Press ENTER to continue";pull
IF SYSDSN("'"pds"'") <> "OK" THEN DO
SAY "PDS NOT USABLE"
SAY SYSDSN("'"pds"'")
EXIT 8
END
ADDRESS ISPEXEC "CONTROL ERRORS RETURN"
CALL INIT
DO 9999 /* limit for testing. change to FOREVER in real life */
/* EACH EXECUTION OF THIS CMD GIVES ONE MORE MEMBER NAME */
/* NAME OF MEMBER IS IN VARIABLE member */
ADDRESS ISPEXEC "LMMLIST DATAID("DATAID1") OPTION(LIST)",
"MEMBER(member) STATS(YES)"
/*Non-zero RC means no more members*/
IF RC = 0 THEN CALL DISPLAY_MEMBER
ELSE LEAVE /* break out of loop */
END
ADDRESS ISPEXEC "LMMLIST DATAID("DATAID1") OPTION(FREE)"
ADDRESS ISPEXEC "LMCLOSE DATAID("DATAID1")"
ADDRESS ISPEXEC "LMFREE DATAID("DATAID1")"
/* at end, write ENDUP, JCL delimiter */
SAY "Program successfully executed - Check your output dataset"
SAY i "members processed"
QUEUE "./ ENDUP"
queue "!!"
queue "//"
queue "//* END \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"
"EXECIO " queued() " DISKW outdd (FINIS)"
"FREE DDN(outdd)"
EXIT 0 /* logical end of program */
INIT:
i=0
/* write JCL at beginning of step */
PDS = translate(PDS," ","'") /* drop apost */
PDS = space(PDS,0) /* drop spaces */
QUEUE "//* Generated by rexx PDS2SEQ "
QUEUE "//* CHANGE userid to your userid "
QUEUE "//* change PDS if desired"
QUEUE "//* put jobcard at top, submit"
QUEUE "//*DELETE EXEC PGM=IEFBR14"
QUEUE "//*DD1 DD DSN="PDS","
QUEUE "//* DISP=(MOD,DELETE),UNIT=SYSDA,SPACE=(TRK,0)"
QUEUE "//*"
QUEUE "//LOAD EXEC PGM=IEBUPDTE,PARM='NEW'"
QUEUE "//SYSPRINT DD SYSOUT=*"
QUEUE "//SYSUT2 DD DSN="PDS","
QUEUE "// DISP=(NEW,CATLG,DELETE),"
QUEUE "// DCB=(DSORG=PO,LRECL=80,BLKSIZE=8000,RECFM=FB),"
QUEUE "// UNIT=SYSDA,"
QUEUE "// SPACE=(TRK,(10,05,20),RLSE)"
QUEUE "//*"
QUEUE "//SYSIN DD DATA,DLM='!!'"
"EXECIO " queued() " DISKW outdd (FINIS)"
/* LIB MGT ACCESSES THE DATASET */
ADDRESS ISPEXEC "LMINIT DATAID(DATAID1) DATASET('"PDS"') ENQ(SHR)"
/* LIKE AN OPEN WITH REGULAR FILES */
ADDRESS ISPEXEC "LMOPEN DATAID("DATAID1") OPTION(INPUT)"
RETURN
DISPLAY_MEMBER:
i=i+1
/*SAY "MEMBER NAME " member
SAY "RECORDS " ZLCNORC */
in_pds = PDS"("member")"
in_pds = SPACE(in_pds,0)
/* at beginning of each member, write blank line & ADD command */
DOT_SLASH_ADD.1 = ""
"EXECIO 1 DISKW outdd (STEM DOT_SLASH_ADD.)"
DOT_SLASH_ADD.1 = "./ ADD NAME="member
"EXECIO 1 DISKW outdd (STEM DOT_SLASH_ADD.)"
"ALLOC DDN(indd) SHR REUSE DSN('"in_pds"')"
"EXECIO * DISKR indd (STEM indd. FINIS)"
"EXECIO " indd.0 " DISKW outdd (STEM indd.)"
if rc <> 0 then Do
say "**********************************************"
say " Error writing seq file: " rc
say " Abnormal end "
say "**********************************************"
Exit 8
end
/*
"REPRO INDATASET("in_pds")",
"OUTFILE(outdd)" */
TRACE OFF
RETURN
ERROR: /* CALL ON ERROR SENDS HERE. DISPLAYS ISPF ERR INFO */
SAY "PROGRAM LIBLIST DID NOT WORK"
SAY ZERRMSG
SAY ZERRSM
SAY ZERRLM
So , it is interesting for me to be able to retrieve data that are on PDS from my search software.
This Rexx reads a PDS , put it in a sequential format , so i can ftp it on my PC. It provides also jcl that you can use to reload the sequential into a PDS format.
It is adapted from a Rexx that i've found on the web (unfortunately i am not able to find the first author...should be from the site of The American Programmer )
/* REXX PDS2SEQ */
/* UNLOAD PDS MEMBERS TO SEQUENTIAL */
/* WITH ./ ADD NAME= COMMANDS FOR MEMBERS */
ARG PDS outp
If PDS = "" then do
Say "Please type in the name of the input PDS without quotes"
Pull PDS
If PDS = "" then exit
end
If outp= "" then do
Say "Please type in the name of the output without quotes"
Say "sequential dataset that will hold data"
Pull outp
If outp = "" then exit
end
outp = "'" || outp || "'"
IF SYSDSN(outp) = "OK" then
"DELETE" outp "PURGE"
SAY "Creating " outp
"ALLOC DDN(outdd) MOD REUSE SPACE(300,150) TRACKS",
"LRECL(80) BLKSIZE(800) RECFM(F B)",
"DSN("outp")"
Say "Press ENTER to continue";pull
IF SYSDSN("'"pds"'") <> "OK" THEN DO
SAY "PDS NOT USABLE"
SAY SYSDSN("'"pds"'")
EXIT 8
END
ADDRESS ISPEXEC "CONTROL ERRORS RETURN"
CALL INIT
DO 9999 /* limit for testing. change to FOREVER in real life */
/* EACH EXECUTION OF THIS CMD GIVES ONE MORE MEMBER NAME */
/* NAME OF MEMBER IS IN VARIABLE member */
ADDRESS ISPEXEC "LMMLIST DATAID("DATAID1") OPTION(LIST)",
"MEMBER(member) STATS(YES)"
/*Non-zero RC means no more members*/
IF RC = 0 THEN CALL DISPLAY_MEMBER
ELSE LEAVE /* break out of loop */
END
ADDRESS ISPEXEC "LMMLIST DATAID("DATAID1") OPTION(FREE)"
ADDRESS ISPEXEC "LMCLOSE DATAID("DATAID1")"
ADDRESS ISPEXEC "LMFREE DATAID("DATAID1")"
/* at end, write ENDUP, JCL delimiter */
SAY "Program successfully executed - Check your output dataset"
SAY i "members processed"
QUEUE "./ ENDUP"
queue "!!"
queue "//"
queue "//* END \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"
"EXECIO " queued() " DISKW outdd (FINIS)"
"FREE DDN(outdd)"
EXIT 0 /* logical end of program */
INIT:
i=0
/* write JCL at beginning of step */
PDS = translate(PDS," ","'") /* drop apost */
PDS = space(PDS,0) /* drop spaces */
QUEUE "//* Generated by rexx PDS2SEQ "
QUEUE "//* CHANGE userid to your userid "
QUEUE "//* change PDS if desired"
QUEUE "//* put jobcard at top, submit"
QUEUE "//*DELETE EXEC PGM=IEFBR14"
QUEUE "//*DD1 DD DSN="PDS","
QUEUE "//* DISP=(MOD,DELETE),UNIT=SYSDA,SPACE=(TRK,0)"
QUEUE "//*"
QUEUE "//LOAD EXEC PGM=IEBUPDTE,PARM='NEW'"
QUEUE "//SYSPRINT DD SYSOUT=*"
QUEUE "//SYSUT2 DD DSN="PDS","
QUEUE "// DISP=(NEW,CATLG,DELETE),"
QUEUE "// DCB=(DSORG=PO,LRECL=80,BLKSIZE=8000,RECFM=FB),"
QUEUE "// UNIT=SYSDA,"
QUEUE "// SPACE=(TRK,(10,05,20),RLSE)"
QUEUE "//*"
QUEUE "//SYSIN DD DATA,DLM='!!'"
"EXECIO " queued() " DISKW outdd (FINIS)"
/* LIB MGT ACCESSES THE DATASET */
ADDRESS ISPEXEC "LMINIT DATAID(DATAID1) DATASET('"PDS"') ENQ(SHR)"
/* LIKE AN OPEN WITH REGULAR FILES */
ADDRESS ISPEXEC "LMOPEN DATAID("DATAID1") OPTION(INPUT)"
RETURN
DISPLAY_MEMBER:
i=i+1
/*SAY "MEMBER NAME " member
SAY "RECORDS " ZLCNORC */
in_pds = PDS"("member")"
in_pds = SPACE(in_pds,0)
/* at beginning of each member, write blank line & ADD command */
DOT_SLASH_ADD.1 = ""
"EXECIO 1 DISKW outdd (STEM DOT_SLASH_ADD.)"
DOT_SLASH_ADD.1 = "./ ADD NAME="member
"EXECIO 1 DISKW outdd (STEM DOT_SLASH_ADD.)"
"ALLOC DDN(indd) SHR REUSE DSN('"in_pds"')"
"EXECIO * DISKR indd (STEM indd. FINIS)"
"EXECIO " indd.0 " DISKW outdd (STEM indd.)"
if rc <> 0 then Do
say "**********************************************"
say " Error writing seq file: " rc
say " Abnormal end "
say "**********************************************"
Exit 8
end
/*
"REPRO INDATASET("in_pds")",
"OUTFILE(outdd)" */
TRACE OFF
RETURN
ERROR: /* CALL ON ERROR SENDS HERE. DISPLAYS ISPF ERR INFO */
SAY "PROGRAM LIBLIST DID NOT WORK"
SAY ZERRMSG
SAY ZERRSM
SAY ZERRLM
Rexx SMF 102 Decoder :Trace SQL statement, host var and sqlcode, process IFCID 224 and IFCID 366
3/2019 Note : I just upload the Rexx to process IFCID 366 dynamically (using IFI READS / READA functions) , it is the recommended way to go to process IFCID 366 IFCID 376 as this allows you the get the full SQL statement for dynamic SQL.
28/01/2019 Add EDM requests stats
06/2018 : Dataset Extend IFCID 258 - This is useful to track how often and when a dataset extend happens. As you may know, dataset extension is very bad in a performance perspective of an insert because DB2 has to find the place foe the insert in all the dataset before extending it, so if you can avoid this, your program will appreciate this. We have resolved a big issue of locks during inserts by using dsn1prnt (to check how the data is spread) and tracking ifcid 258. We've learned that when the spacemap page is locked by a thread, a concurent thread wil try to use another spacemap for inserts and if it can't after trying all the spacemap it will extend your dataset ...You you can get a dataset full even if there is nothing in the table ...
19/12/2017 : Recently i need to analyse my workfile datasets usage , IFCID 342 is perfect for this, it reports each dataset used by Sort or Temprorary Table, the Kilobytes used and the Thread token which uses it ... By reading the stats, i can clarify a lot of things not written in the manual ...
24/08/2017 : i use this rexx with the IFCID224 option to list all the packages with Invalid SProc (we have thousands of this by hour) - when IBM says that the impact is 0-10% CPU , it is worth to do the rebind.
13/4/2017 : New source updated
10/4/2017 : This rexx supports also IFCID 376, as IFCID366 and IFCID376 have exactly the same mapping in SDSNMACS. IFCID376 is just an enhanced, aggregate version of IFCID366. So use this rexx and just modify the code
(...) ifi ifcid = 376 then ... call qw0366... (...)
06/04/2017 : Minor corrections
6/12/2016 : Add processing of IFCID366 (useful to indentify unsupported functions) and IFCID224 (Invalid Select Procedures encountered invalid, this arrives after applying new PTF or migration) To keep REX102 simple, the program can process one kind of work in a pass, this means that if you process IFCID366 , set IFCID366=Y and the others options to N
18/08/2016 : Add the possibility to trace SQL activity : SQL statement text, including values of the input Host variables and SQLCODE. I wrote this because we had a weird behavior in our development DB2 with a program, and i wanted to trace the SQL text with host variables value used at execution time. It was impossible because there is a bug in Mainview. So, i wrote my own SQL activity decoder to be independent of the possible bugs/ configuration problem ... from the monitoring softwares.
The trace to start is :
- STA TRACE(PERFM) DEST(SMF) AUTHID(xxx) CLASS(30) IFCID(63,247,53,58,350)
63 and 350 is for SQL text
247 is for Host Variable in input
53 and 58 is for the SQLCA ( sqlcode ...)
=> if dynamic sql : 63,53,58,247 (or 350 instead of 63 if long sql text > 2500 bytes)
=> if static sql : 247,53,58 (sql statement is not provided but there is the stmtno that you can use to get the corresponding sql text in SYSPACKSTMT)
It is easy to decode DB2 SMF data when you already have an example of Rexx code for SMF 100 and 101. SMF102 program is copied from the SMF100 code.
Please use the same JCL as SMF100 jcl (don't forget to change x'64' to x'66' in the Sort Step to select only SMF102 records)
SMF102 is generally attached to "Performance Data" , and we can think that if we don't have a PERFM trace started, we don't have anything in SMF102. In fact, even if this trace is not started, you have a lot of IFCID records generated in SMF 102 data. Just try at your site.
This program has been written to decode ifcid 90 (DB2 commands text),as at our site, the DB2 commands are not tracked and so there is no way to know who has submitted a STOP DATABASE at 03:10 AM (It is nearly what happened to me when i was on call). We have Mainview , and it can have the commands logged (Monitor trace, not SMF) , but for a reason i don't know it doesn't work (and it is a quite long history to make it work ...). [Correction : it seems that commands are also logged in the DB2 logs]
There is an interesting feature : Display all the IFCID read from the SMF102 dataset, with a description and number of appearance, this helps to see if the ifcid data is there but never exploited (CPU waste ...), or to detect something wrong is one counter is abnormally high (Number of Dataset extend events for example). I will integrate this display in SMF100 and SMF101 rexx programs later.
23/06/2016 : The rexx now can decode IFCID316 and IFCID401 (Dynamic and static statement cache) , IFCID401 is produced in SMF only when the package is flushed from the cache, so it is more complete to read it with READS command (Check the READS sample that i provide also)
Have a nice day.
Duc
Get the code here (Update 27/05/2020)
https://github.com/ndt98/Db2z/blob/master/rex102.rex
Subscribe to:
Comments (Atom)