DB2 utility states, phases, restart, and termination

Two meanings of “utility state”

Start every incident by separating the execution from its targets. The execution is what -DISPLAY UTILITY reports: utility ID, utility name, phase, status, job name, and progress. The target state is what -DISPLAY DATABASE ... RESTRICT reports: for example, a utility restrictive state, COPY-pending, CHECK-pending, RECOVER-pending, REORG-pending, STOP, or an LPL/GRECP condition. They influence one another, but they are not interchangeable.

A utility can be ACTIVE while its target remains available for reads and writes. A utility can be STOPPED while a target remains unavailable. After TERM removes the execution, a restrictive or exception state can remain because terminating the control record does not reverse partially completed physical work. Conversely, an object can be in COPY-pending because an earlier LOAD LOG NO completed normally; no utility needs to be active for that state to exist.

Utility IDs are the restart identity

DSNUTILB receives a utility ID in its EXEC parameter. The ID is not decorative. Db2 uses it to associate the batch step with utility control information in the directory, represented by SYSIBM.SYSUTIL. DISPLAY, restart, and TERM all address that identity. Give every production execution a predictable, unique ID that connects the utility to an application, object set, and schedule, such as PAY.REORG.WEEKLY. Do not depend on a generic value such as UTIL01 across dozens of jobs.

1
2
3
4
5
6
7
8
9
//REORG01  EXEC PGM=DSNUTILB,REGION=0M,
//             PARM='DB2P,PAY.REORG.WEEKLY'
//STEPLIB  DD DISP=SHR,DSN=DB2P.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSIN    DD *
  REORG TABLESPACE PAYDB.PAYTS
        SHRLEVEL CHANGE
