DB2 Utility Troubleshooting

Start with evidence, not a remedy

The job's condition code is the headline, not the root cause. RC 0 normally means successful completion, while a nonzero return code can indicate warnings, rejected records, an incomplete function, or failure. The exact meaning depends on the utility and messages. Do not assume that RC 4 is harmless or that RC 8 always has one standard remedy. Read SYSPRINT from the beginning of utility initialization through the final message because an early allocation warning may explain a later failure.

DSNU messages are the primary utility evidence. Record the complete message IDs, substitutions, reason codes, and companion messages. A message saying that a phase ended is different from one identifying the original failing data set. Also retain the JES job log, system messages, sort output, allocation messages, and any IDCAMS, tape, SMS, or I/O diagnostics. Use IBM documentation for the installed Db2 release and function level; searching only a shortened message can lead to instructions for a different product or release.

• Record subsystem, member, job name, step name, utility ID, and execution time.
• Save the complete SYSPRINT, not only the last screen or final return code.
• Identify the database, table space, index, partition, and related objects.
• Capture the last completed phase and whether Db2 says restart is possible.
• Preserve data-set names, volumes, sort messages, and storage-management errors.
• Display object states before anyone forces access, repairs a flag, or reruns work.

Display the utility and the protected objects

A batch step can end while the Db2 utility remains registered. Conversely, an operator can describe a long-running utility as failed even though it is actively working. DISPLAY UTILITY shows the utility ID, type, status, phase, and restart information available to Db2. DISPLAY DATABASE shows whether table spaces, indexes, or partitions are in utility-controlled or pending states. Capture both views at the same time so the evidence can be correlated.

1
2
3
4
5
6
7
8
9
-- Db2 command examples; narrow the object names for production use
-DISPLAY UTILITY(*)
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS)
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) RESTRICT
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) ADVISORY
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) LOCKS
 
-- Save the complete output with its timestamp.
-- Repeat DISPLAY UTILITY later to determine whether phases or counts move.

Scope matters. One failed partition does not automatically mean every partition needs recovery, and a healthy table space does not prove that all indexes are usable. Check associated indexes, LOB or XML auxiliary objects, referentially related table spaces, and every partition named by the messages. Pending states are safety controls. COPY status requires a recovery base, RECP indicates recovery is required, CHKP requires integrity validation, REORP requires reorganization, and RBDP or related index states require rebuild or recovery. The status suggests a direction; the failure evidence determines the exact scope and sequence.

Troubleshooting a failed LOAD

LOAD failures commonly begin with input rather than Db2 itself. Compare the LOAD field specification with the actual records: positions, lengths, delimiters, null indicators, encodings, date formats, numeric representation, and input data-set attributes. Read messages for conversion errors and count loaded, discarded, and rejected records. A duplicate key can stop or restrict index processing, while constraint choices can leave data needing CHECK DATA. An allocation or sort failure can occur after many rows have already been processed, so elapsed time does not identify the phase.

Determine whether the operation used REPLACE or RESUME and which logging, copy, index, and enforcement options were selected. Those choices affect recoverability and the states left after interruption. On Db2 for z/OS, do not borrow a “LOAD TERMINATE” remedy from Db2 LUW. Inspect DISPLAY UTILITY and DISPLAY DATABASE. If the z/OS LOAD is restartable, correct the input, storage, or system cause and restart it using your approved procedure and the same utility identity. If it must be terminated, plan for possible RECOVER-pending, COPY-pending, CHECK-pending, or rebuild-pending states and use the appropriate corrective utility afterward.

Troubleshooting a failed REORG

REORG has several resource-intensive phases, so “REORG failed” is too broad. During unload and reload processing, investigate source access, temporary data sets, sort capacity, and output allocation. For online SHRLEVEL CHANGE processing, review the mapping table or shadow data sets and the log-apply activity. Near the end, SWITCH can require a drain that briefly excludes conflicting application work. The last successful phase tells you whether the main problem is storage, sorting, log processing, or concurrency.

Never delete REORG work data sets merely because the job ended. They can be required for restart. Preserve the utility ID and output, correct the proven shortage or external failure, then use the restart instructions reported for that utility. If the failure occurred around SWITCH, inspect the active and shadow data sets and object states before taking action. A blind fresh REORG can conflict with the retained utility or destroy useful diagnostic and restart material.

Troubleshooting failed COPY and RECOVER

COPY failures

For COPY, begin with the output image-copy data set. Check SMS class selection, space, volume or tape availability, cataloging, GDG behavior, retention, and I/O messages. Confirm that the named object and partition were eligible and that the copy was recorded in Db2's recovery history. A created data set is not proof of a usable image copy if the utility failed before recording or completing it. Likewise, a successful copy of one partition does not clear a condition on another partition or on the full table space. Verify the return code, DSNU completion messages, catalog record, and final object state.

RECOVER failures

