Skip to Content

Types of checking

CHECK PLUS offers three types of integrity checking as specified in the CHECK TABLESPACE, CHECK INDEX, and CHECK DATA commands. Within each of these types, you can customize the CHECK PLUS checks by selecting various options. For more detailed information, see Considerations for each type of checking.

Related topics

Examples of CHECK PLUS jobs

CHECK command syntax

SQL batch execution program

Tasks that CHECK PLUS performs

The following table describes the types of checking that you can perform with each command:

What CHECK PLUS checks

CHECK TABLESPACE

CHECK INDEX

CHECK DATA

Table space level 1
Intrapage structure

*

*

*

Table space level 2
Interpage structure

*

*

*

Index level 1
Intrapage structure

 

*

*

Index level 2
Index key-to-data consistency

 

*

 

Validation of RI constraints dependent tables or all tables in a relational set

 

 

*

Validation of RI constraints dependent tables or all tables in a relational set

*

*

*

CHECK TABLESPACE command

CHECK TABLESPACE checks the structural integrity of the Db2 objects within a table space:

  • Level 1 table space checking checks structural consistency within each data page.
  • Level 2 table space checking checks structural consistency across data pages.

Level 2 table space checking also validates column data if you specify the VERIFY option or if table check constraints exist.

CHECK INDEX command

CHECK INDEX checks the integrity of the index structure:

  • Level 1 index checking ensures structural consistency of the index pages.
  • Level 2 index checking verifies index page consistency and index key to data consistency.

If running level 2 index checking, CHECK INDEX can also concurrently run any of the CHECK TABLESPACE levels.

CHECK DATA command

CHECK DATA checks Db2-defined RI and allows you to specify additional referential constraints that are not defined to Db2. It also verifies column data if table check constraints exist or via the VERIFY option. CHECK PLUS can run CHECK TABLESPACE level 1 or 2, or run CHECK INDEX level 1 on pages that it reads while performing the RI and table check constraint checking.

Warning

Important

  • CHECK PLUS does not verify column data for ROWID or LOB columns.
  • If you are running CHECK DATA with CHECKTSLEVEL, CHECKIXLEVEL, or the VERIFY option, these operations are performed only on pages being read by CHECK DATA to perform RI and table check constraint checking. Thus, specifying the CHECKTSLEVEL or CHECKIXLEVEL options on the CHECK DATA command does not ensure the integrity of the entire table space or index structures. Specifying the VERIFY option on the CHECK DATA command does not ensure that the VERIFY is done. The VERIFY is not done when there are no constraints defined for the table space or if the object table space is not read during constraint checking.

Using the CHECK TABLESPACE or CHECK INDEX command with the CHECKIXLEVEL or the VERIFY options ensures that CHECK PLUS performs these actions regardless of constraint definitions.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

CHECK PLUS for DB2 13.1