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

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