RECOVER requires a trustworthy recovery chain. Save the messages showing which full and incremental image copies Db2 selected, which active or archive logs it requested, and the intended recovery point. Missing or damaged copies, uncataloged data sets, unavailable archive logs, tape recalls, and storage errors are common causes. A recovery can appear inactive while an archive log is being recalled; confirm the request before declaring the utility stuck.

Point-in-time recovery adds a consistency question. Related table spaces, indexes, and referential sets may need a coordinated point. Recovering only one object can lead to CHECK-pending or rebuild-pending because Db2 cannot prove that related data represents the same moment. Reconstruct the plan from verified copies and log ranges, document the chosen point, and include dependent objects. After RECOVER, inspect pending states and run the required CHECK DATA, CHECK INDEX, or REBUILD INDEX work rather than assuming that one RC 0 repaired the entire application.

Troubleshooting failed RUNSTATS

RUNSTATS does not normally repair table data, but failure can leave the optimizer using old, incomplete, or intentionally preserved statistics. Read which table, column group, index, or partition failed and whether catalog updates occurred. Check object availability, authorization, utility syntax, profile contents, sampling options, distribution-statistics requests, and resource messages. If a stored statistics profile was used, inspect the profile rather than assuming the submitted control statement contains every effective option.

Correct the specific option or access problem and collect the intended statistics again. Then confirm completion and, where appropriate, query the catalog timestamps and cardinalities. Do not compensate for failed RUNSTATS by rebinding every package immediately. First establish whether statistics actually changed and assess access-path risk under normal change control.

Troubleshooting CHECK DATA and CHECK INDEX

CHECK DATA failures and violations

CHECK DATA can fail because the utility could not run, or it can complete its checking work and report real violations. Those are different outcomes. Allocation, authorization, unavailable-object, or utility conflicts require an operational fix. A referential-constraint or check-constraint violation requires a data decision. Preserve the row identifiers, constraint names, exception table output, and counts. Do not delete violating business rows merely to obtain RC 0. The data owner must decide whether rows should be corrected, loaded into exception handling, or removed under an approved rule.

CHECK INDEX failures and inconsistencies

CHECK INDEX compares index structure and keys with the underlying table. Separate a failure to execute from a reported inconsistency. If the utility reports missing, extra, or invalid index entries, preserve page, key, and index identifiers and check for preceding hardware, media, or utility failures. REBUILD INDEX is often the structural remedy because it reconstructs keys from table rows, but the correct scope depends on the affected index and partitions. After rebuilding, run the appropriate validation again and investigate why the mismatch arose; otherwise a storage or process defect can repeat.

Troubleshooting failed REBUILD INDEX

REBUILD INDEX reads table rows, extracts keys, sorts them, and builds the index. Use the phase and messages to narrow the failure. Source read or table-space restrictions affect key extraction. Sort-work shortages and sort product messages affect the sort phase. Index data-set allocation, extent, or I/O problems affect build output. Duplicate-key messages on a unique index can reveal a data-integrity problem rather than an index storage problem.

Check whether the whole index, one partition, or only pending pieces were selected. Preserve restart data and correct the demonstrated cause before restart. If restart is unavailable, plan a new REBUILD with the exact required scope and enough work and target space. Confirm that rebuild-pending states clear, review related indexes, and validate application access. For a unique index, make sure the final result enforces the expected uniqueness instead of treating rejected duplicates as an ordinary warning.

When a utility looks stuck

Long-running and stuck are not synonyms. A large REORG can spend substantial time sorting. RECOVER can wait for archive-log recall. COPY can wait for tape mounting. CHECK utilities can scan enormous objects. Compare DISPLAY UTILITY snapshots over a reasonable interval and look for phase changes, record counts, message activity, CPU, I/O, sort progress, and external requests. Also check whether the batch address space is active and whether automation is waiting for an operator reply.

• A changing phase or count is evidence of progress, even if progress is slow.
• A stable display plus tape recall messages points to an external wait.
• A stable display plus drain messages points to application claims or locks.
• Space-abend or allocation messages indicate failure, not a harmless slow phase.
• No new output is not enough evidence by itself; some phases report infrequently.

Utility drain timeout diagnosis

A drain lets a utility obtain the level of control needed for a phase. An online utility may permit normal work for most of its run and then need a short drain for a switch or consistency point. If application threads retain claims, the utility waits. When the configured retry and timeout behavior is exhausted, it can fail with drain-related DSNU messages. This is usually a workload coordination problem, not proof that the table space is corrupt.

Capture the drain messages and DISPLAY DATABASE LOCKS output, then identify the thread, connection, correlation ID, member, and unit of work that owns the conflicting claim. Long transactions, idle connections with uncommitted work, continuously arriving transactions, and batch programs with infrequent commits are common blockers. Ask the application owner whether the work can commit, end normally, or be paused. Canceling a thread can roll back business work and should follow incident authority. Increasing a timeout without removing continuous claims can only make the utility wait longer.

