DB2 LOAD utility JCL

How the job is invoked

Online utilities share one batch program: DSNUTILB. The EXEC PARM names the subsystem ID and a utility ID (UID) that must be unique while the utility is active. IBM’s cataloged procedure DSNUPROC wraps DSNUTILB so you set SYSTEM= and UID= instead of coding PGM= yourself.

1
2
3
//LOADJOB  JOB (ACCT),'DB2 LOAD',CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID
//LOAD     EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.LOAD.EMP',UTPROC=''
//*  Equivalent idea: EXEC PGM=DSNUTILB,PARM='DB2T,HR.LOAD.EMP'

STEPLIB (or JOBLIB) must include the Db2 SDSNLOAD library for that subsystem’s runtime. Mixing SDSNLOAD from the wrong release is a classic “why did DSNUTILB abend at once?” error. UTPROC left blank means a new execution, not a restart of a stopped utility.

DD statements at a glance

Rename any of these with INDDN, ERRDDN, MAPDDN, DISCARDDN, WORKDDN, or COPYDDN in the control statement—then the JCL DD (or TEMPLATE) must use that new name. Defaults exist so IBM samples and shop standards stay readable.

A working skeleton

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
//LOADJOB  JOB (ACCT),'DB2 LOAD',CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID
//LOAD     EXEC DSNUPROC,SYSTEM=DB2T,UID='HR.LOAD.EMP',UTPROC=''
//SYSREC   DD DISP=SHR,DSN=HR.EMPLOYEE.SYSREC
//SYSDISC  DD DSN=HR.EMPLOYEE.SYSDISC,
//            DISP=(MOD,DELETE,CATLG),UNIT=SYSDA,
//            SPACE=(CYL,(10,5),RLSE)
//SYSERR   DD DSN=HR.EMPLOYEE.SYSERR,
//            DISP=(MOD,DELETE,CATLG),UNIT=SYSDA,
//            SPACE=(CYL,(5,5),RLSE)
//SYSMAP   DD DSN=HR.EMPLOYEE.SYSMAP,
//            DISP=(MOD,DELETE,CATLG),UNIT=SYSDA,
//            SPACE=(CYL,(5,5),RLSE)
//SYSUT1   DD DSN=HR.EMPLOYEE.SYSUT1,
//            DISP=(MOD,DELETE,CATLG),UNIT=SYSDA,
//            SPACE=(CYL,(20,10),RLSE)
//SORTOUT  DD DSN=HR.EMPLOYEE.SORTOUT,
//            DISP=(MOD,DELETE,CATLG),UNIT=SYSDA,
//            SPACE=(CYL,(20,10),RLSE)
//UTPRINT  DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  LOAD DATA INDDN SYSREC REPLACE
       DISCARDDN SYSDISC DISCARDS 100
       ERRDDN SYSERR MAPDDN SYSMAP
       WORKDDN(SYSUT1,SORTOUT)
    INTO TABLE HR.EMPLOYEE
    (EMPNO    POSITION(1:6)  CHAR,
     LASTNAME POSITION(7:21) CHAR)
/*
 

IBM samples often use DISP=(MOD,DELETE,CATLG) on work files so a restart can append, a successful job deletes them from the catalog path you chose, and a failure keeps them. Match SPACE to input size: SYSDISC should be able to hold the whole SYSREC if everything is rejected.

SYSREC (input)

Sequential, BSAM-readable. Tape, DASD, or an HFS/zFS file via TEMPLATE PATH. If SYSREC is on tape and work files use TEMPLATE, IBM tells you to include SPACE on those templates. Concatenation is allowed when you load several files of the same layout.

SYSDISC, SYSERR, SYSMAP

SYSDISC copies rejected input records—same LRECL as SYSREC. SYSERR is the error work file (always allocate it). SYSMAP correlates a table row identifier with the input record that caused an index or RI problem; skip it only when IBM says it is optional for your options (no unique indexes, no ENFORCE, no discards).

SYSUT1 and SORTOUT

Index keys are sorted here. Undersize them and DFSORT fails in BUILD. SORTNUM / SORTDEVT in the control statement can allocate extra sort work (SORTWKnn / SWnnWKmm) dynamically; you still need the WORKDDN pair.

DSNUPROC versus PGM=DSNUTILB

DSNUPROC fills in SYSPRINT, STEPLIB, and often UTPRINT. When you EXEC DSNUPROC, extra DDs are often coded as //DSNUPROC.SYSREC DD ... so they override the procedure. If you EXEC PGM=DSNUTILB yourself, you must supply STEPLIB to SDSNLOAD, SYSPRINT, SYSIN, and every work file. Both are correct; shops pick one standard.

1
2
3
4
5
6
7
8
//LOAD EXEC PGM=DSNUTILB,PARM='DB2T,HR.LOAD.EMP'
//STEPLIB  DD DISP=SHR,DSN=DB2T.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSREC   DD DISP=SHR,DSN=HR.EMPLOYEE.SYSREC
//SYSIN    DD *
  LOAD DATA REPLACE INTO TABLE HR.EMPLOYEE
/*

TEMPLATE and inline copies

Put TEMPLATE statements in SYSIN before LOAD to build GDG or timestamped data set names for SYSREC, SYSDISC, or COPYDDN. Inline COPY during LOAD needs a COPYDDN data set (often named SYSCOPY in older JCL). That copy is recorded in SYSIBM.SYSCOPY like a COPY utility output and is the usual way to avoid COPY-pending after LOG NO.

1
2
3
4
5
//SYSIN DD *
  TEMPLATE CPY DSN HR.EMP.&TS..D&DATE..T&TIME.
  LOAD DATA REPLACE LOG NO COPYDDN CPY NOCOPYPEND
    INTO TABLE HR.EMPLOYEE
/*

Operational checks before you submit

• UID unique — -DISPLAY UTILITY shows collisions; do not reuse an active UID
• Authorization — LOAD privilege on the database (or SYSADM / SYSCTRL as your shop allows)
• Object state — STOP/START access, COPY-pending, REORG-pending can block LOAD; check -DISPLAY DATABASE
• RI — ENFORCE CONSTRAINTS needs parent rows already loaded or you accept CHECK-pending
• Restart — same JCL UID and compatible SYSIN; work files not deleted

The next pages cover UNLOAD (which often builds the SYSREC and SYSPUNCH this JCL consumes) and COPY/RECOVER, which protect you after a bad REPLACE.

Explain It Like I'm Five

JCL is the shopping list and the kitchen. DSNUTILB is the cook. SYSIN is the recipe (LOAD … INTO TABLE). SYSREC is the grocery bag of ingredients. SYSDISC is the plate for food that came out wrong. SYSERR and SYSMAP are the cook’s notes about which potato caused the problem. SYSUT1 and SORTOUT are extra counter space for sorting index cards. The UID is the order number so if the oven stops, you can say “continue order HR.LOAD.EMP” instead of starting a new cake.

Exercises

1. Convert the DSNUPROC skeleton to PGM=DSNUTILB with an explicit STEPLIB.
2. List which DDs you can drop if the table has no indexes and you specify DISCARDS 0 with no SYSDISC.
3. Explain DISP=(MOD,DELETE,CATLG) on SYSUT1 in a restart scenario.
4. Add COPYDDN and LOG NO to the SYSIN of the skeleton and name the extra DD you need.
5. Find your shop’s SDSNLOAD data set name and DSNUPROC procedure library.