Common Production Issues

In production, DB2 problems show up as SQLCODEs in job output, as table spaces stuck in strange statuses, or as sudden slowdowns. This page collects the issues you will meet most often and the first action to take for each.

SQLCODEs you will see in production

• -904 means the object is unavailable - often a table space in COPY pending, REORG pending or CHECK pending status, or a utility holding it.
• -911 means deadlock or timeout - your transaction lost a lock fight; retry the unit of work.
• -913 is also a deadlock or timeout, reported at a different point in the program.
• -805 means the package was not found - the program was not bound, or was bound to the wrong collection.
• -818 means the load module and the plan timestamp do not match - recompile and rebind.
• -803 means a duplicate key - the insert violates a unique index.
• -530, -531 and -532 mean referential integrity violations - parent row missing or child rows exist.
• -551 means missing authorization - the ID lacks the needed privilege on the object.
• -204 means the object is undefined - wrong table name, wrong qualifier, or wrong subsystem.
• -206 means a column name is not valid in the context - check spelling and table correlation names.

Table space status problems

• COPY pending follows a LOAD with LOG NO - cleared only by a full COPY.
• REORG pending follows certain ALTERs and mass operations - cleared only by REORG.
• CHECK pending follows LOAD with ENFORCE NO or point-in-time recovery - cleared by CHECK DATA.
• RECOVER pending follows point-in-time recovery - cleared by the documented follow-up recovery steps.
• GRECP (group buffer pool recover pending) appears in data sharing after failures - needs RECOVER.
• Any pending status blocks normal SQL with -904 until the matching utility clears it.

Performance issues in production

• Stale RUNSTATS is the number one cause of sudden query slowdowns - refresh statistics and rebind.
• Lock contention shows up as -911/-913 - check for long-running updaters holding locks.
• A missing or wrong index turns a quick query into a table space scan - check the access path with EXPLAIN.
• Utility windows overlapping with online hours slow both - keep utilities in their scheduled windows.

Example of checking running utilities when production has a problem:-

Issue this DB2 command from the console, SDSF or DB2I. It lists every utility running on the subsystem with its phase, so you can see which utility is holding the table space your job needs.