Let us start this with a question:
How many times, that we tried to generate the utilities from RC/Query and thought the generated job should be generated with LISTDEF so the execution can be done in parallel.
How many times, we could have thought controlling the number of objects in every LISTDEF should be in our hands while generating a utility ?
Well, now it is possible with RC/Query and we can do all these for almost all IBM utilities. Our motive is to run the tasks in parallel as much as possible in Db2 to save cost and job execution time.
With RC/Query while generating any utility, parallelism is maintained and the JCL will be generated with LISTDEF format across all the objects with many extra features added.
With PTF SO10430, CA RC/Query has been enhanced to now generate utility jobs for the most frequently used IBM utilities in LISTDEF format.
The behavior is similar for all IBM utilities although the following examples only show IBM REORG.
The following features are now provided:
Feature #1: General Utilities in Parallel
In this example, we are generating an IBM REORG from CA RC/Query. We used the new profile option Utility Object Count in 1 Step with a value of 5 to include up to 5 objects in each LISTDEF.
//SYSIN DD *
LISTDEF LIST1 INCLUDE TABLESPACE DSNDB04.DSNR1RHZ
INCLUDE TABLESPACE NEWDB45.F75862B1
INCLUDE TABLESPACE SRIDB11.CADB2TS
INCLUDE TABLESPACE SRIDB11.CADB2TS1
INCLUDE TABLESPACE SRIDB12.CADB2TS
TEMPLATE CDATA1 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..C&TI(1,4).'
UNIT TAPE RETPD 15 VOLCNT(5) DISP(NEW,CATLG,DELETE)
TEMPLATE CDATA2 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..C&TI(1,4).'
UNIT(SYSDA) DISP(NEW,CATLG,DELETE)
SPACE (4,20) CYL
TEMPLATE UDATA1 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..T&TI(1,4).'
UNIT(SYSDA) DISP(NEW,CATLG,DELETE)
SPACE (4,20) CYL
REORG TABLESPACE LIST LIST1
COPYDDN(CDATA1)
UNLDDN(UDATA1)
STATISTICS TABLE(ALL) INDEX(ALL)
SORTDEVT SYSDA SORTNUM 03
UNLOAD CONTINUE
SHRLEVEL CHANGE
DEADLINE CURRENT TIMESTAMP + 1 HOUR
MAXRO 240
LONGLOG
DRAIN DELAY 1200
DRAIN_WAIT 30 RETRY 4 RETRY_DELAY 10
/*
Feature #2: Change the number of objects per LISTDEF
Use the new Utility Object Count in 1 Step profile option to change the number of objects to include in each LISTDEF. Enter PROF;2 and set this profile option to a different number. For example, change from 5 to 10 as shown.
COMMAND ===>
PRINT PARAMETERS: (For Printing Query Report being viewed)
PRINT SQL PAGE ==> N (Y/N; SQL Page N/A if Printline Size < 41)
PROCESSING OPTIONS: (For Query Reports)
VIEW LEVELS ==> ALL - For Object Dependency Report.
QUALIFIERS ==> Y (Y/N) - For Obj. Dep. and some Ver. 2.1 cols.
MAX LINES ==> ALL - For List and Detail reports.
FULL DETAIL ==> N (Y/N) - For reports based on multiple tables.
OMIT ORDER BY ==> N (Y/N) - For List and Detail reports.
CA-UTILITIES ==> N (Y/N) - To use CA-Utilities.
SAVED REPORTS ==> Y (Y/N) - To use customized reports.
MATCHCASE ==> CAPS (ON, - Enables matchcase.
OFF, - Disables matchcase. ( SQL Intensive )
CAPS) - Force object names to upper case.
CAPNATS ==> N (Y/N) - Capitalize National Characters.
ALL LOCATIONS ==> Y (Y/N) - For searches in Plan/Package Report.
UNDERSCORE WILDCARD ==> ON (ON/OFF)- To process underscore as wildcard.
OPTIONS NOTIFY ==> ON (ON/OFF)- For options and notify pop-ups.
PREFILL COMMAND ==> Y (Y/N) - Prefill command line for utility.
ISOLATION LEVEL ==> (Blank - Global Level ISOLATION value.
CS, - Cursor Stability.
UR, - Uncommited Read)
UTILITY OBJECT ==> 10 - Number of objects per step in
COUNT IN 1 STEP utility processing JCL.
Then generate the REORG utility again. We now observe LISTDEF has 10 objects included.
//SYSIN DD *
LISTDEF LIST1 INCLUDE TABLESPACE DSN00027.ABCDEDF
INCLUDE TABLESPACE DSN03928.ACCOUNT
INCLUDE TABLESPACE DSN05161.AAA111A
INCLUDE TABLESPACE DSN06818.ABCLOKES
INCLUDE TABLESPACE DSN06819.ABCLOKES
INCLUDE TABLESPACE DSN08195.AAA112
INCLUDE TABLESPACE DSN08262.AAA111
INCLUDE TABLESPACE PFG.SACT
INCLUDE TABLESPACE SRIDB11.LPBFTS1
INCLUDE TABLESPACE SRIDB11.
TEMPLATE CDATA1 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..C&TI(1,4).'
UNIT TAPE RETPD 15 VOLCNT(5) DISP(NEW,CATLG,DELETE)
TEMPLATE CDATA2 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..C&TI(1,4).'
UNIT(SYSDA) DISP(NEW,CATLG,DELETE)
SPACE (4,20) CYL
TEMPLATE UDATA1 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..T&TI(1,4).'
UNIT(SYSDA) DISP(NEW,CATLG,DELETE)
SPACE (4,20) CYL
REORG TABLESPACE LIST LIST1
COPYDDN(CDATA1)
UNLDDN(UDATA1)
STATISTICS TABLE(ALL) INDEX(ALL)
SORTDEVT SYSDA SORTNUM 03
UNLOAD CONTINUE
SHRLEVEL CHANGE
DEADLINE CURRENT TIMESTAMP + 1 HOUR
MAXRO 240
LONGLOG
DRAIN DELAY 1200
DRAIN_WAIT 30 RETRY 4 RETRY_DELAY 10
/*
Feature #3: Convenience/Flexibility
For the convenience of users, CA RC/Query will generate 2 TEMPLATEs for IMAGECOPY outputs defaulting the image-copy output to TAPE. However, if a user wants to allocate the image-copy output to DASD (as part of COPY or REORG utilities), then the effort needed is a 1 character change. CDATA1 to CDATA2 in generated utility statement.
Feature #4: REBUILD included Indexes
REBUILD of indexes that can be performed individually or at tablespace level. While generating the REBUILD utility statement at the individual index level, LISTDEF approach is used.
//SYSIN DD *
LISTDEF LIST1 INCLUDE INDEX ADUSR01.I04011A1
INCLUDE INDEX ADUSR01.I04011BF1
INCLUDE INDEX ADUSR01.I04011BF2
INCLUDE INDEX ADUSR01.I04011BF3
INCLUDE INDEX ADUSR01.I04011BP
INCLUDE INDEX ADUSR01.I04011F1
INCLUDE INDEX ADUSR01.I04011F2
INCLUDE INDEX ADUSR01.I04011F3
INCLUDE INDEX ADUSR01.I04011P
REBUILD INDEX LIST LIST1
SORTDEVT SYSDA SORTNUM 03
SHRLEVEL CHANGE
/*
While generating REBUILD utility at the tablespace level, TEMPLATE is not followed.
//SYSIN DD *
REBUILD INDEX(ALL) TABLESPACE SRIDB01.SP2PBR02
SORTDEVT SYSDA SORTNUM 03
SHRLEVEL CHANGE
/*
Feature #5: UNLOAD generated using DSNUPROC
The UNLOAD utility now generates the utility using DSNUPROC – 1 step for 1 object (or 1 utility statement).
//UTIL0001 EXEC PGM=DSNUTILB,REGION=4096K,PARM='D11A'
- - - - - - - - - - - - - - - - 10 Line(s) not Displayed => lines are excluded
//SYSIN DD *
UNLOAD FROM TABLE ADUSR01.AAA111;
/*
Feature #6: DSNTIAL line command now unloads using DSNTIAUL utility
Generating Unload job in DSNTIAUL format can be achieved by DSNTIAUL command.
Feature #7 (Honoring the existing behavior):
If a user does not want to specify how many utility statements to include in each JCL step, select Y for 2nd value below and keep the (Recommended) option blank or empty.
RQPUTCNT
(Recommended) Enter the maximum number
of utility statements you want included
in each step (1 to 99) :
Enter Y to include one step for each
object per utility statement or one
step for all utility statements (Y|N) : N
PF1 = HELP
However, to improve system performance when multiple objects are processed, we recommend that you make use of the recommended option.In the following example, the user entered 5 for the recommended option (include up to 5 objects in 1 LISTDEF statement (1 step).
RQPUTCNT
(Recommended) Enter the maximum number
of utility statements you want included
in each step (1 to 99) : 5
Enter Y to include one step for each
object per utility statement or one
step for all utility statements (Y|N) : N
PF1 = HELP
------------------------------
Srinivas Adupa
Product Marketing Engineer
Broadcom
Lisle
------------------------------