Recovering from referential constraint violation

When a referential constraint is violated, the table space is available for some actions, but you cannot run certain utilities or use SQL to update the data in the table space until you recover from this situation.

Symptoms

One of the following messages is issued at the end of utility processing, depending on whether the table space is partitioned:

DSNU561I csect-name - TABLESPACE=tablespace-name PARTITION=partnum
      IS IN CHECK PENDING
DSNU563I csect-name - TABLESPACE=tablespace-name IS IN CHECK PENDING

Causes

Db2 detected one or more referential constraint violations.

Environment

The table space is still generally available. However, it is not available to the COPY, REORG, and QUIESCE utilities, or to SQL select, insert, delete, or update operations that involve tables in the table space.

Resolving the problem

Operator response:
  1. Use the START DATABASE ACCESS (UT) command to start the table space for utility-only access.
  2. Run the CHECK DATA utility on the table space. Consider these recommendations:
    • If you do not believe that violations exist, specify DELETE NO. If violations do not exist, specifying DELETE NO resets the CHECK-pending status; however, if violations do exist, the status is not reset.
    • If you believe that violations exist, specify the DELETE YES option and an appropriate exception table. Specifying DELETE YES results in deletion of all rows that are in violation, copies them to an exception table, and resets the CHECK-pending status.
    • If the CHECK-pending status was set during execution of the LOAD utility, specify the SCOPE PENDING option. This checks only those rows that are added to the table space by LOAD, rather than every row in the table space.
  3. Correct the rows in the exception table, if necessary, and use the SQL INSERT statement to insert them into the original table.
  4. Issue the command START DATABASE to start the table space for RO or RW access, whichever is appropriate. The table space is no longer in CHECK-pending status and is available for use. If you use the ACCESS (FORCE) option of this command, the CHECK-pending status is reset. However, using ACCESS (FORCE) is not recommended because it does not correct the underlying violations of referential constraints.