CHECK DATA

The CHECK DATA utility verifies that every row in a table space satisfies its referential constraints and table check constraints. It is the safety net you run after operations that could have introduced bad data, such as a LOAD with ENFORCE NO or a point-in-time recovery.

What is CHECK DATA

• CHECK DATA scans the rows of a table space and tests each one against foreign keys and check constraints.
• Rows that violate a constraint are called violations or exceptions.
• Violations can be deleted from the table or copied to an exception table for later fixing.
• Running CHECK DATA clears CHECK pending status, which otherwise blocks SQL on the table space.
• You can check all tables in the space (SCOPE ALL) or only the pending ones (SCOPE PENDING).

When to run CHECK DATA

• After a LOAD that ran with ENFORCE NO, which skips constraint checking for speed.
• After a point-in-time RECOVER, which can leave related tables out of sync.
• When the table space is in CHECK pending status and applications get -904 errors.
• After any repair or data fix applied directly to the table space pages.

Handling violations

• Create an exception table with the same columns as the checked table plus extra diagnostic columns.
• Use the EXCEPTIONS option so violating rows are copied there instead of being deleted blindly.
• Fix the data in the exception table or the parent table, then reload the corrected rows.
• Run CHECK DATA again until it reports zero violations and the pending status clears.

Example of a CHECK DATA control statement:-