Application compile and bind JCL in DB2 for z/OS

The preparation pipeline

Think of program preparation as two products that must stay in step: a load module the operating system can execute, and a package (plus a plan that names that package) Db2 uses at run time. If you relink without rebinding after SQL changes, or rebind without shipping a matching load module, you get SQLCODE -805 or confusing “old SQL” behaviour.

IBM ships cataloged procedures such as DSNHICOB (COBOL), DSNHC (C), DSNHPLI (PL/I), and DSNHASM (assembler) in the sample library. Many shops still write explicit steps so COND codes, library names, and bind options are obvious in the listing. Change managers (Endevor, ChangeMan, and similar) usually call the same programs under the covers.

You can skip a separate precompile step if the COBOL compiler runs the Db2 coprocessor (SQL compiler option). The coprocessor still produces a DBRM (or uses a package path your shop defined). Beginners should still understand DSNHPC, because listings, DBRMLIB members, and Abend-Aids talk in those DD names.

DSNTEP2 JCL (batch dynamic SQL)

DSNTEP2 is an IBM sample PL/I program that executes SQL dynamically. It is the batch cousin of SPUFI: SELECT, INSERT, UPDATE, DELETE, many DDL statements (CREATE, ALTER, DROP, COMMENT ON), GRANT/REVOKE, COMMIT, ROLLBACK, and the static CONNECT / SET CONNECTION / RELEASE statements for remote work. IBM also ships DSNTEP4, which uses multi-row FETCH for faster SELECT output.

Since Db2 Version 6, IBM provides an object-code DSNTEP2, so you do not need a PL/I compiler just to run it. Source is still in SDSNSAMP if you want to change behaviour. The install jobs bind a plan (the name is site-specific; older books show names like DSNTEP81 or DSNTEP12). Always use the plan and RUNLIB your DBA documented.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//TEP2    EXEC PGM=IKJEFT01,DYNAMNBR=20,REGION=0M
//STEPLIB  DD DISP=SHR,DSN=DSN.V12.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(DB2T)
  RUN PROGRAM(DSNTEP2) PLAN(DSNTEP12) -
      LIB('DSN.V12.RUNLIB.LOAD') -
      PARMS('/ALIGN(MID)')
  END