Restarting a restartable utility

Restart is often safer and faster than beginning again because Db2 has recorded checkpoints and retained the information needed to continue. Confirm through DISPLAY UTILITY and the final messages that restart is supported. Correct the original cause first: add sort space, restore an input data set, make a volume available, resolve a drain plan, or repair the documented external dependency. Preserve required work data sets and use the same utility ID with the site's approved DSNUTILB or utility procedure restart parameters.

A restart request is not a generic retry switch. Some options cannot be changed on restart, and utility phases have different restart behavior. Compare the resubmitted job with the failed job, ensure the target subsystem and utility identity match, and read the restart messages. If Db2 rejects restart, stop and determine whether the identity, retained state, JCL, or phase is wrong instead of repeatedly submitting it.

Terminating a DB2 utility safely

Termination is appropriate when the utility cannot or should not continue and the DBA has assessed the consequences. It is not a shortcut for a drain wait or an unfamiliar message. Before issuing TERM UTILITY, save the evidence, identify the current phase, read the utility-specific termination behavior, confirm the fallback, and notify owners of the affected objects. Use the exact utility ID shown by DISPLAY UTILITY.

1
2
3
4
5
6
7
8
9
10
-- Conceptual operator sequence; use site change and authorization procedures
-DISPLAY UTILITY(*)
 
-- After evidence review and approval:
-TERM UTILITY(utility-id)
 
-- Then verify, do not assume:
-DISPLAY UTILITY(*)
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) RESTRICT
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) ADVISORY

Termination removes or ends the registered utility according to Db2's rules for that phase; it does not guarantee that previous changes are undone. LOAD can leave data or indexes needing recovery, checking, copying, or rebuilding. REORG can leave restart resources or an object requiring follow-up. RECOVER can leave an object unavailable because restoration is incomplete. After termination, repeat DISPLAY UTILITY and DISPLAY DATABASE, inspect every related object, and execute the documented safe remedy.

Pending states and safe remedies

Treat a pending state as Db2 explaining what proof is missing. For COPY-pending, create the required image copy at the correct scope. For RECOVER-pending, restore the damaged table space or partition from valid recovery resources; rebuild or recover an affected index as appropriate. For CHECK-pending, run CHECK DATA and resolve real violations. For REORG-pending, run the eligible REORG. For rebuild-pending, rebuild the affected index or partition. Always verify the result with messages and a fresh display.

REPAIR controls and forced access can change status without creating a copy, restoring pages, validating constraints, or reconstructing keys. They have exceptional, expert-directed uses, but they are not normal utility troubleshooting. Clearing the warning while leaving the cause intact can expose incorrect data and remove the protection that told applications to stay away.

A worked evidence-first example

Suppose an online REORG ends with RC 8. The final screen says “drain failure,” but SYSPRINT shows that unload, sort, reload, and log apply completed before SWITCH. DISPLAY UTILITY retains the REORG at a restartable phase. DISPLAY DATABASE LOCKS identifies a long-running application thread. The safe response is not to delete work data sets or submit a new REORG. Save the output, coordinate a commit or controlled end of the blocker, confirm the application arrival rate is paused if necessary, and restart the retained utility under the approved procedure. Then require successful DSNU completion messages and display the table space and indexes again.

Now change one fact: the output also contains storage errors for a shadow data set. Ending the blocker alone is no longer enough. The DBA must resolve the data-set or volume problem, verify the retained restart resources, and review the documented restart point. Evidence changes the remedy. That is why a single return code or the phrase “REORG failed” cannot safely drive production action.

Explain It Like I'm Five

Imagine a librarian is moving books, repairing shelves, making backup photographs, and checking that every book is in the right place. If the librarian stops, you first ask what job they were doing and where they stopped. Their notes are SYSPRINT and DSNU messages. A “do not enter” sign is a pending state. Restart means giving the librarian the missing box or key so the same job can continue. Termination means ending the job, but some shelves may still be apart. Tearing down the sign does not repair a shelf, replace a lost book, or create a backup photograph.

Exercises

• A LOAD REPLACE fails after input processing. List the evidence needed to decide among restart, termination, RECOVER, CHECK DATA, COPY, and REBUILD INDEX.
• A REORG waits at SWITCH. Draft a checklist for finding the blocking application thread without immediately canceling business work.
• A COPY job creates an output data set but ends RC 8. Explain why the data set alone is not proof of a valid recovery base.
• A RECOVER requests an archive log that is not mounted yet. Describe the observations that distinguish a legitimate recall wait from a failed utility.
• CHECK INDEX reports a key mismatch. Write a plan that preserves evidence, rebuilds at the correct scope, validates the result, and investigates the underlying cause.
• Compare the information supplied by return code, DSNU messages, DISPLAY UTILITY, and DISPLAY DATABASE. Explain why no single source is sufficient.

Frequently asked questions