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.

You can create a control statement with the ISPF/PDF edit function. After you create it, save it in a sequential or partitioned data set. When you create the JCL for running the job, use the SYSIN DD statement to specify the name of the data set that contains the utility control statement.

Syntax diagram

                .-------------------------------------.   
                V                                     |   
>>-CHECK--DATA----table-space-spec--+---------------+-+--------->
                                    '-PART--integer-'     

>--+---------------------------------------------+--+-------+--->
   |                              .-ALL-.        |  '-CLONE-'   
   '-INCLUDE--XML -TABLESPACES--+-+-----+------+-'              
                                '-+----------+-'                
                                  '-xml-spec-'                  

   .-SHRLEVEL--REFERENCE-.   
>--+---------------------+-------------------------------------->
   '-SHRLEVEL--CHANGE----'   

               .-SCOPE--PENDING-----------.   
>--drain-spec--+--------------------------+--------------------->
               '-SCOPE--+-AUXONLY-------+-'   
                        +-ALL-----------+     
                        +-REFONLY-------+     
                        '-XMLSCHEMAONLY-'     

                        (1)     
   .-AUXERROR--REPORT-------.   
>--+------------------------+----------------------------------->
   '-AUXERROR--INVALIDATE---'   

   .-LOBERROR--REPORT-----.  .-XMLERROR--REPORT-----.   
>--+----------------------+--+----------------------+----------->
   '-LOBERROR--INVALIDATE-'  '-XMLERROR--INVALIDATE-'   

>--+-------------------------------------------------------+---->
   |                 .-----------------------------------. |   
   |                 V                                   | |   
   '-FOR--EXCEPTION----IN--table-name1--USE--table-name2-+-'   

   .-DELETE--NO----------------.  .-EXCEPTIONS--0-------.   
>--+---------------------------+--+---------------------+------->
   |              .-LOG--YES-. |  '-EXCEPTIONS--integer-'   
   '-DELETE--YES--+----------+-'                            
                  '-LOG--NO--'                              

   .-ERRDDN--SYSERR-.   
>--+----------------+------------------------------------------->
   '-ERRDDN--ddname-'   

   .-WORKDDN--SYSUT1--,--SORTOUT---------.   
>--+-------------------------------------+---------------------->
   '-WORKDDN--+-ddname1--,--ddname2----+-'   
              |          .-,--SYSUT2-. |     
              +-ddname1--+-----------+-+     
              | .-SYSUT1-.             |     
              '-+--------+--,--ddname2-'     

   .-PUNCHDDN--SYSPUNCH-.                              
>--+--------------------+--+-----------------------+------------>
   '-PUNCHDDN--ddname---'  '-SORTDEVT--device-type-'   

>--+------------------+----------------------------------------><
   '-SORTNUM--integer-'

Notes:

If you specify AUXERROR and LOBERROR or XMLERROR, the options for the keywords (REPORT and INVALIDATE) must match.

Start of change xml-spec: End of change

      .-,--------------------.                     
      V                      |                     
>>-(----+-table-space-spec-+-+--)--+-----------+---------------><
        '-xml-column-spec--'       '-XMLSCHEMA-'

Start of change xml-column-spec: End of change

>>-TABLE----+-------------+------------------------------------->
            '-schema-name-'     

>--table-name--XMLCOLUMN--column-name--------------------------><

Start of change table-space-spec: End of change


>>-TABLESPACE--+----------------+--table-space-name------------><
               '-database-name.-'

drain-spec:


   .-DRAIN_WAIT--integer-.  .-RETRY--integer-.   
>>-+---------------------+--+----------------+------------------>

   .-RETRY_DELAY--integer-.   
>--+----------------------+------------------------------------><

Option descriptions

DATA

Indicates that you want the utility to check referential and table check constraints. CHECK DATA does not check informational referential constraints.

TABLESPACE database-name.table-space-name

Specifies the table space to which the data belongs. Start of change

You can specify base table spaces or, if TABLESPACE is specified as a part of the INCLUDE XML TABLESPACES option, XML table spaces. TABLESPACE cannot be used to specify LOB table spaces. End of change

database-name is the name of the database and is optional. The default value is DSNDB04.

table-space-name is the name of the table space.

PART integer

Identifies which partition to check for constraint violations.

Start of change integer is the physical partition number. It must be in the range from 1 to the number of partitions that are defined for the table space. The maximum is 4096. End of change

INCLUDE XML TABLESPACES

