DB2 utility JCL patterns

The common utility JCL skeleton

IBM lets you write the EXEC yourself or call the cataloged procedure DSNUPROC. Both end up running DSNUTILB from an APF-authorized library.

1
2
3
4
5
6
7
8
//jobname JOB ...
//UTIL  EXEC PGM=DSNUTILB,PARM='DB2T,HR.COPY.HRTS'
//STEPLIB  DD DISP=SHR,DSN=DSN.DB2T.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSIN    DD *
  utility-control-statement
/*

1
2
3
4
//UTIL  EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.COPY.HRTS'
//SYSIN    DD *
  utility-control-statement
/*

PARM is system,uid[,utproc]. The UID identifies the job in SYSIBM.SYSUTIL for -DISPLAY UTILITY, -TERM UTILITY, and restart. Default UID is often userid.jobname. Restart uses the same UID with RESTART or RESTART(PHASE). PREVIEW checks syntax (and expands LISTDEF) without running the utility.

In data sharing, SYSTEM can be a group or subgroup attachment name. The job must run on a z/OS image that can attach to that group unless you use group attach.

Shared DD names

Prefer TEMPLATE for image copies and unload files so you are not editing GDGs in JCL every week:

1
2
3
TEMPLATE LOC DSN HR.COPY.&DB..&TS..D&DATE..T&TIME.
         UNIT SYSDA DISP(NEW,CATLG,DELETE) SPACE(50,10) CYL
COPY TABLESPACE HRDB.HRTS COPYDDN(LOC) FULL YES SHRLEVEL REFERENCE

LOAD JCL

LOAD needs input (SYSREC or INDDN), error (SYSERR), and usually sort work (SYSUT1, SORTOUT). Add SYSMAP when unique indexes or ENFORCE CONSTRAINTS can discard rows. Add SYSDISC if you want discarded records kept—LRECL/RECFM must match SYSREC. COPYDDN takes an inline image copy so LOG NO does not leave COPY-pending.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//LOAD  EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.LOAD.EMP'
//SYSREC   DD DISP=SHR,DSN=HR.EMP.DATA
//SYSERR   DD DSN=HR.EMP.SYSERR,DISP=(MOD,DELETE,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE)
//SYSMAP   DD DSN=HR.EMP.SYSMAP,DISP=(MOD,DELETE,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE)
//SYSDISC  DD DSN=HR.EMP.SYSDISC,DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE)
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(50,20),RLSE)
//SORTOUT  DD UNIT=SYSDA,SPACE=(CYL,(50,20),RLSE)
//UTPRINT  DD SYSOUT=*
//SYSIN    DD *
  LOAD DATA INDDN(SYSREC) REPLACE LOG YES
    INTO TABLE HR.EMPLOYEE
/*

Details of each LOAD DD are on the LOAD utility JCL page. Keep work data set DISP restart-friendly (IBM samples often use MOD,DELETE,CATLG).

UNLOAD JCL

UNLOAD writes rows to UNLDDN (default SYSREC) and can punch a LOAD statement to PUNCHDDN (often SYSPUNCH). FROMCOPY unloads from an image copy instead of the live space—then you do not need the table space allocated for read.

1
2
3
4
5
6
7
8
9
10
11
//UNLD  EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.UNLD.EMP'
//SYSREC   DD DSN=HR.EMP.UNLOAD(+1),DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,SPACE=(CYL,(50,20),RLSE)
//SYSPUNCH DD DSN=HR.EMP.PUNCH(+1),DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,SPACE=(TRK,(5,5),RLSE)
//SYSIN    DD *
  UNLOAD TABLESPACE HRDB.HRTS
    FROM TABLE HR.EMPLOYEE
    UNLDDN SYSREC
    PUNCHDDN SYSPUNCH
/*

REORG JCL

SHRLEVEL NONE REORG uses unload/reload work files (SYSREC, SYSUT1, SORTOUT). SHRLEVEL CHANGE/REFERENCE uses shadow data sets (often dynamically allocated) plus a mapping table for CHANGE. SORTDEVT/SORTNUM reduce hard-coded SORTWKnn DDs. COPYDDN is required in spirit for recoverability after LOG NO; REFERENCE/CHANGE take an inline copy during execution.

1
2
3
4
5
6
7
8
9
//REORG EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.REO.HRTS'
//SYSIN   DD *
  TEMPLATE CP DSN HR.COPY.&DB..&TS..REO.&DATE.
  REORG TABLESPACE HRDB.HRTS
       SHRLEVEL CHANGE
       COPYDDN(CP)
       SORTDEVT SYSDA SORTNUM 8
       STATISTICS TABLE(ALL) INDEX(ALL)
/*

RUNSTATS JCL

RUNSTATS is the smallest skeleton: SYSIN, SYSPRINT, STEPLIB. COLGROUP/FREQVAL may allocate sort work dynamically. SHRLEVEL CHANGE lets queries run during collection.

1
2
3
4
5
6
//STATS EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.RST.HRTS'
//SYSIN   DD *
  RUNSTATS TABLESPACE HRDB.HRTS
    TABLE(ALL) INDEX(ALL)
    SHRLEVEL CHANGE UPDATE ALL REPORT YES
/*

COPY JCL

COPY needs an output data set per COPYDDN/RECOVERYDDN name, or a TEMPLATE. Full copies often use a GDG (+1). FlashCopy needs FCCOPYDDN pointing at a VSAM template, not a sequential tape.

1
2
3
4
5
6
7
8
9
//COPY  EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.CPY.HRTS'
//SYSCOPY DD DSN=HR.COPY.HRTS(+1),DISP=(NEW,CATLG,DELETE),
//           UNIT=TAPE,LABEL=(1,SL)
//SYSIN    DD *
  COPY TABLESPACE HRDB.HRTS
       COPYDDN(SYSCOPY)
       FULL YES
       SHRLEVEL REFERENCE
/*

RECOVER JCL

RECOVER usually dynamically allocates image copies from SYSIBM.SYSCOPY. You often supply only SYSIN and SYSPRINT. Tapes must be in the catalog (or available to DFSMShsm recall). For TORBA/TOLOGPOINT, paste the hex from REPORT RECOVERY or QUIESCE output. Do not point a SYSCOPY DD at the backup unless you are using a documented FROMCOPY-style override—the default path is SYSCOPY catalog lookup.

1
2
3
4
5
//RECV  EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.RCV.HRTS'
//SYSIN   DD *
  RECOVER TABLESPACE HRDB.HRTS
  RECOVER INDEXSPACE HRDB.XENP1
/*

CHECK DATA and CHECK INDEX JCL

CHECK DATA needs sort work and an error DD; exception tables must already exist if you use FOR EXCEPTION. CHECK INDEX needs WORKDDN sort files. SHRLEVEL CHANGE variants exist with extra restrictions (including FlashCopy in some releases).

1
2
3
4
5
6
7
8
9
10
//CHK   EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.CHK.HRTS'
//SYSERR  DD DSN=HR.CHK.SYSERR,DISP=(NEW,CATLG,DELETE),
//           UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE)
//SYSUT1  DD UNIT=SYSDA,SPACE=(CYL,(20,10),RLSE)
//SORTOUT DD UNIT=SYSDA,SPACE=(CYL,(20,10),RLSE)
//UTPRINT DD SYSOUT=*
//SYSIN   DD *
  CHECK DATA TABLESPACE HRDB.HRTS SCOPE ALL
  CHECK INDEX (ALL) TABLESPACE HRDB.HRTS
/*

REBUILD JCL

REBUILD INDEX reconstructs an index from the table. Provide sort work (SYSUT1/SORTOUT or SORTDEVT). COPYDDN can take an inline index copy if the index is COPY YES. STATISTICS collects index stats; follow with table-space RUNSTATS if you need matching table numbers.

1
2
3
4
5
6
//RBLD  EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.RBD.XEMP'
//SYSIN   DD *
  REBUILD INDEX (HR.XEMP1)
       SORTDEVT SYSDA SORTNUM 6
       STATISTICS
/*

DD checklist by utility

SORTWKnn DDs appear when you do not use SORTDEVT. Allocate enough cylinders; B37 on sort is a common REORG/LOAD failure. Region=0M (or a large REGION) on the EXEC statement is a frequent shop standard for utility steps.

Restart, TERM, and PREVIEW

• Same UID + PARM RESTART resumes at the last commit point
• RESTART(PHASE) resumes at the start of the current phase
• Do not change SYSIN in incompatible ways across restart (object list, REPLACE versus RESUME)
• -TERM UTILITY (uid) if you will not restart; then fix JCL and submit a new UID
• OPTIONS PREVIEW or PARM PREVIEW before a production LOAD REPLACE

Explain It Like I'm Five

Utility JCL is a delivery truck form. The truck is always named DSNUTILB. The address is PARM (which Db2 house, which tracking number UID). SYSIN is the packing list (COPY, LOAD, REORG). SYSPRINT is the receipt. Each job type brings extra boxes: LOAD brings a crate of new toys (SYSREC), COPY brings an empty photo album (SYSCOPY), RECOVER usually brings nothing extra because it already knows where the albums are filed (SYSCOPY catalog). IKJEFT01 is a different truck that only delivers doorbell messages (DSN), not furniture.

Exercises

1. Convert a raw EXEC PGM=DSNUTILB COPY job to DSNUPROC form without changing SYSIN.
2. List every DD a LOAD REPLACE with discards and an inline copy needs, and which can become TEMPLATE.
3. Write RECOVER JCL for a table space plus its COPY YES index with no copy DD statements, and explain why that works.
4. Add OPTIONS PREVIEW to a LISTDEF COPY job and describe what SYSPRINT should show.
5. Document your shop’s restart DISP standard for SYSUT1 (MOD versus NEW) and why it matters after ABEND S322.