//SYSIN    DD *
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00';
/*

DSNTEP2 parameters you will see on PARMS:

• ALIGN(MID) — center SELECT output on the page (default)
• ALIGN(LHS) — left-align output
• MIXED / NOMIXED — whether SYSIN may contain DBCS mixed data (NOMIXED is the usual default)
• SQLTERM(c) — statement terminator when the SQL itself contains semicolons (stored-procedure text is the classic case; % is a common substitute)

DSNTEP2 is an application, not a utility. A failed statement does not automatically undo earlier successful INSERT/UPDATE/DELETE statements in the same job unless you COMMIT carefully or ROLLBACK. Treat multi-statement change scripts like production DML: know your commit points.

DSNTIAUL JCL (sample unload)

DSNTIAUL is an assembler sample program that unloads table or view data into sequential data sets in a form the LOAD utility can reload. It can unload some or all rows from up to 100 tables in one run. It writes data to SYSREC00 through SYSREC99 and LOAD control statements to SYSPUNCH. Rows are copied; they are not deleted from the table.

SYSIN normally lists qualified table or view names, one per SELECT that DSNTIAUL will run. If you pass SQL in the RUN PARMS, SYSIN instead contains complete SQL statements (dynamic non-SELECT is allowed as well as SELECT). Static CONNECT, SET CONNECTION, and RELEASE must be uppercase when you use that path.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//STEPLIB  DD DISP=SHR,DSN=DSN.V12.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=HR.UNLOAD.EMPLOYEE,DISP=(,CATLG,DELETE),
//            UNIT=SYSDA,SPACE=(CYL,(10,5),RLSE)
//SYSPUNCH DD DSN=HR.UNLOAD.EMP.LOADIN,DISP=(,CATLG,DELETE),
//            UNIT=SYSDA,SPACE=(TRK,(2,1),RLSE)
//SYSTSIN  DD *
  DSN SYSTEM(DB2T)
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
      LIB('DSN.V12.RUNLIB.LOAD')
  END
//SYSIN    DD *
HR.EMPLOYEE
/*

DSNTIAUL predates the UNLOAD utility and remains useful for LOAD-shaped extracts and for shops that customized the sample. For large partitioned table spaces, online UNLOAD options, or fancy formatting, prefer the UNLOAD utility. Check the installed plan name; sample jobs sometimes bind DSNTIAU1 / DSNTIAUL-style names per release.

DCLGEN JCL

DCLGEN (declarations generator) reads the catalog and writes an SQL DECLARE TABLE statement plus a host-language structure that matches the columns. You can run it from the DB2I DCLGEN panel or as a DSN subcommand under IKJEFT01. You need SELECT (or ownership / DBADM / SYSADM) on the table or view.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//DCLGEN  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(DB2T)
  DCLGEN TABLE(HR.EMPLOYEE) -
         LIBRARY('HR.DCLGEN.COBOL(EMPLOYEE)') -
         ACTION(REPLACE) -
         LANGUAGE(IBMCOB) -
         STRUCTURE(DCL-EMPLOYEE) -
         NAMES(EMP-) -
         COLSUFFIX(YES) -
         INDVAR(YES) -
         APOST
  END
/*

Important DCLGEN options:

• TABLE — table or view; OWNER and AT(location) qualify when needed
• LIBRARY — existing sequential or PDS data set; member name is typical for COBOL copy libraries
• ACTION(ADD | REPLACE) — ADD fails if the member exists; REPLACE overlays
• LANGUAGE(IBMCOB | PLI | C | CPP) — IBMCOB is Enterprise COBOL
• NAMES(prefix) with COLSUFFIX(YES) — field names like EMP-EMPNO instead of numbered EMP-1, EMP-2 (numbered prefixes are hard to read)
• INDVAR(YES) — indicator array named with an I prefix on the table name
• APOST / QUOTE — COBOL string delimiter; must match precompiler APOST/QUOTE
• LABEL(YES) — include LABEL ON text as comments
• DCLBIT(YES) — DECLARE VARIABLE for FOR BIT DATA columns (needed with the coprocessor SQLCCSID option in COBOL)
• RMARGIN(STD | WIDE) — wrap after column 72 or 80

Underscores in column names become hyphens in COBOL. Include the member with EXEC SQL INCLUDE (not a plain COPY) so the precompiler sees DECLARE TABLE. Do not hand-edit generated DCLGEN output as a habit; regenerate when the table changes.

Precompile JCL (DSNHPC)

Program DSNHPC scans source for EXEC SQL blocks. It writes a DBRM member and modified source in which SQL is replaced by calls to the language interface. HOST must match the language: HOST(IBMCOB) for Enterprise COBOL.

1
2
3
4
5
6
7
8
9
10
11
12
13
//PC      EXEC PGM=DSNHPC,REGION=0M,
//             PARM='HOST(IBMCOB),APOST,SOURCE,XREF,FLAG(I)'
//STEPLIB  DD DISP=SHR,DSN=DSN.V12.SDSNLOAD
//DBRMLIB  DD DISP=SHR,DSN=HR.DBRMLIB.DATA(EMPPROG)
//SYSLIB   DD DISP=SHR,DSN=HR.DCLGEN.COBOL
//         DD DISP=SHR,DSN=HR.COPYLIB.COBOL
//SYSIN    DD DISP=SHR,DSN=HR.COBOL.SOURCE(EMPPROG)
//SYSCIN   DD DSN=&&SYSCIN,DISP=(NEW,PASS),
//            UNIT=SYSDA,SPACE=(CYL,(5,5)),
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0)
//SYSPRINT DD SYSOUT=*
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT2   DD UNIT=SYSDA,SPACE=(CYL,(1,1))

Common PARM pieces: APOST or QUOTE for SQL string delimiters, SOURCE and XREF for listings, STDSQL(YES) if you require BEGIN/END DECLARE SECTION, TWOPASS versus ONEPASS for when host variables must be declared relative to DECLARE CURSOR, and ATTACH(CAF) or ATTACH(RRSAF) when you do not link a dummy DSNHLI. Treat precompiler return code 8+ as a stop; many shops also stop on 4.

Compile JCL

The COBOL compiler reads the modified source from SYSCIN, not the original EXEC SQL program. Enterprise COBOL uses IGYCRCTL. Point SYSLIB at DCLGEN and COPY libraries for any non-SQL COPY statements. SQL INCLUDE was already expanded (or at least recognized) in the precompile step.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//COB     EXEC PGM=IGYCRCTL,COND=(4,LT,PC),
//             PARM='LIB,OBJECT,APOST,RENT,MAP,LIST'
//STEPLIB  DD DISP=SHR,DSN=IGY.SIGYCOMP
//SYSLIB   DD DISP=SHR,DSN=HR.DCLGEN.COBOL
//         DD DISP=SHR,DSN=HR.COPYLIB.COBOL
//SYSIN    DD DSN=&&SYSCIN,DISP=(OLD,DELETE)
//SYSLIN   DD DSN=&&LOADSET,DISP=(NEW,PASS),
//            UNIT=SYSDA,SPACE=(CYL,(5,5))
//SYSPRINT DD SYSOUT=*
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(2,2))
//SYSUT2   DD UNIT=SYSDA,SPACE=(CYL,(2,2))
//SYSUT3   DD UNIT=SYSDA,SPACE=(CYL,(2,2))
//SYSUT4   DD UNIT=SYSDA,SPACE=(CYL,(2,2))
//SYSUT5   DD UNIT=SYSDA,SPACE=(CYL,(2,2))
//SYSUT6   DD UNIT=SYSDA,SPACE=(CYL,(2,2))
//SYSUT7   DD UNIT=SYSDA,SPACE=(CYL,(2,2))

Match APOST/QUOTE with the precompiler. RENT is typical for reentrant CICS and Language Environment. If you use the coprocessor, this step also talks to Db2 and you allocate DBRMLIB on the compile step instead of a separate PC step.

Link-edit JCL

The binder (IEWL or HEWL) combines the object deck with Language Environment and the Db2 language interface. The precompiler generated calls to entry point DSNHLI. That symbol is satisfied by the attachment module you include.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//LKED    EXEC PGM=IEWL,COND=((4,LT,PC),(4,LT,COB)),
//             PARM='XREF,LET,LIST,RENT,AMODE=31,RMODE=ANY'
//SYSLIB   DD DISP=SHR,DSN=CEE.SCEELKED
//         DD DISP=SHR,DSN=DSN.V12.SDSNLOAD
//         DD DISP=SHR,DSN=HR.LOADLIB
//SYSLIN   DD DSN=&&LOADSET,DISP=(OLD,DELETE)
//         DD DDNAME=SYSIN
//SYSLMOD  DD DISP=SHR,DSN=HR.LOADLIB(EMPPROG)
//SYSPRINT DD SYSOUT=*
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(2,1))
//SYSIN    DD *
  INCLUDE SYSLIB(DSNELI)
  NAME EMPPROG(R)
/*

For CICS, include DSNCLI and follow your CICS translator/stub standards. For IMS, include DFSLI000. Mixing the wrong stub with the wrong run-time (batch load module in CICS, for example) fails at first SQL. IBM also provides DSNULI, the universal language interface, which picks an attachment at run time.

Bind JCL

Binding turns the DBRM into an executable package. Current practice is BIND PACKAGE into a collection, then BIND PLAN with PKLIST. MEMBER on BIND PLAN still exists as a compatibility path but is not the model for new work.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//BIND    EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//STEPLIB  DD DISP=SHR,DSN=DSN.V12.SDSNLOAD
//DBRMLIB  DD DISP=SHR,DSN=HR.DBRMLIB.DATA
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(DB2T)
  BIND PACKAGE(HRCOLL) MEMBER(EMPPROG) -
       ACTION(REPLACE) QUALIFIER(HR) -
       ISOLATION(CS) VALIDATE(BIND) -
       EXPLAIN(YES) OWNER(HRBIND)
  BIND PLAN(EMPPLAN) -
       PKLIST(HRCOLL.*) -
       ACTION(REPLACE) ISOLATION(CS) -
       RETAIN
  END
/*

DBRMLIB on the bind step must contain the member the precompiler wrote. Collection, qualifier, owner, isolation, and VALIDATE(BIND|RUN) are shop standards—copy a working job rather than inventing options. After bind, the run step is again IKJEFT01 with RUN PROGRAM(EMPPROG) PLAN(EMPPLAN) and STEPLIB pointing at your load library plus SDSNLOAD.

Keep the DBRM, package consistency token, and load module together through promotion. Binding the test DBRM into production while shipping an old load module is a classic -805. The same JCL skeleton works in DB2I program preparation panels if you prefer interactive prep before you automate it.

Coprocessor versus DSNHPC in JCL

Enterprise COBOL can compile SQL with the Db2 coprocessor (SQL option) instead of a separate DSNHPC step. The compiler then needs Db2 libraries, a DBRMLIB (or the equivalent output your install uses), and SQL processing options that used to live on the precompiler PARM. Listings look like compiler listings with SQL diagnostics mixed in. Many automated build tools still generate a PC step because it matches older DSNHICOB samples and because DBA reviews are written around DBRM member timestamps from DSNHPC.

Do not run both a full DSNHPC precompile and the coprocessor on the same source unless you know which step owns the DBRM. Double processing produces confusing DBRMs and duplicate SQL errors. Pick one path per program and document it in the JCL comment block.

Return codes: treat PC or compile RC greater than 4 as a stop (COND=(4,LT,...)). Bind RC 8 means the package was not created; do not run the program against yesterday's package and today's load module. Keep SYSPRINT from PC, COB, LKED, and BIND in the job output—most “it worked on my TSO panel” failures are a missed DBRMLIB concatenation or the wrong ssid on DSN SYSTEM.

Explain It Like I'm Five

Your COBOL homework has special Db2 sentences mixed in. A helper (the precompiler) copies those sentences into a separate notebook (the DBRM) and rewrites the homework so the COBOL teacher can grade it. Another helper binds the notebook so the library (Db2) knows how to fetch the books. DSNTEP2 is a ready-made robot that already knows how to ask the library questions without you writing a whole COBOL program. DCLGEN is a sticker sheet of the table's column names so you do not misspell them. Link-edit is gluing the right phone adapter (TSO, CICS, or IMS) onto the program so the call actually rings Db2.

Exercises

1. List the five preparation outputs (DCLGEN member, DBRM, object, load module, package) and which JCL step creates each one.
2. Write DSNTEP2 SYSTSIN/SYSIN to SELECT two columns from a table you know, using ALIGN(LHS).
3. Explain when SYSIN for DSNTIAUL is a table name versus a SELECT statement.
4. Choose NAMES + COLSUFFIX versus a numeric NAMES prefix and justify the choice for a table with column EMPNO.
5. Your link-edit has unresolved DSNHLI. Which module do you INCLUDE for a TSO batch job, and which would you INCLUDE instead for CICS?