Indicates that CHECK DATA is to perform consistency checks on the specified XML table spaces and related node ID indexes.

By default, the utility checks only the XML table spaces and their related node ID indexes. If an XML type modifier exists for an XML column and xml-spec is specified, XML documents can also be checked against the stored XML schemas. Specify XMLSCHEMA on the xml-spec option to enable the check against stored XML schemas.

The consistency checks enabled by INCLUDE XML TABLESPACE are performed in addition to the existing checks specified by the SCOPE keyword.

XML indexes that are associated with the XML table spaces that are checked are not verified. Run the CHECK INDEX utility separately on those indexes.

The following checks are performed:

The XML table space is checked to ensure that all rows of each XML document are present in the XML table space and that the XML document is structurally intact.
All entries in the node ID index are checked against the rows in the XML table space. Each index entry must have a corresponding row in the XML table space, and vice versa. This functionality is equivalent to running the CHECK INDEX utility on the node ID index.
All values in the document ID column are checked against the node ID index. Each document ID value must have matching entries in the node ID index. Each node ID index value must also have a document ID value.
If XMLSCHEMA is specified, CHECK DATA validates documents that are stored in that column. When a document is validated, the base table row is updated with the validated document that is returned when SHRLEVEL REFERENCE and XMLERROR INVALIDATE or AUXERROR INVALIDATE are specified.

ALL

Checks all XML table spaces that are related to the base table spaces that are identified by the table-space-spec. Specifying ALL is equivalent to explicitly specifying all the XML column identifiers.

xml-spec

Checks only those XML table spaces and related node ID indexes that are identified by either the XML column of a table or by the explicit table space name.

Each XML column has a single XML table space that is associated with it. Therefore, an XML table space can be identified either by the XML column of the base table or by the explicit table space name.

If an XML column identifier is used, the utility finds the name of the XML table space in the DB2® catalog or the database directory.

table-space-spec: Identifies an XML table space to check. The XML table space specification must identify an XML table space that has a corresponding column in a base table. The base table must reside in the table space that is identified by the table-space-spec option of the main CHECK DATA control statement.
xml-column-spec: Identifies an XML table space to check by the XML column of the XML table space in a base table. An XML column identifier consists of the fully qualified table name and the name of the XML column. An XML column identifier must reference a table in any one of the base table spaces that are to be checked.
XMLSCHEMA: Specifies that if the XML columns have an XML type modifier, the CHECK DATA utility checks the XML documents against the stored XML schema.

CLONE

Indicates that CHECK DATA is to check the clone table in the specified table space. Because clone tables cannot have referential constraints, the utility checks only constraints for inconsistencies between the clone table data and the corresponding LOB data. If you do not specify CLONE, CHECK DATA operates against only the base table.

SHRLEVEL

Indicates the type of access that is to be allowed for the index, table space, or partition that is to be checked during CHECK DATA processing.

REFERENCE

Specifies that applications can read from but cannot write to the index, table space, or partition that is to be checked.

Start of change The CHECK DATA utility can write changes to the table space, index space, or partition during processing. End of change

Restriction: Start of change

You cannot run CHECK DATA with the SHRLEVEL REFERENCE option on a table space that contains a system-period temporal table when one of the following options is also specified:

DELETE YES
LOBERROR INVALIDATE
AUXERROR INVALIDATE
XMLERROR INVALIDATE

CHANGE

Specifies that applications can read from and write to the index, table space, or partition that is to be checked.

Start of change The CHECK DATA utility operates on shadow copies only and does not change the table space, index space, or partition during processing. REPAIR statements are generated for any changes to be made and are written to the data set that is indicated in the PUNCHDDN option. CHECK DATA does not generate REPAIR statements for inconsistencies that it finds in compressed rows if you specify SHRLEVEL CHANGE and one of the following options: End of change

AUXERROR INVALIDATE
LOBERROR INVALIDATE
XMLERROR INVALIDATE

If you specify SHRLEVEL CHANGE, DB2 performs the following actions:

Drains all writers and forces the buffers to disk for the specified object and all of its indexes
Invokes DFSMSdss to copy the specified object and all of its indexes to shadow data sets
Enables read/write access for the specified object and all of its indexes
Runs CHECK INDEX on the shadow data sets

