DB2 JCL Utilities: Complete Reference Guide

DB2 for z/OS maintenance is driven by a suite of batch utilities invoked through JCL. Unlike DB2 LUW where you issue maintenance commands from a CLI, DB2 z/OS utilities are batch programs submitted as jobs to JES. They handle everything from statistics collection and reorganisation to backup, recovery, and bulk data loading.

This guide covers every DBA and developer must-know utility with complete, production-ready JCL examples.

Utility Overview

All DB2 utilities share the same JCL skeleton: they execute DSNUTILB (or a related program) with the subsystem name, and read utility control statements from a SYSIN DD:

//UTILSTEP EXEC PGM=DSNUTILB,PARM='DSN1,UTILNAME'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
  utility-control-statements
/*

The PARM value has the format 'subsystem-id,utility-name'. STEPLIB must include the DB2 load libraries.

RUNSTATS — Collecting Optimizer Statistics

RUNSTATS collects statistical information about tables and indexes and stores it in the DB2 catalog. The query optimizer uses these statistics to choose access paths. Stale statistics are one of the leading causes of poor DB2 query performance.

Basic RUNSTATS on a Tablespace

//RUNSTATS EXEC PGM=DSNUTILB,PARM='DSN1,RUNSTATS'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
  RUNSTATS TABLESPACE EMPDB.EMPTS01
    TABLE(ALL)
    INDEX(ALL)
    SHRLEVEL CHANGE
/*

RUNSTATS with Column Statistics

For complex queries with range predicates, collect column-level statistics:

//SYSIN    DD *
  RUNSTATS TABLESPACE EMPDB.EMPTS01
    TABLE(EMPSCHM.EMPLOYEE
        COLUMN(WORKDEPT, SALARY, HIREDATE)
        COLGROUP(WORKDEPT, JOB)
    )
    INDEX(ALL KEYCARD)
    SHRLEVEL CHANGE
    REPORT YES
/*

COLGROUP collects statistics on combinations of columns — valuable for multi-column predicates. REPORT YES generates a report of the statistics collected.

REORG — Reorganising Tablespaces and Indexes

As rows are inserted, updated, and deleted, DB2 tablespaces become fragmented. Free space accumulates in random locations, and if clustering is defined on an index, the physical row order drifts from the logical order. REORG reclaims this space and restores order.

REORG TABLESPACE — Online (SHRLEVEL CHANGE)

//REORG    EXEC PGM=DSNUTILB,PARM='DSN1,REORG'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SORTOUT  DD UNIT=SYSDA,SPACE=(CYL,(500,200))
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(500,200))
//SYSIN    DD *
  REORG TABLESPACE EMPDB.EMPTS01
    SHRLEVEL CHANGE
    DEADLINE NONE
    SORTDEVT SYSDA
    SORTNUM 4
/*

SHRLEVEL CHANGE keeps the tablespace available throughout. DB2 REORGs in three phases: unload → reload → switch. During the switch phase (seconds), exclusive access is taken briefly.

REORG with DISCARD

//SYSIN    DD *
  REORG TABLESPACE EMPDB.EMPTS01
    SHRLEVEL CHANGE
    DISCARD DD(DSCARD)
    MAPPINGTABLE EMPSCHM.REORG_MAP
/*

DISCARD allows rows matching a filter to be removed during reorganisation.

REORG INDEX

//SYSIN    DD *
  REORG INDEX EMPSCHM.EMPNO_IDX
    SHRLEVEL CHANGE
/*

COPY — Image Backup

COPY creates an image copy — a backup — of a tablespace or index space. DB2 recovery depends on image copies combined with log records.

Full Image Copy

//COPY     EXEC PGM=DSNUTILB,PARM='DSN1,COPY'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSCOPY  DD DSN=BACKUP.EMPDB.EMPTS01.FULL,
//            DISP=(NEW,CATLG),
//            UNIT=TAPE,
//            SPACE=(CYL,(100,50))
//SYSIN    DD *
  COPY TABLESPACE EMPDB.EMPTS01
    FULL YES
    SHRLEVEL CHANGE
    COPY1
/*

Incremental Image Copy

An incremental copy captures only pages changed since the last full copy — much faster for large tablespaces:

//SYSIN    DD *
  COPY TABLESPACE EMPDB.EMPTS01
    FULL NO
    SHRLEVEL CHANGE
/*

Copy to Two Targets Simultaneously

DB2 supports writing to a primary and local backup simultaneously:

//SYSCOPY  DD DSN=BACKUP.EMPDB.EMPTS01.FULL.PRI,...
//SYSCOPY2 DD DSN=BACKUP.EMPDB.EMPTS01.FULL.SEC,...
//SYSIN    DD *
  COPY TABLESPACE EMPDB.EMPTS01
    FULL YES
    COPYDDN(SYSCOPY,SYSCOPY2)
    SHRLEVEL CHANGE
/*

RECOVER — Restoring from Backup

RECOVER restores a tablespace to a consistent state using image copies and log records.

Recover to Current

//RECOVER  EXEC PGM=DSNUTILB,PARM='DSN1,RECOVER'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  RECOVER TABLESPACE EMPDB.EMPTS01
/*

DB2 locates the most recent image copy from the bootstrap dataset (BSDS) and applies all log records up to the current end of log.

Recover to a Point in Time

//SYSIN    DD *
  RECOVER TABLESPACE EMPDB.EMPTS01
    TORBA X'000000A1B2C3D4'
/*

TORBA (To Recovery Log Byte Address) recovers to a specific point in the log. TOTIME '2026-04-20-14.30.00' can also specify a timestamp.

CHECK DATA — Referential Integrity Validation

After a recovery or bulk load, CHECK DATA verifies referential integrity:

//CHKDATA  EXEC PGM=DSNUTILB,PARM='DSN1,CHECK'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  CHECK DATA TABLESPACE EMPDB.EMPTS01
    SCOPE ALL
    LOBERROR REPORT
/*

LOAD — Bulk Data Loading

LOAD is the fastest way to insert large volumes of data:

//LOAD     EXEC PGM=DSNUTILB,PARM='DSN1,LOAD'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSREC   DD DSN=INPUT.EMPLOYEE.DATA,DISP=SHR
//SYSERR   DD SYSOUT=*
//SORTOUT  DD UNIT=SYSDA,SPACE=(CYL,(200,100))
//SYSIN    DD *
  LOAD DATA INDDN(SYSREC)
    INTO TABLE EMPSCHM.EMPLOYEE
    (EMPNO    POSITION(1:6)    CHAR(6),
     FIRSTNME POSITION(7:18)   CHAR(12),
     MIDINIT  POSITION(19:19)  CHAR(1),
     LASTNAME POSITION(20:34)  CHAR(15),
     WORKDEPT POSITION(35:37)  CHAR(3),
     SALARY   POSITION(38:46)  DECIMAL EXTERNAL(9))
    RESUME YES
    SHRLEVEL CHANGE
/*

RESUME YES appends to existing data. REPLACE deletes existing rows before loading.

UNLOAD — Extracting Data

UNLOAD extracts data from DB2 tablespaces to sequential datasets:

//UNLOAD   EXEC PGM=DSNUTILB,PARM='DSN1,UNLOAD'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSREC   DD DSN=EXTRACT.EMPLOYEE.DATA,
//            DISP=(NEW,CATLG),
//            UNIT=SYSDA,
//            SPACE=(CYL,(50,25))
//SYSIN    DD *
  UNLOAD TABLESPACE EMPDB.EMPTS01
    FROM TABLE EMPSCHM.EMPLOYEE
    (EMPNO, FIRSTNAME, LASTNAME, WORKDEPT, SALARY)
    WHERE SALARY > 50000
/*

REBIND — Refreshing Package Access Plans

When statistics change significantly after RUNSTATS, existing packages may still use the old access plan. REBIND forces DB2 to recompile access plans:

//REBIND   EXEC PGM=IKJEFT01
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(DSN1)
  REBIND PACKAGE(EMPPROJ.EMPRPT(1))
  END
/*

A typical maintenance sequence for a tablespace is: RUNSTATS → REBIND → (if needed) REORG → COPY.

Maintenance Schedule Best Practices

A well-run DB2 shop follows a maintenance cycle:

Daily: Check for tablespaces in RECP (Recovery Pending) or RBDP (Rebuild Pending) status.

Weekly: RUNSTATS on volatile tablespaces; REBIND packages with outdated access plans; incremental COPY.

Monthly: Full COPY; REORG on tablespaces with REORGCHK indicators; CHECK INDEX on large indexes.

After bulk operations: RUNSTATS immediately after any LOAD or mass INSERT/DELETE/UPDATE affecting > 10% of rows.

Next Steps

With JCL utilities mastered, you can maintain DB2 z/OS databases at a professional level. The next critical mainframe DB2 skill is embedding SQL directly in COBOL programs. See DB2 COBOL Embedded SQL for a complete guide. For the full learning path, visit the DB2 Mastery course hub.

Monitoring Utility Status

Check the status of running or pending utilities:

-DSN1 DISPLAY UTILITY(*)

This shows all active and recent utilities, their phase, and percentage complete. To terminate a stuck utility:

-DSN1 TERM UTILITY(utility-id)

Check tablespace status with:

-DSN1 DISPLAY DATABASE(EMPDB) SPACE(*) RESTRICT

Look for RO (Read Only), STOP, RECP (Recovery Pending), or RBDP (Rebuild Pending) status codes, which indicate the tablespace needs attention.

CHECK INDEX — Index Integrity

After a RECOVER or hardware issue, indexes may be inconsistent with the data. CHECK INDEX verifies consistency:

//CHKIDX   EXEC PGM=DSNUTILB,PARM='DSN1,CHECK'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  CHECK INDEX ALL TABLESPACE EMPDB.EMPTS01
/*

If CHECK INDEX finds errors, the index space enters RBDP (Rebuild Pending) status. Run REBUILD INDEX to fix it:

//SYSIN    DD *
  REBUILD INDEX (ALL) TABLESPACE EMPDB.EMPTS01
    SHRLEVEL CHANGE
/*

MODIFY RECOVERY — Housekeeping the SYSIBM.SYSCOPY Catalog

Over time, the SYSIBM.SYSCOPY table accumulates records from old image copies. MODIFY RECOVERY prunes old entries:

//MODREC   EXEC PGM=DSNUTILB,PARM='DSN1,MODIFY'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  MODIFY RECOVERY TABLESPACE EMPDB.EMPTS01
    DELETE AGE(30)
/*

This removes recovery history records older than 30 days, keeping the SYSCOPY table manageable. Always ensure you have at least one full image copy retained before pruning.