CHECK DATA

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

CHECK DATCHECK_SETCHECKPA does not check LOB table spaces. The utility does not check informational referential constraints.

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

Start of change Run CHECK DATA to check the integrity of XML documents and their related node ID indexes. End of change

Start of change Run CHECK DATA to verify data consistency in hash access tables. End of change

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

Output

CHECK DATA SHRLEVEL REFERENCE optionally copies rows and optionally deletes those rows that violate referential or table check constraints. CHECK DATA SHRLEVEL REFERENCE copies each row that violates one or more constraints to an exception table. If a row violates two or more constraints, CHECK DATA SHRLEVEL REFERENCE copies the row only once. For SHRLEVEL CHANGE, CHECK DATA generates REPAIR statements that you can run to delete the rows.

Start of change If the utility finds any violation of constraints, the table space that is checked is not put into the CHECK-pending status. You can force the prior behavior, that a table space is put into CHECK-pending status when violations or constraints are detected, by specifying CHECK_SETCHKP=Y on the CHECK_SETCHKP system parameter. End of change

CHECK DATA SHRLEVEL REFERENCE resets CHECK-pending status if it finds no errors or if all rows that contain violations were copied to exception tables and deleted.

CHECK DATA SHRLEVEL CHANGE operates on shadow copies of the table space and generates the corresponding REPAIR statements.

Authorization required

To execute this utility, you must use a privilege set that includes one of the following authorities:

STATS privilege for the database
DBADM, DBCTRL, or DBMAINT authority for the database. If the object on which the utility operates is in an implicitly created database, DBADM authority on the implicitly created database or DSNDB04 is required.
DATAACCESS authority
SYSCTRL or SYSADM authority

An ID with installation SYSOPR authority can also execute CHECK DATA. However, you cannot use SYSOPR authority to execute CHECK DATA on table space SYSDBASE in database DSNDB06 or on any object except SYSUTILX in database DSNDB01.

If you are using SHRLEVEL CHANGE, the user ID that invokes COPY with the CONCURRENT option must provide the necessary authority to execute the DFSMSdss COPY command. DFSMSdss will create a shadow data set with the authority of the utility batch address space. The submitter should have an RACF® ALTER authority, or its equivalent, for the shadow data set.

If you specify the DELETE option, the privilege set must include the DELETE privilege on the tables that are being checked. If you specify the FOR EXCEPTION option, the privilege set must include the INSERT privilege on any exception table that is used. Start of change If you specify the AUXERROR INVALIDATE or the XMLERROR INVALIDATE option, the privilege set must include the UPDATE privilege on the base tables that contain LOB columns. End of change

Execution phases of CHECK DATA

Phase: Description
UTILINIT: Performs initialization
CHECKXML: Performs XML structure checking for all XML table spaces specified by INCLUDE XML TABLESPACES.
SCANTAB: Extracts foreign keys; uses an index if the index contains the same columns or a superset of the columns in the foreign key; otherwise scans the table
SORT: Sorts foreign keys if they are not extracted from the foreign key index
CHECKDAT: Looks in primary indexes for foreign key parents, checks XML schema validations, checks XML structure, and issues messages to report detected errors
REPORTCK: Copies error rows into exception tables, and delete them from source table if DELETE YES is specified
UTILTERM: Performs cleanup

Syntax and options of the CHECK DATA control statement
The CHECK DATA utility control statement, with its multiple options, defines the function that the utility job performs.
Before running CHECK DATA
Certain activities might be required before you run the CHECK DATA utility, depending on your situation.
Exception tables for the CHECK DATA utility
An exception table is a user-created table that duplicates the definition of a dependent table. The CHECK DATA utility checks the number of columns in the dependent table. The CHECK DATA utility also copies the deleted rows from the dependent table to the exception table.
Exception processing for tables with auxiliary columns
CHECK DATA writes constraint violations to exception tables. The exception table for the base table must have a similar auxiliary column and an auxiliary table space for each auxiliary column.
Specifying the scope of CHECK DATA
Running CHECK DATA with SCOPE PENDING is normally sufficient. DB2 records which data rows must be checked to ensure the referential integrity of the table space.
How violations are identified
CHECK DATA issues a message for every row that contains a referential constraint violation or table check constraint violation.
Detection and correction of constraint violations
You can avoid problems by running CHECK DATA with DELETE NO to detect violations before you attempt to correct the errors.
CHECK DATA XML error detection
Run CHECK DATA with the INCLUDE XML TABLESPACES option to verify the consistency of the XML table space and the node ID index.
Correcting XML data after running CHECK DATA
After you run the CHECK DATA utility, you might need to correct XML data.
Resetting CHECK-pending status
If a table space has a status of CHECK-pending, you can remove the CHECK-pending status by correcting the error and running a utility job. You can either rerun the CHECK DATA utility with SHRLEVEL REFERENCE specified or you can run the REPAIR utility.
LOB column errors
If you run CHECK DATA on a base table space that contains at least one LOB column, you might receive an error on the LOB column.
Resetting auxiliary CHECK-pending status
A table space with LOB or XML columns can be recovered to a point in time. In this case, RECOVER TABLESPACE sets the auxiliary CHECK-pending (ACHKP) status on the table space. You can remove the auxiliary CHECK-pending status if DB2 does not find any inconsistencies.
Termination and restart of CHECK DATA
You can terminate and restart the CHECK DATA utility.
Sample CHECK DATA control statements
Use sample control statements as models for developing your own CHECK DATA control statements.

Parent topic: DB2 online utilities