Start of change By default, DFSMSdss uses FlashCopy® to copy DB2 objects to shadow data sets, if FlashCopy is available. If DFSMSdss cannot use FlashCopy, DFSMSdss uses a slower method. As a result, creating copies of objects might take a long time, and the time during which the data and indexes have read-only access might increase. You can set the CHECK_FASTREPLICATION subsystem parameter to REQUIRED to force the CHECK utility to use only FlashCopy. If FlashCopy is not available, the CHECK utility fails. End of change

DRAIN_WAIT

Specifies the number of seconds that CHECK DATA is to wait when it drains the table space or index. The specified time is the aggregate time for objects that are to be checked. This value overrides the values that are specified by the IRLMRWT and UTIMOUT subsystem parameters.

integer can be any integer from 0 to 1800. If you do not specify DRAIN_WAIT or specify a value of 0, CHECK DATA uses the value of the lock timeout subsystem parameter IRLMRWT.

RETRY integer

Specifies the maximum number of retries that CHECK DATA is to attempt.

integer can be any integer from 0 to 255. If you do not specify RETRY, CHECK DATA uses the value of the utility multiplier system parameter UTIMOUT.

Specifying RETRY can increase processing costs and result in multiple or extended periods during which the specified index, table space, or partition is in read-only access.

RETRY_DELAY integer

Specifies the minimum duration, in seconds, between retries. integer can be any integer from 1 to 1800.

If you do not specify RETRY_DELAY, CHECK DATA uses the smaller of the following two values:

DRAIN_WAIT value × RETRY value
DRAIN_WAIT value × 10

SCOPE

Limits the scope of the rows in the table space that are to be checked.

PENDING

Indicates that the only rows to be checked are those rows that are in table spaces, partitions, or tables that are in CHECK-pending (CHKP) status. Start of change

For each or these rows, CHECK DATA checks all referential and check constraints and LOB and XML columns. End of change

Start of change When CHECK DATA checks XML columns, the utility verifies the relationship between the node ID index and the values in the XML indicator column in the base table space. If the INCLUDE XML TABLESPACES option is specified, CHECK DATA also validates the schema for all specified XML table spaces that satisfy both of the following conditions: End of change

Are in CHKP status
Reference a table in any of the base table spaces to be checked

If you specify SCOPE PENDING for a table space that is not in CHKP status, CHECK DATA does not check the table space. The utility does not issue an error message.

AUXONLY

Indicates that only the LOB and XML columns are to be checked for table spaces that have tables with LOB columns or XML columns. CHECK DATA does not check referential and check constraints.

When CHECK DATA checks XML columns, the utility verifies only the relationship between the node ID index and the values in the XML indicator column in the base table space.

ALL

Indicates that all dependent tables in the specified table spaces are to be checked. CHECK DATA checks all referential and check constraints and LOB and XML columns.

Start of change If the INCLUDE XML TABLESPACES option is specified, the associated XML table space and node ID index are checked for structural defects and inconsistencies. End of change

REFONLY

Indicates the same behavior as the ALL option, except that the LOB and XML columns are not checked. CHECK DATA checks all referential and check constraints.

XMLSCHEMAONLY

Indicates that only the XML schema is to be validated for the XML objects that are specified by the INCLUDE XML TABLESPACE option. CHECK DATA does not check XML and LOB column integrity or referential and check constraints. End of change

AUXERROR

Specifies the action that CHECK DATA is to perform when it finds a LOB or XML column check error.

REPORT

A LOB or XML column check error is reported with a warning message. The base table space is set to the auxiliary CHECK-pending (ACHKP) status.

Note:

CHECK DATA sets the base table space to ACHKP status if SHRLEVEL REFERENCE is specified. If SHRLEVEL CHANGE is specified, CHECK DATA does not change the status of the base table space. End of change

INVALIDATE

A LOB or XML column check error is reported with a warning message. The base table LOB or XML column is set to an invalid status. A LOB or XML column with invalid status that is now correct is set valid. This action is also reported with a message. The base table space is set to the auxiliary warning (AUXW) status if any LOB column remains in invalid status.

Start of change If SHRLEVEL REFERENCE is specified, CHECK DATA sets the base table of a LOB or XML column to an invalid status and the base table space to AUXW status. If SHRLEVEL CHANGE is specified, CHECK DATA does not change the status of the base table space or a LOB or XML column. End of change

Start of change If SHRLEVEL REFERENCE and INCLUDE XML TABLESPACES are specified, CHECK DATA deletes corrupted XML documents and the associated node ID index entries. If the node ID index is not consistent with the content in the XML table, CHECK DATA corrects the node ID index. End of change

Restriction: Start of change

