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.

The following table describes the contents of an exception table. This table lists the columns, a description of the column content, whether or not the column is required, the data type and length of the column value, and whether or not the column has the NULL attribute.

Table 1. Contents of exception tables
Column Description Required? Data type and length NULL attribute
1 to n Corresponds to columns in the table that is being checked. These columns hold data from table rows that violate referential or table check constraints. Yes The same as the corresponding columns in the table that is being checked. The same as the corresponding columns in the table that is being checked.
n+1 Identifies the RIDs of the invalid rows of the table that is being checked. No CHAR(4); CHAR(5)1 for table spaces that are defined with LARGE or DSSIZE options Anything
n+2 Indicates the starting time of the CHECK DATA utility. No TIMESTAMP Anything
≥ n+2 Additional columns that the CHECK DATA utility does not use. No Anything Anything
Note:
  1. You can use CHAR(5) for any type of table space, but you must use it for table spaces that are defined with the LARGE or DSSIZE options.

If you delete rows by using the CHECK DATA utility with SCOPE ALL, you must create exception tables for all tables that are named in the table spaces and for all their descendents. All descendents of any row are deleted.

When creating or using exception tables, be aware of the following guidelines:

  • The exception tables should not have any unique indexes or referential or table check constraints that might cause errors when CHECK DATA inserts rows into them.
  • You can create a new exception table before you run CHECK DATA, or you can use an existing exception table. The exception table can contain rows from multiple invocations of CHECK DATA.
  • If column n+2 is of type TIMESTAMP, CHECK DATA records the starting time. Otherwise, it does not use column n+2.
  • You must have DELETE authorization on the dependent table that is being checked.
  • You must have INSERT authorization on the exception table.
  • Column names in the exception table can have any name.
  • Any change to the structure of the dependent table (such as a dropped column) is not automatically recorded in the exception table. You must make that change in the exception table.