DB2 Troubleshooting Object and Pending States

Start with evidence, not a state-clearing command

The first troubleshooting question is not “How do I remove this code?” It is “Why did Db2 protect this object?” A pending state usually follows a utility, structural ALTER, recovery event, data-sharing failure, or storage problem. If an operator removes the indicator before understanding the cause, the object can appear available while its rows, pages, constraints, physical format, or recovery chain remain unsafe.

Capture the failing SQLCODE, reason code, resource name, subsystem, member, timestamp, database, table space, partition, and index. SQLCODE -904 commonly reports an unavailable resource, but its reason and resource fields are more useful than the SQLCODE alone. Preserve messages from the MSTR address space and the complete output from any utility that recently touched the object. This evidence establishes the sequence of events before a restart or new utility changes it.

Diagnose with -DISPLAY DATABASE

The Db2 command -DISPLAY DATABASE is the central diagnostic tool. Start narrowly with the database and space named in the application or utility message. A specific display is easier to read and less expensive than immediately listing every object in the subsystem. Inspect every returned row because different partitions and associated indexes can have different statuses.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Examples: substitute the real database and space names
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS)
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) RESTRICT
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) ADVISORY
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) LOCKS
-DISPLAY UTILITY(*)
 
-- Broader discovery can produce substantial output
-DISPLAY DATABASE(*) SPACENAM(*) RESTRICT
-DISPLAY DATABASE(*) SPACENAM(*) ADVISORY
 
-- Save the full command response, message IDs, utility ID,
-- utility phase, return code, member, and timestamp.

The RESTRICT option helps identify restrictive states such as REORP, COPY, CHKP, RECP, LPL, and rebuild-pending conditions. The ADVISORY view helps find advisory conditions such as AREO* and AREOR. LOCKS can explain why a corrective utility cannot obtain the claim or drain it needs, but an object pending state is not itself an ordinary lock wait. -DISPLAY UTILITY answers a separate question: whether an active or retained utility controls the object and whether it can be restarted.

REORG-pending and pending DDL

Restrictive REORP

REORP means restrictive REORG-pending. Db2 requires physical reorganization before normal access can continue. This state can follow a structural change or another operation that leaves the current page format incompatible with the required definition. The normal remedy is REORG TABLESPACE at the affected table-space or partition scope. Do not assume that RUNSTATS, COPY, or START DATABASE will materialize the physical change; those actions solve different problems.

AREO* and AREOR

AREO* is advisory REORG-pending. Applications can normally continue, but the table space contains physical organization or row versions that should be normalized by REORG. AREOR is associated with pending definition changes. A requested ALTER can be recorded while its full physical effect waits for an eligible REORG. That REORG does more than improve clustering: it can materialize the pending DDL and establish the new definition.

Choose REORG options from the IBM documentation for the installed release and function level. SCOPE PENDING can select eligible pending partitions, while SHRLEVEL and object scope determine whether a particular pending definition can be materialized. Plan claims, drains, SWITCH processing, package effects, work data sets, image copies, and fallback. After completion, verify both the status and the intended catalog definition.

1
2
3
4
5
6
7
8
9
-- Conceptual utility control statement
REORG TABLESPACE APPDB.ORDERTS
  SCOPE PENDING
  SHRLEVEL CHANGE
 
-- Do not copy options blindly. Some pending changes require
-- different scope or SHRLEVEL rules at a given Db2 level.
-- Confirm that the REORG both completed successfully and
-- materialized the intended pending definition.

COPY-pending protects recoverability

COPY status means restrictive COPY-pending. Db2 is saying that the affected table space or partition needs an acceptable image-copy recovery base, commonly after an operation changed data without providing the expected recoverability. Access restrictions vary with the exact condition, so do not interpret a successful SELECT as proof that the object is healthy or updateable.

The normal corrective action is the COPY utility for the correct physical scope. A full table-space copy and a partition copy are not interchangeable. For a partition-level state, copy the affected partition as required. For multi-piece data sets, copying an incomplete piece does not prove that the whole page set has a usable recovery base. Review utility messages and the copy catalog, then repeat -DISPLAY DATABASE. Clearing COPY with a low-level command would remove the warning but would not create the missing image copy.

CHECK-pending requires proof of integrity

CHKP means CHECK-pending. Db2 cannot currently guarantee that relevant referential constraints or table check constraints are satisfied. CHKP can follow certain LOAD processing or a point-in-time recovery that brings related table spaces back to inconsistent moments. The concern is logical integrity: parent and child rows, or rows and their check conditions, might no longer agree.

CHECK DATA is the normal remedy. It examines the applicable relationships and constraints and can reset CHKP when the data is valid. If violations exist, utility options and site procedures determine whether rows are reported, copied to exception tables, corrected, or deleted. That is a business-data decision, not merely a technical cleanup. Preserve exception information and obtain the data owner's approval before removing rows. Include LOB, auxiliary, XML, and dependent objects where the relationship demands it.

RECOVER-pending means the object is broken

RECP is restrictive RECOVER-pending. It indicates that Db2 considers a table space, partition, index space, or eligible auxiliary index broken. For a table space or partition, the expected remedy is RECOVER using valid image copies and log records. For an index, RECOVER INDEX or REBUILD INDEX can be appropriate depending on available copies, logging, object type, and whether rebuilding from the table data is safer.

