CHECK DATA

The CHECK DATA online utility checks table spaces for violations of referential and table check constraints. This utility also checks for consistency between a base table space and the corresponding LOB or XML table spaces.

CHECK DATA also verifies data consistency in hash access tables and checks the integrity of XML documents and their related node ID indexes. CHECK DATA does not check LOB table spaces or informational referential constraints.

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

Restriction: Start of change

Do not run CHECK DATA on data that is encrypted through built-in functions. Because CHECK DATA does not decrypt that data, the utility might produce unpredictable results. End of change

Output

CHECK DATA SHRLEVEL REFERENCE optionally copies and deletes rows that violate referential or table check constraints. The utility copies each row that violates one or more constraints to an exception table. If a row violates two or more constraints, the utility copies the row only once. CHECK DATA SHRLEVEL REFERENCE resets any CHECK-pending (CHKP), auxiliary CHECK-pending (ACHKP), and auxiliary warning (AUXW) restrictive states if the utility finds no errors or if all rows that contain violations were copied to exception tables and deleted.

Start of change CHECK DATA SHRLEVEL CHANGE operates on shadow copies of the table space and generates REPAIR statements to correct any violations. CHECK DATA SHRLEVEL CHANGE resets any existing CHKP, ACHKP, and AUXW states if it finds no errors. End of change

CHECK DATA does not set CHECK-pending status on a table space, even if it finds violations of constraints in the table space.

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 execute CHECK DATA on only SYSUTILX in database DSNDB01.

If you specify SHRLEVEL CHANGE, the user ID that invokes COPY with the CONCURRENT option must provide the necessary authority to execute the DFSMSdss COPY command. DFSMSdss creates 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. If you specify AUXERROR INVALIDATE, LOBERROR INVALIDATE, or XMLERROR INVALIDATE, the privilege set must include the UPDATE privilege on the base tables.

Execution phases of CHECK DATA

Phase

Description

UTILINIT

Initializes utility processing.

CHECKXML

Checks XML structures for all XML table spaces that are specified by INCLUDE XML TABLESPACES.

SCANTAB

Extracts foreign keys. The utility uses an index if the index contains the same columns or a superset of the columns in the foreign key. Otherwise, the utility scans the table. The following are other conditions that will cause a scan of the table:

PART is specified.
The table contains both indexed and non-indexed foreign keys.
The table contains LOB columns.
The table contains XML columns.

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 deletes them from the source table if DELETE YES is specified.
UTILTERM: Performs cleanup.