/*

If the execution stops, use the same ID only to continue the original work according to its restart procedure. IBM specifically warns against reusing the ID of an unterminated stopped utility for a different utility. Db2 can find the retained information and treat the submission as a restart of the old work. Before changing SYSIN, object lists, templates, or DD allocations, read the restart rules for that utility. A restart is continuation, not an opportunity to silently redefine the job.

Execution phases and what they tell you

Most online utilities have a common frame with UTILINIT at the start, one or more utility-specific phases, and UTILTERM at normal cleanup. The middle is where the useful diagnosis lives. LOAD can report RELOAD, SORT, BUILD, INDEXVAL, ENFORCE, DISCARD, REPORT, SWITCH, or LOGAPPLY depending on its options. RECOVER can report RESTORE, LOGAPPLY, LOGCSR, or LOGUNDO. REORG can move through unload, reload, log processing, drain, and switch work. Exact names and restart points differ by utility and Db2 release.

Phase matters because recovery cost and risk are not uniform. A LOAD failure in RELOAD might restart at an internal commit point. A failure during index BUILD can require index work to repeat. A REORG waiting immediately before SWITCH is usually a claim-and-drain problem, not an I/O failure. A RECOVER deep in LOGAPPLY has already restored a base copy; terminating it can leave the target in RECOVER-pending and force a new recovery decision.

The displayed COUNT belongs to the phase. It can be records, pages, keys, log records, or another utility-defined unit. Do not label it “percent complete” unless the utility’s messages provide a meaningful total. Compare repeated displays and SYSPRINT timestamps. A rising count is evidence of progress; a count of zero is not proof of a hang, especially during initialization, allocation, drain waits, sort setup, or a phase that does not expose a useful counter.

Using DISPLAY UTILITY correctly

DISPLAY UTILITY is the first command for the execution layer. Display all known utilities, then narrow to one ID. In a data sharing group, the response also identifies the member on which work runs, and supported MEMBER filtering can narrow group output. IBM notes that the status is a snapshot taken when utility processing receives the command. By the time the response appears, ACTIVE might have become STOPPED or the utility might have completed.

1
2
3
4
5
6
7
8
9
10
11
-DISPLAY UTILITY(*)
-DISPLAY UTILITY(PAY.REORG.WEEKLY)
 
Typical fields to capture:
  UTILID = PAY.REORG.WEEKLY
  UTILITY = REORG
  PHASE = LOG
  COUNT = phase-specific value
  STATUS = ACTIVE
  JOBNAME = PAYRORG
  TIME STARTED = timestamp

Do not read the command in isolation. Match UTILID and JOBNAME to JES, inspect SYSPRINT for the latest DSNU message and reason code, check UTPRINT for sort failures, and inspect allocation messages for missing or full work data sets. If progress appears stalled, use DISPLAY DATABASE with CLAIMERS or LOCKS, or DISPLAY BLOCKERS, to determine whether an application holds the claim class that the next utility phase must drain.

1
2
3
4
-DISPLAY DATABASE(PAYDB) SPACENAM(PAYTS) RESTRICT
-DISPLAY DATABASE(PAYDB) SPACENAM(PAYTS) CLAIMERS
-DISPLAY DATABASE(PAYDB) SPACENAM(PAYTS) LOCKS
-DISPLAY BLOCKERS

Claims, drains, and utility compatibility

SQL applications take claims while they access a page set. A utility takes the access it needs for its most restrictive planned operation. To obtain exclusive or write-blocking access, it drains one or more claim classes and waits for current claimers to commit or deallocate. A drain prevents new incompatible claimers from entering while Db2 waits for existing claimers to leave. Transaction locks still matter, but utility-to-utility concurrency is also governed by Db2 utility compatibility rules.

That last point prevents a common mistake: two utilities are not automatically compatible just because their current phases both appear to read. Db2 considers the access each utility will need. Allowing both to begin could create a guaranteed collision when one later becomes a writer. A documented exception is that REORG TABLESPACE SHRLEVEL CHANGE and COPY SHRLEVEL CHANGE can coexist for much of their processing, although REORG still needs exclusive control for its switch window.

• If the utility is ACTIVE and COUNT changes, it is working; do not cancel it merely because elapsed time is longer than yesterday.
• If it is ACTIVE at a drain-sensitive phase and COUNT does not change, inspect CLAIMERS and BLOCKERS before changing utility JCL.
• If it is STOPPED, claims held by the executing address space are gone, but retained utility control information and object states still require a restart-or-terminate decision.
• If another utility is rejected, compare target object scope and documented compatibility; changing the utility ID does not make incompatible work compatible.

Online versus offline is not the same as concurrent

A Db2 online utility is an operation such as LOAD, COPY, RECOVER, REORG, RUNSTATS, or CHECK that runs under DSNUTILB while attached to an active Db2 subsystem. It registers a utility ID and can be displayed. “Online” does not mean that every application can keep updating every target for the entire run. Access depends on utility type, options, target granularity, and phase.

An offline operation can mean an online utility deliberately using exclusive access—commonly described operationally as an offline window—or a stand-alone service program such as DSN1COPY or DSN1PRNT that does not run as a DSNUTILB online utility. Stand-alone programs do not register a DSNUTILB utility ID, so DISPLAY UTILITY is not their monitor. Never run a stand-alone page-set tool against an object that an online utility or Db2 member still owns unless the IBM procedure explicitly allows it.

Restarting a stopped utility

Restart is normally the safest path when Db2 retained the execution and the failed operation is still desired. First preserve the original SYSPRINT and job log. Identify the failing DSNU message, phase, object, and reason code. Correct the cause—allocate more space, restore an input data set, correct a catalog or authorization issue, remove a blocker, or repair the environmental problem—without changing the logical purpose of the job.

1
2
3
4
5
6
7
8
9
//REORG01  EXEC PGM=DSNUTILB,REGION=0M,
//             PARM='DB2P,PAY.REORG.WEEKLY,RESTART'
//STEPLIB  DD DISP=SHR,DSN=DB2P.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSIN    DD *
  REORG TABLESPACE PAYDB.PAYTS
        SHRLEVEL CHANGE
/*

RESTART and RESTART(CURRENT) request restart from the most recent commit point that the utility and phase support. RESTART(PHASE) requests the beginning of the last active phase. The apparent choice is not always available: a utility can document phase-only restart for one phase and current-point restart for another. Output data sets can also have utility-specific reset, reuse, or disposition requirements. For example, IBM documents that UNLOAD can preserve an incomplete output data set after interruption and reset output for units it must process again.

1. Capture DISPLAY UTILITY and DISPLAY DATABASE output before changing anything.
2. Read the first meaningful DSNU failure, not only the final return code.
3. Check the utility-specific “termination or restart” documentation for that phase.
4. Correct the environmental cause and verify required input, output, and work data sets.
5. Resubmit with the same utility ID and the documented restart parameter.
6. Monitor the phase, count, JES output, and object status until normal UTILTERM.

TERM UTILITY and retained state

When a utility job ends before normal completion, Db2 can retain its control information so the execution remains restartable. Operators often call this a retained or stopped utility. In data sharing, retained locks after a member failure add another concern: member restart and group recovery can be required to process retained locks safely. Do not assume that submitting the same work on another member bypasses the retained identity or ownership.

Use -TERM UTILITY when the business decision is to abandon the retained execution, when restart is impossible or inappropriate, or when an IBM recovery procedure specifically directs termination. TERM requests termination, releases resources associated with the utility step, and removes the ability to restart that execution. An active request can appear as TERMINATING while cleanup is underway. Redisplay until the utility is no longer known.

1
2
3
4
-DISPLAY UTILITY(PAY.REORG.WEEKLY)
-TERM UTILITY(PAY.REORG.WEEKLY)
-DISPLAY UTILITY(PAY.REORG.WEEKLY)
-DISPLAY DATABASE(PAYDB) SPACENAM(PAYTS) RESTRICT

TERM is not a universal “make normal” command. It does not promise to remove COPY-pending, CHECK-pending, RECOVER-pending, REORG-pending, or every utility restrictive state. It does not complete an unfinished image copy, make an incomplete unload file valid, rebuild a half-built index, or apply the remaining recovery log. After termination, inspect every target and run the documented corrective action. Keep the failed output until the incident is understood; deleting evidence first makes recovery slower.

Common failures and safe recovery

Out-of-space and allocation failures

Work files, sort files, image copies, shadow data sets, and target VSAM can all fill. Read IEC, IDCAMS, DFSORT, and DSNU messages together. Increase or replace the correct data set, verify catalog and SMS conditions, and restart from the supported point. Blindly restarting without more space simply repeats the stop. Blindly terminating can discard a useful checkpoint and extend the outage.

Claim or drain waits

A REORG can process for hours and then wait near SWITCH because a long-running batch thread will not release a claim. Identify the claimer, application owner, unit of recovery, and business impact. Prefer a coordinated application commit or quiesce over cancelling random threads. Tune drain wait and retry behavior in planned JCL; do not discover the policy during the outage.

Input, copy, or log unavailable

LOAD and UNLOAD can depend on sequential input or output; RECOVER depends on image copies and log; COPY can depend on templates, tape, and cataloged data sets. Verify the exact data set and volume from messages. For RECOVER, do not TERM merely because a mount is slow: first establish whether the required recovery asset exists and can be recalled. If an asset is genuinely lost, recovery planning might need a different image copy or recovery point.

Utility or member failure

An S0C4, subsystem failure, or member outage can leave a stopped utility and retained control. In data sharing, collect the member name from DISPLAY and determine whether member restart is part of lock recovery. Do not start an overlapping utility on a surviving member simply to “get around” the stopped one. Preserve dumps and request IBM support when a product defect or damaged control information is possible.

Target remains restricted after cleanup

DISPLAY DATABASE RESTRICT is authoritative for the object layer. COPY-pending commonly calls for a valid image copy; RECOVER-pending calls for successful recovery; CHECK-pending calls for integrity checking or the documented repair path; REORG-pending calls for the required REORG that materializes pending definition changes. Choose the action from the exact state and original utility options, not from the fact that TERM returned a normal command-completion message.

Operational best practices

• Use stable, meaningful utility IDs and prevent two scheduled jobs from accidentally sharing one.
• Record utility ID, job name, subsystem or member, object list, SHRLEVEL, and change ticket in the runbook.
• Baseline phase elapsed times and COUNT movement so “slow” has an evidence-based meaning.
• Retain SYSPRINT, UTPRINT, JES messages, generated LISTDEF expansion, and utility control statements for every production run.
• Monitor DISPLAY UTILITY together with DISPLAY DATABASE RESTRICT and claim/drain information.
• Test RESTART and RESTART(PHASE) procedures in a nonproduction subsystem; a restart runbook that has never been exercised is only a guess.
• Define who may authorize TERM. The operator who notices STOPPED should not have to invent the data-recovery decision alone.
• After normal completion, verify the intended artifact or state: row counts, image copy catalog entry, cleared exception state, index availability, or recovery point.

Automation should alert on STOPPED and unusually long ACTIVE phases, but it should not automatically TERM every stopped utility. Safe automation gathers context: recent DSNU messages, current object restrictions, phase, member, blockers, and prior baseline. Humans or a narrowly defined recovery policy then choose restart, phase-restart, termination, or escalation.

Explain It Like I'm Five

Imagine a moving crew rebuilding a classroom. The utility ID is the work-order number. The phase says whether the crew is packing books, moving shelves, or cleaning up. DISPLAY UTILITY is calling the foreman to ask what the crew is doing. If the truck breaks, the work order says STOPPED but stays on the clipboard so the crew can return to the last safe checkpoint. RESTART sends them back with the same work order. TERM tears up the work order. Tearing it up does not magically put every shelf back, so you still inspect the classroom. Claims are children using desks; a drain asks them to finish and leave before the crew moves a desk. “Online” means the school is open, not that every child can use every desk while it is being moved.

Exercises

1. Write DISPLAY commands to find all utilities, inspect utility ID PAY.REORG.WEEKLY, and check PAYDB.PAYTS for restrictive states and claimers.
2. A REORG SHRLEVEL CHANGE is ACTIVE in LOG with a rising COUNT. Explain why terminating it because it has run for two hours is not evidence-based.
3. A LOAD is STOPPED in BUILD after a sort-work allocation failure. List the evidence you preserve, the resource you correct, and the utility-specific restart question you answer before resubmission.
4. Compare RESTART(CURRENT), RESTART(PHASE), and TERM UTILITY in terms of repeated work, retained identity, and ability to continue the original execution.
5. After TERM, DISPLAY DATABASE reports COPY-pending. Explain why TERM did not fail and why a separate corrective operation is still required.
6. Design a utility ID naming standard that remains unique across application, environment, object group, and schedule without relying on random names.