DB2 CHECK PENDING status and recovery

The LOAD utility places a tablespace in DB2 CHECK PENDING (CHKP) status if its referential integrity is in doubt or its check constraints are violated. DB2 enforces constraints when:

• A row is inserted into the table.
• A row in the table is updated.
• Data is added to the table using the LOAD utility with the ENFORCE CONSTRAINTS option.

Causes of DB2 CHECK PENDING status

• When you use ALTER TABLE to add a check constraint to already populated tables, and CURRENT RULES special register is DB2, the check constraint is added to the table description but its enforcement is deferred. Because there might be rows in the table that violate the check constraint, the table is placed in CHECK-pending status.
• When a table is LOADed with ENFORCE NO option, then the table is left in CHECK PENDING status as DB2 bypasses referential integrity and check constraints
• The LOAD utility is run with CONSTRAINTS NO, and check constraints are defined on the table.
• The check pending status will appear when Referential Integrity(RI) violation between data in parent table with child table. During the program access, when RI violation encounters the database it immediately put in CHKP status.
• An index might be placed in CHECK-pending status if you recovered an index to a specific RBA or LRSN from a copy and applied the log records, but you did not recover the table space in the same list. The CHECK-pending status can also be set on an index if you specified the table space and the index, but the recovery point in time was not a point of consistency (QUIESCE or COPY SHRLEVEL REFERENCE).
• A point-in-time RECOVER introduces violations of check constraints.
• CHECK DATA is run on a table that contains violations of check constraints.

Resolve DB2 CHECK PENDING status

• Use the REPAIR utility with the SET STATEMENT and NOCHECKPEND option.

//STEP01 EXEC DSNUPROC
  REPAIR SET TABLESPACE dbname.tsname NOCHECKPEND

• Run the CHECK DATA utility for those tablespace and the tablespace status changes from CHKP to RW. CHECK DATA utility locates invalid data and, optionally, removes it. If CHECK DATA removes the invalid data, the remaining data satisfies all check and referential constraints and therefore, the CHECK-pending restriction is removed.

• Although CHECK DATA is usually the preferred method for resetting CHECK-pending status, you can also reset this status by using any of the following operations:
  1. Drop tables that contain invalid rows.
  2. Replace the data in the table space by using LOAD REPLACE and enforcing check and referential constraints.
  3. Recover all members of the table space that were set to a prior quiesce point.
  4. Use the REPAIR utility with the SET STATEMENT and NOCHECKPEND option.

• Run the CHECK INDEX utility on the index. If any errors are found, use the REBUILD INDEX utility to rebuild the index from existing data
• You can execute the following query (This command will restart the tablespace and reset all the pending states on the tablespace and the table will be back in RW (read write mode).
  -START DATABASE(dbname) SPACE(tablespace-name) ACESS(FORCE)

Note: The Status RW (READ/WRITE) means that the database is ready to do database processing.

//STEPO1 EXEC PGM=DSNUTILB,REGIONM, 
// PARM='DB2X,MIGRDAN, '
//STEPLIB DD DSN=XXX.XXXXX.SDSNLOAD,DISP=SHR
//SYSIN DD *    
REPAIR SET TABLESPACE XXXXX.XXXXX NOCOPYPEND 
REPAIR SET TABLESPACE XXXXX.XXXXX NORCVRPEND
REPAIR SET INDEX XXXXX.XXXXX NOCOPYPEND
REPAIR SET INDEX XXXXX.XXXXX NORCVRPEND
REPAIR SET INDEX XXXXX.XXXXX NORBDPEND
/*
//SYSPRINT DD SYSOUT=* 
//UTPRINT DD SYSOUT=*

CHECK DATA Utility

The CHECK DATA utility checks tablespaces for violations of referential and table check constraints and reports information about violations that it detects. CHECK DATA checks for consistency between a base table space and the corresponding LOB or XML tablespaces. CHECK DATA does not check LOB tablespaces. The utility does not check informational referential constraints.

Run CHECK DATA after a conditional restart or a point-in-time recovery on all tablespaces where parent and dependent tables might not be synchronized or where base tables and auxiliary tables might not be synchronized.

Restriction: Do not run CHECK DATA on encrypted data. Because CHECK DATA does not decrypt the data, the utility might produce unpredictable results.

CHECK INDEX Utility

The CHECK INDEX online utility tests whether indexes are consistent with the data that they index, and issues warning messages when it finds an inconsistency.

Run the CHECK INDEX utility after a conditional restart or a point-in-time recovery on all tablespaces whose indexes might not be consistent with the data. Also run CHECK INDEX before running CHECK DATA, especially if you specify DELETE YES. Running CHECK INDEX before CHECK DATA ensures that the indexes that CHECK DATA uses are valid. When checking an auxiliary table index, CHECK INDEX verifies that each LOB is represented by an index entry and that an index entry exists for every LOB.

Important: Inaccurate statistics for tables, tablespaces, or indexes can result in a sort failure during CHECK INDEX.

Read DB2 blogs: Click Here IBM DB2 Manual: Click Here