Chapter 31. Data Consistency Utilities

IN THIS CHAPTER

• The CHECK Utility

• The CHECK DATA Option

• The CHECK LOB Option

• The CHECK INDEX Option

• The REPAIR Utility

• The REPAIR DBD Option

• The REPAIR LOCATE Option

• The REPAIR SET Option

• REPAIR and Versions

• The REPORT Utility

• The DIAGNOSE Utility

Often, the consistency of data in a DB2 database must be monitored and controlled. In the scope of DB2 databases, consistency encompasses four things:

• The consistency of reference from index entries to corresponding table rows

• The consistency of reference from LOB entries to corresponding table rows

• The consistency of data values in referential structures

• The consistency of data values conforming to check constraints

• The general consistency of DB2 data sets and data

Recall from previous chapters that a DB2 index is composed of column key values and RID pointers to rows in the DB2 table containing these values. Because the table and index information are in different physical data sets, the information in the index could become invalid. If the index key values or pointers become inconsistent, you would want to be able to pinpoint and correct the inconsistencies. This is the first type of consistency.

When LOB columns are specified in a DB2 table, the data is not physically stored in the same table space as the rest of the data in the table. An auxiliary table is required for each LOB column in the table. The primary table space maintains pointers to the auxiliary table pages where the LOBs are actually stored. Because the primary table data and the LOB data reside in different physical data sets, the pointers in the primary table could become invalid. If the LOB pointers become inconsistent, you would want to be able to pinpoint and correct the inconsistencies. This is the second type of consistency.

The third type of consistency refers to the referential integrity feature of DB2. When a primary-key-to-foreign-key relationship is defined between DB2 tables, a referential structure is created. Every foreign key in the dependent table must either match a primary key value in the parent table or be null. If, due to other utility processing, the referential integrity rules are violated, you must be able to view and possibly correct the violations.

The fourth type of consistency refers to ensuring that data values conform to specific values (or ranges of values). This is implemented using check constraints. A check constraint uses expressions to place specific data value restrictions on the contents of a column. The expression is explicitly defined in the table DDL and is formulated in much the same way that SQL WHERE clauses are formulated. Every data value stored in a column with a check constraint should conform to the pre-defined check constraint expression.

General consistency is the final type of consistency. If portions of DB2 table space and index data sets contain invalid, inconsistent, or incorrect data because of hardware or software errors, you want to be able to correct the erroneous information.

The data consistency utilities are used to monitor, control, and administer these three types of data consistency errors. There are three data consistency utilities ( CHECK , REPAIR , and REPORT ) with a total of five functions. This chapter describes all of them.