DB2 Object States

State belongs to the physical object and its scope

Db2 protects data through physical objects. A table's rows reside in a table space; access paths reside in index spaces; and partitioned objects have independently manageable physical or logical parts. Consequently, a status is not merely a property of a table name. It can apply to an entire table space, one table space partition, an index space, an index partition, a logical index partition, or even selected page ranges. The affected scope determines both the outage and the corrective utility.

States can also coexist. An object can be started but serialized for utility access, or a table space can be normal while one associated index is rebuild-pending. Read every code in the command output, not only the first familiar abbreviation. Then inspect related table spaces, indexes, partitions, active utilities, messages, and utility history before deciding what failed.

RW and the normal access states

RW means the object is started for read-write activity. For a healthy application object, RW is the expected end state after corrective work. It does not mean that no utility is active elsewhere, that every related index is usable, or that every partition has the same state. RO means the object is started for read-only activity. Other access modes include states intended for replication or persistent read-only operation. The display output is authoritative for the particular object and scope shown.

• A SELECT normally needs the table space and required access structures to permit read access.
• INSERT, UPDATE, DELETE, MERGE, LOAD RESUME, and many maintenance actions need an access state that permits changes.
• The optimizer can sometimes avoid an unavailable index, but it cannot bypass an unavailable table space that contains the rows.
• A partition-level state can limit only one partition, but SQL that needs that partition still fails or is restricted.

SQL failures caused by unavailable resources commonly surface as resource-unavailable diagnostics, including SQLCODE -904, with reason and resource identifiers that point to the actual object. Do not stop at the SQLCODE. Correlate the resource name and reason code with DISPLAY DATABASE, the MSTR log, utility output, and the application timestamp.

Restrictive, utility, and advisory states

A restrictive state prevents or severely limits ordinary use because Db2 cannot currently guarantee the required integrity, structure, or recoverability. REORP, COPY, RECP, CHKP, and index rebuild-pending states belong in this category. IBM's guidance for objects left restricted by LOAD says SQL access is generally unavailable and only limited operations that correct or remove the state are possible. Exact permitted operations vary by status and object type.

A utility access state says an active or retained utility controls the object. UTRO permits read-only access, UTRW permits read-write access under the utility's concurrency model, and UTUTmakes the object unavailable while the utility owns it. A plain UT indication is a utility-controlled state whose details require DISPLAY UTILITY and messages. These are not errors by themselves; an online REORG legitimately moves through utility phases and changes its access requirements.

An advisory or informational state normally allows SQL to continue. Examples include AREO*, AREOR, advisory rebuild-pending, and ICOPY. “Advisory” does not mean “ignore forever.” It means Db2 is preserving availability while telling the DBA that physical maintenance, definition materialization, or a new recovery base should be scheduled.

REORG-pending and advisory REORG states

REORP is restrictive REORG-pending status on a table space or partition. It says a reorganization is required before normal use can continue. Causes include particular structural alterations, recovery to a point before a definition was materialized, or operations that leave the physical organization incompatible with the required definition. The normal corrective direction is REORG TABLESPACE at the scope and SHRLEVEL required by the cause.

AREO* is advisory REORG-pending. It often follows an immediate schema change that creates a new table version, such as adding a column. Existing rows can remain in an older format, and Db2 interprets their version while SQL continues. A later REORG normalizes the physical rows and removes the advisory condition. AREOR is advisory REORG-pending associated with pending definition changes. In that case, the requested definition is recorded but is not fully reflected in the current data and catalog behavior until an eligible REORG materializes it.

Do not collapse AREO*, AREOR, and REORP into one label. Their availability and DDL implications differ. For example, another ALTER can be rejected until an earlier advisory condition is resolved, even though applications can still read and update the object. When REORG materializes pending definition changes, it must coordinate catalog, package, dynamic statement, and data changes. Plan for drains, lock timing, package invalidation, and the SWITCH phase rather than treating it as a simple space cleanup.

1
2
3
4
5
6
7
8
9
-- Conceptual utility control statements; validate site syntax and options
 
REORG TABLESPACE APPDB.ORDERTS
  SCOPE PENDING
  SHRLEVEL CHANGE
 
-- SCOPE PENDING selects eligible REORP or advisory REORG-pending
-- partitions. The correct SHRLEVEL and full-table-space requirements
-- depend on the pending change and installed Db2 level.

COPY-pending and informational COPY-pending