You cannot run CHECK DATA SHRLEVEL REFERENCE with AUXERROR INVALIDATE on a table or a history table that is defined with data versioning. End of change

Before you use CHECK DATA to check a LOB or XML column, take the following actions:

Run CHECK LOB to ensure the validity of the LOB table space.
Run REBUILD INDEX or CHECK INDEX on the index on the auxiliary table to ensure its validity.
Run REBUILD INDEX or CHECK INDEX on the NODE ID index on the XML table space to ensure its validity.

LOBERROR

Specifies the action that CHECK DATA is to perform when it finds a LOB column check error. Do not specify LOBERROR if AUXERROR is specified. If both are specified, the keywords must match. LOBERROR is ignored for SCOPE XMLONLY since LOB checking is not being performed.

REPORT: A LOB column check error is reported with a warning message. The base table space is set to the auxiliary CHECK-pending (ACHKP) status.
If AUXERROR is not specified, the default value is REPORT.
INVALIDATE: A LOB column check error is reported with a warning message. The base table LOB column is set to an invalid status. A LOB column with invalid status that is now correct is set valid. The base table space is set to the auxiliary warning (AUXW) status if any LOB column remains in invalid status.
Restriction: You cannot run CHECK DATA with LOBERROR INVALIDATE on a table or a history table that is defined with data versioning.

XMLERROR

Specifies the action that CHECK DATA is to perform when it finds an XML column check error. Do not specify XMLERROR if AUXERROR is specified. If both are specified, the keywords must match. XMLERROR is ignored for SCOPE XMLONLY since LOB checking is not being performed.

REPORT

An XML column check error is reported with a warning message. The base table space is set to the auxiliary CHECK-pending (ACHKP) status.

If AUXERROR is not specified, the default value is REPORT.

Note:

CHECK DATA sets the base table space to ACHKP status if SHRLEVEL REFERENCE is specified. If SHRLEVEL CHANGE is specified, CHECK DATA does not change the status of the base table space. End of change

INVALIDATE

An XML column check error is reported with a warning message. The base table XML column is set to an invalid status. An XML column with invalid status that is now correct is set valid. The base table space is set to the auxiliary warning (AUXW) status if any LOB column remains in invalid status.

Start of change CHECK DATA sets the base table of a LOB or XML column to an invalid status and the base table space to AUXW only if SHRLEVEL REFERENCE is specified. If SHRLEVEL CHANGE is specified, CHECK DATA does not change the status of the base table space or a LOB or XML column. End of change

Restriction: Start of change

You cannot run CHECK DATA SHRLEVEL REFERENCE with XMLERROR INVALIDATE on a table or a history table that is defined with data versioning. End of change

FOR EXCEPTION

Indicates that any row that is in violation of referential or table check constraints is to be copied to an exception table. Although this keyword does not apply to the checking of LOB or XML columns, rows with LOB or XML columns are moved to the exception tables. If you specify AUXONLY for LOB and XML checking only, the FOR EXCEPTION option is ignored.

If any row violates more than one constraint, that row is included only once in the exception table. Start of change CHECK DATA includes checking for XML schema violations and XML structure checking. End of change

This option is ignored when SHRLEVEL CHANGE is specified.

Start of change If you run CHECK DATA on a base table with XML columns, the EXCEPTIONS keyword has an effect only if the INCLUDE XML TABLESPACES option is also specified. End of change

IN table-name1: Specifies the table (in the table space that is specified on the TABLESPACE keyword) from which rows are to be copied.
table-name1 is the name of the table.
USE table-name2: Specifies the exception table into which error rows are to be copied.
table-name2 is the name of the exception table and must be a base table; it cannot be a view, synonym, or alias.

Start of change For both table-name1 and table-name2, enclose the table name in quotation marks if the name contains a blank or a special character. (A special character is any character other than a letter or a digit.) End of change

DELETE

Indicates whether rows that are in violation of referential or table check constraints are to be deleted from the table space. Start of change

You can specify DELETE only if you specify the FOR EXCEPTION clause. End of change

NO

Indicates that error rows are to remain in the table space. Primary errors in dependent tables are copied to exception tables.

If DELETE NO and SHRLEVEL REFERENCE are specified, and constraint violations are detected, CHECK DATA places the table space in the CHECK-pending status.

YES

Indicates that error rows are to be deleted from the table space.

When you specify FOR EXCEPTION, deleted rows from both dependent and descendant tables are placed into exception tables.

