DB2 utilities overview and DSNUTILB

DSNUTILB in one picture

DSNUTILB must reside in an APF-authorized library. The JCL load library (STEPLIB or JOBLIB, typically prefix.SDSNLOAD) must match the maintenance level of the Db2 you attach to or you get ugly abends. PARM has three comma-separated pieces:

1
2
3
4
5
6
7
8
9
10
//LOAD01   EXEC PGM=DSNUTILB,REGION=0M,
//            PARM='DB2A,PAYLOAD'
//STEPLIB  DD DISP=SHR,DSN=DB2A.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSREC   DD DISP=SHR,DSN=PAYROLL.LOAD.FILE
//SYSIN    DD *
  LOAD DATA INDDN SYSREC RESUME YES
       INTO TABLE HR.PAY_TRAN
/*

Every online utility needs SYSIN. Many need more DDs (SYSREC, SYSCOPY, SYSUT1, SORTOUT, SYSDISC). TEMPLATE control statements can allocate those from patterns instead of hard-coded DD names. SYSPRINT is the utility message file; UTPRINT is sort messages.

IBM also ships procedure DSNUPROC, which EXECs DSNUTILB and provides STEPLIB, SYSPRINT, and UTPRINT. You still write SYSIN. Stored procedures DSNUTILU and DSNUTILS submit the same control text from an application.

In data sharing, PARM system can be the group or subgroup attach name. If you specify a member SSID, the job must run on a z/OS image where that member is active.

Utility IDs, DISPLAY, TERM, restart

The UID is how -DISPLAY UTILITY and -TERM UTILITY find your job. It is stored with phase information in the directory (SYSIBM.SYSUTIL). Duplicate UIDs fail if a utility with that ID is still known. If DSNU CLIST created the ID, it often looks like tso-userid.control-file-name; EXEC defaults often look like userid.jobname. Lowercase or special characters in a UID used on ALTER UTILITY need quotes.

• RESTART or RESTART(CURRENT) — resume at the last commit point inside a phase
• RESTART(PHASE) — resume at the beginning of the phase that was running
• PREVIEW — syntax-check (and list expansion) only

After -TERM UTILITY you cannot restart that execution. You submit a new utility. That is why you DISPLAY first: a LOAD in ENFORCE is a different conversation from a LOAD still in RELOAD.

Core utilities versus the Utilities Suite

Several utilities ship with Db2 at no extra charge. The rest are enabled by the Db2 Utilities Suite for z/OS (FMID such as HDBCC1K on Db2 12). All utilities can run on catalog, directory, and sample objects without the Suite. Application table spaces generally need the Suite for LOAD, COPY, REORG, and friends.

If a shop “cannot run LOAD,” check licensing and FMIDs before rewriting the job as INSERT. Catalog REORG and COPY of DSNDB01/DSNDB06 still work without the Suite.

Control statements you will see in every job

OPTIONS

Job-wide switches: PREVIEW, EVENT, FILTERDDN, and similar. OPTIONS PREVIEW is the SYSIN way to do what PARM PREVIEW does for the whole input stream.

LISTDEF

Names a list: INCLUDE TABLESPACES DATABASE PAYDB, EXCLUDE a space, wildcards. COPY, RUNSTATS, QUIESCE, and REORG can then say LIST PAYLIST instead of one statement per object.

TEMPLATE

A naming pattern for SYSCOPY, discard files, and work data sets, with variables such as &DB., &TS., &DATE., &TIME., &DSNUM. SORT work data sets are different: SORTDEVT/SORTNUM (or SORT DD statements) allocate those, not TEMPLATE.

EXEC SQL

A Suite utility that runs SQL inside the same job before LOAD INCURSOR (cross loader) or to set the scene. It is not SPUFI.

1
2
3
4
OPTIONS PREVIEW
LISTDEF PAYLIST INCLUDE TABLESPACES DATABASE PAYDB
TEMPLATE SCPY DSN PAY.&DB..&TS..D&DATE.
COPY LIST PAYLIST COPYDDN(SCPY)

Work data sets, messages, and zIIP

Almost every Suite utility needs more than SYSIN. COPY wants a copy data set (COPYDDN or TEMPLATE). LOAD wants SYSREC (or a cursor), often SYSDISC, SYSERR, SYSMAP, and sort work. REORG wants unload and work data sets unless you use SHRLEVEL CHANGE with a shadow and templates. UTPRINT is where DFSORT or DB2SORT writes; if it is missing, index build and REORG sort phases fail in confusing ways. SYSPRINT is the official utility message file — DSNU messages with reason codes live there, not in JESYSMSG alone.

SORTDEVT and SORTNUM (or explicit SORTWKnn DDs) allocate sort work on disk. TEMPLATE does not allocate sort work. Shops that convert every DD to TEMPLATE still leave SORTDEVT SYSALLDA on LOAD and REORG. Too many SORTNUM data sets can exhaust below-the-line storage and collapse parallel index build. ZPARM IGNSORTN YES ignores SORTNUM so the sort product picks a count.

Many utility phases are zIIP-eligible. That does not mean the job is free: RELOAD, log apply, and some I/O still run on general CPs. REGION=0M is common so the step can obtain what it needs; MEMLIMIT and IEFUSI still apply. Parallel index build and REORG unload parallelism are controlled by utility keywords and ZPARMs, not by DSN. If two LOAD jobs use the same UID, the second fails; if they use different UIDs on the same partition with SHRLEVEL NONE, they wait on drains or fail on restrictive states.

Authorization, objects, and restrictive states

Each utility has its own privilege: LOAD, COPY, RECOVER, REORG, RUNSTATS, CHECK, UNLOAD, and so on, plus database-level DBADM and system SYSADM/SYSCTRL. You can hold LOAD on a table and still fail COPY of the space. MLS (multi-level security) tables add extra checks. Utilities that touch catalog and directory objects (DSNDB01, DSNDB06) often require installation SYSADM and a documented maintenance window — CATMAINT is not a Tuesday afternoon experiment.

When a utility stops badly it can leave the object in a restrictive state: COPY-pending after LOAD LOG NO, CHECK-pending after ENFORCE NO, RECOVER-pending after a failed RECOVER, REORP after pending ALTERs. DISPLAY DATABASE RESTRICT shows those flags. Restarting with the same UID is the first choice if SYSUTIL still has the row. TERM UTILITY drops the row and you must fix the object with COPY, CHECK DATA, RECOVER, or a new LOAD before applications can use it. That is operationally different from a SQL ROLLBACK, which does not set COPY-pending.

Partition numbers in SYSIN and in DSNU messages are physical partition numbers unless a particular utility says it is using logical partitions. After an ALTER TABLE ROTATE PARTITION FIRST TO LAST, physical and logical numbers diverge — copy the PART integer from DISPLAY or the catalog, not from an old runbook.

How utilities differ from DSN and from SQL

DSN BIND does not load rows. INSERT is logged row by row and fires triggers according to SQL rules; LOAD can skip logging, rebuild indexes in bulk, and optionally skip triggers except in SHRLEVEL CHANGE. COPY is not BACKUP SYSTEM; RECOVER is not RESTORE SYSTEM. Utilities drain or claim objects and set restrictive states (COPY-pending, CHECK-pending, REORP). SQL does not set those states. Operators still use hyphen commands to DISPLAY and TERM the utility; they do not type LOAD DATA on the console.

A practical first job for a new DBA is OPTIONS PREVIEW with LISTDEF and TEMPLATE, then the real COPY or RUNSTATS without PREVIEW. Preview expands wildcards so you see whether the list accidentally includes DSNDB06. Restart discipline matters more than clever SYSIN: same UID, same object list, RESTART or RESTART(PHASE) as IBM documents for that utility. Changing REPLACE to RESUME on a restart of LOAD is how you corrupt a space. DSNUPROC is fine for copy-and-paste shops; custom JCL is fine if STEPLIB, SYSPRINT, and UTPRINT are never forgotten. Stored procedure DSNUTILU is how applications submit Unicode control text without a hand-built job, but the UID still shows up on DISPLAY UTILITY and still needs TERM if it sticks.

Do not confuse DSNUTILB with stand-alone service programs. DSN1COPY copies pages when Db2 may be down; DSN1PRNT prints a page; DSN1LOGP formats log records. Those programs are core, take PARM options of their own, and do not register a UID in SYSUTIL. If you EXEC the wrong program you will not see the job on -DISPLAY UTILITY. Conversely, never run DSN1COPY against a space a LOAD still owns — take DISPLAY DATABASE and DISPLAY UTILITY first.

Authorization is utility-specific (LOAD authority, COPY, REORG, plus database and system auths). MLS tables add extra rules. Partition numbers in statements and messages are physical partition numbers unless a utility says otherwise.

Explain It Like I'm Five

SQL is handing books onto a shelf one at a time. A utility is a moving crew with a truck (DSNUTILB). SYSIN is the work order: “load this box,” “photograph the shelf,” “rebuild the shelf.” The UID is the work-order number on the clipboard so the boss (-DISPLAY UTILITY) can ask how far they got. Core tools are the crew that comes with the building; the Suite is extra crew you pay for to move customer furniture.

Exercises

1. Write a minimum DSNUTILB JCL skeleton with PARM, STEPLIB, SYSPRINT, UTPRINT, and SYSIN OPTIONS PREVIEW.
2. DISPLAY UTILITY(*) on a sandbox and match UID, PHASE, and JOBNAME to the JCL that submitted it.
3. List three Suite utilities your shop uses and two core utilities. Check whether catalog COPY still runs if Suite is missing.
4. Explain when you would RESTART(PHASE) versus TERM and resubmit.
5. Convert a COPY job with three SYSCOPY DDs into TEMPLATE plus LISTDEF (on paper).