COPY means restrictive COPY-pending on a table space or partition. Db2 needs a valid image-copy recovery base after an operation that changed data without providing the expected recoverability. Reads might remain possible in some COPY-pending situations, but updates are restricted; rely on the documented status behavior rather than assuming the object is fully usable. The COPY utility normally creates the required image copy and resets the state.

Scope matters. A successful full image copy clears the status for the copied table space. Copying one partition clears only the eligible partition-level condition, and copying one piece of a multi-piece linear data set does not necessarily clear COPY-pending. Confirm the utility return code and issue DISPLAY DATABASE again.

ICOPY is informational COPY-pending. It is an advisory status, often relevant to indexes or NOT LOGGED objects, and does not impose the same SQL restriction as COPY. It still records a recoverability requirement. Run the appropriate COPY and verify the status; do not clear ICOPY merely to make a dashboard green.

RECOVER-pending: the object is broken

RECP is RECOVER-pending. IBM defines it as a restrictive status that indicates a table space, table space partition, index space, or index on an auxiliary table is broken and must be recovered. This is stronger than a maintenance recommendation. Application access is restricted because Db2 cannot safely use the object in its present condition.

For a table space or partition, the normal action is RECOVER using valid image copies and log records. For an index, RECOVER INDEX or REBUILD INDEX can be appropriate, depending on what recovery resources exist and whether rebuilding from table data is preferable. A point-in-time recovery also requires consistency planning. Recovering only one member of a referentially connected table space set, or recovering related indexes and data to different points, can create new CHECK-pending or rebuild-pending conditions.

• Identify the exact failing page set, partition, index, or page range.
• Verify usable copies, log ranges, and the intended recovery point.
• Include related objects whose consistency depends on the same point in time.
• Read RECOVER messages and utility history before changing the plan.
• Validate the resulting data, indexes, and application access after recovery.

CHECK-pending: integrity needs proof

CHKP is CHECK-pending on a table space or partition. Db2 can set it when referential or table-check constraint consistency is uncertain—for example, after certain LOAD operations or an inconsistent point-in-time recovery of related table spaces. It is restrictive because Db2 needs evidence that rows satisfy the required relationships and constraints.

CHECK DATA is the normal validation utility. It examines the relevant relationships and constraints, reports violations, and can reset CHECK-pending when the data is valid. Utility options determine how violations are handled, so “run CHECK DATA” is not a complete production plan. Decide whether violating rows will be deleted, copied to an exception table, corrected by an application process, or cause the job to stop. Preserve evidence and obtain the data owner's approval before deleting business data.

LOB and auxiliary relationships have related warning and auxiliary CHECK-pending states. An apparently healthy base object does not prove that its LOB table space, auxiliary index, or XML-related object is healthy. Include dependent objects in diagnosis.

What “LOAD-pending” means on Db2 for z/OS

“Load pending” is a common formal state in Db2 for Linux, UNIX, and Windows, but it is not the primary Db2 for z/OS DISPLAY DATABASE status name. Do not copy a LUW command such as LOAD TERMINATE into a z/OS runbook. On Db2 for z/OS, an interrupted or terminated LOAD can leave an active utility registration and can set combinations of restrictive states according to the phase and options: table spaces can be RECP, CHKP, or COPY, while indexes can be rebuild-pending.

Diagnose the actual z/OS condition. DISPLAY UTILITY shows whether the utility can be restarted; utility output identifies its last phase and return code; DISPLAY DATABASE shows the resulting object states. A restartable LOAD should normally be restarted using the documented utility procedure. Terminating it can preserve or create pending states that then require RECOVER, REBUILD INDEX, CHECK DATA, or COPY. The right sequence comes from the failed phase—not from the informal phrase “load pending.”

DISPLAY DATABASE diagnosis

DISPLAY DATABASE is the central status command. Use a precise database and space name during an incident so the output is readable, then broaden the search to discover related objects. RESTRICT filters pending restrictive states. ADVISORY, used without RESTRICT, lists advisory conditions such as ICOPY, AREO*, and AREOR. LOCKS can help explain why a corrective utility cannot obtain the serialization it needs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Db2 commands: examples only
 
-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(*)
 
-- Estate-wide discovery can be expensive and verbose:
-DISPLAY DATABASE(*) SPACENAM(*) RESTRICT
-DISPLAY DATABASE(*) SPACENAM(*) ADVISORY
 
-- Read every status and partition row. Save the complete output,
-- timestamp, Db2 messages, utility ID, phase, and return code.