If you specify SHRLEVEL REFERENCE, error rows are deleted from the table space. If you specify SHRLEVEL CHANGE, CHECK DATA generates REPAIR LOCATE DELETE statements that you can run to delete the rows. These statements are written to the PUNCHDDN data set.

Important: Start of change

Check any generated REPAIR statements after you run CHECK DATA SHRLEVEL CHANGE on tables that have data versioning activated or on history tables. Historic information could be deleted. End of change

If you delete rows from a table space that is not logged, the table space is placed in informational COPY-pending (ICOPY) status.

Restriction: Start of change

You cannot run CHECK DATA with DELETE YES on a table or a history table that is defined with data versioning. End of change

LOG

Specifies the logging action that is to be taken when records are deleted.

YES: Logs all records that are deleted during the REPORTCK PHASE.
If the table space has the NOT LOGGED attribute, LOG YES is ignored.
NO: Does not log any records that are deleted during the REPORTCK phase. Other types of log records are still written. If any rows are deleted, CHECK DATA places the table space in COPY-pending status and any indexes with the COPY YES attribute in informational COPY-pending status. If rows are deleted from a table space that is not logged, the table space is marked informational COPY-pending.

Attention: Use the LOG NO option with caution because its use limits your ability to recover data by using the log. For example, suppose that you issue a CHECK DATA DELETE YES LOG NO statement at particular log RBA. You can recover data that exists on the log before that point in time or after the point on the log at which the utility execution completes.

EXCEPTIONS integer

Specifies the maximum number of exceptions, which are reported by messages only. CHECK DATA terminates in the CHECKDATA phase when it reaches the specified number of exceptions; if termination occurs, the error rows are not written to the EXCEPTION table.

Only records that contain primary referential integrity errors or table check constraint violations are applied toward the exception limit. The number of records that contain secondary errors is not limited.

integer is the maximum number of exceptions. The default value is 0, which indicates no limit on the number of exceptions.

This keyword does not apply to LOB table spaces or base table spaces that contain XML columns.

ERRDDN ddname

Specifies a DD statement for an error processing data set.

ddname is either a DD name or a TEMPLATE name specification from a previous TEMPLATE control statement. If utility processing detects that the specified name is both a DD name in the current job step and a TEMPLATE name, the utility uses the DD name. The default value isSYSERR.

WORKDDN (ddname1,ddname2)

Specifies the DD statements for the temporary work file for sort input and the temporary work file for sort output. A temporary work file for sort input and output is required.

You can use the WORKDDN keyword to specify either a DD name or a TEMPLATE name specification from a previous TEMPLATE control statement. If utility processing detects that the specified name is both a DD name in the current job step and a TEMPLATE name, WORKDDN uses the DD name.

ddname1 is the DD name of the temporary work file for sort input. The default is SYSUT1.

ddname2 is the DD name of the temporary work file for sort output. The default is SORTOUT.

PUNCHDDN ddname

Specifies the DD statement for a data set that is to receive the REPAIR utility control statements that CHECK DATA SHRLEVEL CHANGE generates.

ddname is the DD name.

The default value is SYSPUNCH.

The PUNCHDDN keyword specifies either a DD name or a TEMPLATE name specification from a previous TEMPLATE control statement. If utility processing detects that the specified name is both a name in the current job step and a TEMPLATE name, the utility uses the DD name.

SORTDEVT device-type

Specifies the device type for temporary data sets that are to be dynamically allocated by a sort program. You can specify any disk device type that is acceptable to the DYNALLOC parameter of the SORT or OPTION control statement for the sort program. Start of change

Tape devices are not supported by the sort program. End of change

Do not use a TEMPLATE specification to dynamically allocate sort work data sets. The presence of the SORTDEVT keyword controls dynamic allocation of these data sets.

device-type is the device type. If you omit SORTDEVT and a sort is required, you must provide the DD statements that the sort program requires for the temporary data sets.

SORTNUM integer

Specifies the number of temporary data sets that are to be dynamically allocated by the sort program.

integer is the number of temporary data sets that can range from 2 to 255.

If you omit SORTDEVT, SORTNUM is ignored. If you use SORTDEVT and omit SORTNUM, no value is passed to the sort program; the sort program uses its own default.

You need at least two sort work data sets for each sort. The SORTNUM value applies to each sort invocation in the utility.

Important: The SORTNUM keyword is ignored if the UTSORTAL subsystem parameter is set to YES and the IGNSORTN subsystem parameter is set to YES.