Recovery scope is crucial. Before running RECOVER, verify the selected copy, required log range, target recovery point, storage condition, and related objects. A point-in-time recovery of one table space can leave referentially related table spaces at different moments and cause CHKP. Indexes recovered to a different point from their data can need rebuilding. After RECOVER, check the utility return code, display all related objects, validate indexes and constraints, and test representative SQL.

Troubleshooting a stopped page set

A stopped page set is different from a pending state. STOP can be an intentional operational state established by STOP DATABASE, or it can remain after a controlled maintenance sequence. Determine who stopped it, why, which access mode was intended, whether a utility still owns it, and whether storage is available. Starting an intentionally isolated object can interfere with recovery, maintenance, or incident containment.

Once the cause is resolved and the object is safe, an authorized START DATABASE command with the required access option returns it to service. Do not confuse a start command with repair: START does not create an image copy, recover broken pages, validate constraints, or materialize pending DDL. If RECP, COPY, CHKP, or REORP remains, perform that state's corrective utility instead of repeatedly stopping and starting the object.

GRECP in a DB2 data sharing group

GRECP means group buffer pool RECOVER-pending. It applies in Db2 data sharing when cached pages associated with a group buffer pool require group recovery, such as after a loss of group buffer pool structure or connectivity. This is not a local application deadlock. The DBA must identify the affected group buffer pool, members, page sets, and failure messages, then follow the documented data-sharing recovery procedure for that environment.

Db2 can perform group buffer pool recovery when the required structures and members are available. Some documented procedures use START DATABASE with ACCESS(FORCE) to initiate recovery processing for affected objects, but ACCESS(FORCE) must not become a generic availability shortcut. Confirm coupling-facility health, group buffer pool status, member connectivity, logs, and the exact IBM procedure first. Verify that GRECP clears across the complete affected scope, not only on the member where the command was issued.

LPL and logically unavailable page ranges

The logical page list, shown as LPL, identifies pages or page ranges that Db2 has made logically unavailable after detecting a condition that prevents safe use. An I/O error, damaged page, or recovery-related event can be involved. The rest of the object might appear available, which makes LPL especially important: broad object status alone can hide a smaller unavailable range that one query happens to touch.

Capture the LPL details and all related media, buffer manager, and storage messages. Correct the storage cause before recovering pages, or the same failure can return. Use the IBM-supported LPL or page-range recovery procedure for the installed Db2 release; the proper action can involve automated recovery processing, an authorized START operation, or RECOVER at an appropriate scope. Never erase LPL evidence merely to make the display clean. Verification includes an empty LPL for the affected ranges, healthy storage paths, successful SQL, and no recurring messages.

A safe troubleshooting workflow

• Record the symptom. Save SQLCODEs, reason codes, resource names, messages, timestamps, members, and job output before changing object state.
• Display exact scope. Inspect the database, table space, every partition, indexes, RESTRICT output, ADVISORY output, and active utilities.
• Find the initiating event. Look for failed utilities, ALTER statements, recoveries, data-sharing events, media errors, and deliberate STOP commands.
• Match state to remedy. Use REORG for REORP or planned advisory work, COPY for COPY, CHECK DATA for CHKP, and RECOVER or index rebuild for RECP.
• Plan dependencies. Include related partitions, indexes, LOBs, referential sets, copies, logs, claims, drains, group members, and storage owners.
• Execute with fallback. Preserve utility output and know what to do if the corrective utility fails during initialization, processing, or switching.
• Prove the result. Repeat displays, confirm return codes, test SQL, inspect dependent objects, and document the root cause and prevention work.

Why blindly clearing states is dangerous

Commands such as REPAIR SET and exceptional force-access controls exist for carefully documented situations, but they are not substitutes for repair. Clearing COPY does not create an image copy. Clearing CHKP does not validate a foreign key. Clearing RECP does not reconstruct a broken page set. Clearing AREOR does not materialize pending DDL. Starting a stopped object does not make its storage healthy. A status is a safety interlock; removing the interlock and fixing the machine are separate actions.

An exceptional reset requires evidence that the indicator is stale or that another approved procedure has already corrected the underlying condition. It also requires expert review, change authorization, a fallback plan, and post-action validation. Under incident pressure, the fastest safe path is usually the utility that addresses the actual cause, not the command that makes the status disappear fastest.

Explain It Like I'm Five

Imagine Db2 is a school with many classrooms. REORP means the desks must be rearranged before class can begin. AREO* means class may continue, but the desks should be rearranged soon. COPY means the teacher needs a reliable photograph of the room so it can be rebuilt later. CHKP means the teacher must check that every child is on the right class list. RECP means something is broken and must be restored. STOP means someone intentionally locked the room. GRECP means several school buildings must agree on their shared supplies again. LPL means a few floor tiles are unsafe. Taking down any warning sign does not fix the desks, photograph, list, supplies, or floor.

Exercises

• A utility ends unsuccessfully and a partition displays COPY. List the evidence you would capture, the utility you would plan, and the checks you would perform afterward.
• Compare REORP, AREO*, and AREOR by availability, physical meaning, pending DDL impact, and the role of REORG.
• A parent table space was recovered to yesterday while its child remained current. Explain why CHKP can result and design a CHECK DATA and exception-handling plan.
• An object shows RECP after an I/O error. Create a recovery checklist covering storage, image copies, logs, recovery point, indexes, constraints, fallback, and validation.
• A page set is STOP with no pending code. Write the questions you must answer before issuing START DATABASE.
• Explain why GRECP and LPL need wider infrastructure evidence than an ordinary SQL lock timeout.

Frequently asked questions