The command can filter specific categories such as CHKP, COPY, RECP, REORP, RBDP, LPL, and utility access modes. In data sharing, also consider GRECP, which indicates group buffer pool RECOVER-pending, and LPL, where listed pages or page ranges are logically unavailable because Db2 detected an error. Those are recovery incidents, not ordinary application locks.

A safe resolution workflow

1. Freeze assumptions. Record the application error, time, subsystem, database, space, partition, member, and resource identifiers before anyone clears or restarts anything.
2. Display the state. Capture DISPLAY DATABASE for the affected object, its partitions, and related indexes. Run both restrictive and advisory views when the initial output is incomplete.
3. Find the cause. Review DISPLAY UTILITY, job output, Db2 messages, utility history, recent ALTER statements, recovery activity, and storage errors.
4. Determine scope. Decide whether the condition affects one partition, the whole table space, an index, a referential set, a LOB relationship, or page ranges.
5. Select the corrective utility. Use REORG for REORP or planned advisory REORG work, COPY for COPY/ICOPY, RECOVER or REBUILD for broken objects, and CHECK DATA for integrity validation.
6. Plan concurrency and fallback. Check claims, drains, retained locks, image copies, logs, work data sets, outage windows, package effects, and what happens if the utility fails in each phase.
7. Execute under change control. Preserve SYSPRINT and messages. Do not improvise REPAIR SET or ACCESS(FORCE) simply because an application is waiting.
8. Verify the outcome. Require a successful utility return code, repeat DISPLAY DATABASE, validate SQL, inspect dependent indexes, and document the root cause and prevention action.

Why forcing the state off is dangerous

REPAIR SET includes controls that can reset COPY-pending, RECOVER-pending, CHECK-pending, rebuild-pending, and advisory REORG statuses. START DATABASE with ACCESS(FORCE) can also bypass certain restrictive conditions. IBM explicitly warns that these choices do not correct the underlying problem. Removing RECP does not reconstruct damaged pages. Removing CHKP does not prove referential integrity. Removing COPY does not create an image copy. Removing AREOR does not materialize pending DDL.

Such controls have valid exceptional uses—for example, when an expert has independent proof that a stale status is wrong or a documented disaster-recovery procedure requires a controlled bypass. They demand authorization, evidence, a fallback, and post-action validation. The status is a safety interlock; defeating it is not the same as repairing the machine.

Explain It Like I'm Five

Imagine each database object is a room in a library. RW is a green sign: people may read and put books back. A utility sign means a cleaning crew is using the room; UTRO lets visitors look but not move books, while UTUT closes the door. REORP says the shelves must be rebuilt before visitors return. COPY says the librarian needs a safe photograph of every shelf in case something goes wrong. RECP says books or shelves are damaged and must be restored. CHKP says the librarian is not sure every book is in the correct section and must check. Advisory signs let visitors continue, but the librarian still schedules the work. Tearing down a warning sign does not fix the shelves, books, or backup photograph.

Exercises

1. A table space displays RW but one index displays RBDP. Explain which SQL might still work, what performance risk exists, and which utility you would investigate.
2. Compare REORP, AREO*, and AREOR in terms of restrictiveness, pending definitions, SQL availability, and the role of REORG.
3. A partition is in COPY status after a utility. Draft the commands and evidence you would collect before and after a partition-level COPY.
4. A point-in-time recovery leaves related table spaces in CHKP. Describe why CHECK DATA is required and how exception handling could affect business rows.
5. An operator says a table is “load pending.” Write five questions that distinguish an active restartable LOAD from RECP, RBDP, CHKP, and COPY conditions on Db2 for z/OS.
6. Explain why REPAIR SET NORCVRPEND can make an object look available without making its data correct.
7. Build a recovery checklist for one RECP partition that includes copies, logs, related indexes, referential scope, fallback, validation, and final DISPLAY DATABASE output.

IBM documentation used for this tutorial

The definitions and safety guidance above are based on IBM Db2 13 for z/OS documentation. Always select the documentation for your installed release and function level because status details and eligible utility options can evolve.

• IBM -DISPLAY DATABASE command reference for status codes, RESTRICT, ADVISORY, and utility access modes
• IBM Advisory or restrictive states guidance, including the warning about REPAIR SET and ACCESS(FORCE)
• IBM RECOVER-pending status corrective-action table
• IBM REORG TABLESPACE, COPY, CHECK DATA, and LOAD termination references