CHECK-pending status after running LOAD

The LOAD utility places a table space in CHECK-pending (CHKP) status if its referential integrity is in doubt or its check constraints are violated.

To reset this status, run the CHECK DATA utility, which locates invalid data and, optionally, removes it. If CHECK DATA removes the invalid data, the remaining data satisfies all check and referential constraints and therefore, the CHECK-pending restriction is removed.

Although CHECK DATA is usually the preferred method for resetting CHECK-pending status, you can also reset this status by using any of the following operations:

  • Drop tables that contain invalid rows.
  • Replace the data in the table space by using LOAD REPLACE and enforcing check and referential constraints.
  • Recover all members of the table space that were set to a prior quiesce point.
  • Use the REPAIR utility with the SET STATEMENT and NOCHECKPEND option.

Example of CHKP status after LOAD REPLACE

Suppose that you replace the contents of the sample project table (PROJ) by using LOAD with the REPLACE option. You also specify that LOAD is to enforce the table's referential and table check constraints. When the LOAD job completes, the project table contains only valid records and is not in CHECK-pending status. However, its dependent table, the project activity table (PROJACT), is placed in CHECK-pending status. Some of its rows might have project numbers that no longer exist in the project table. (If the project table had any other dependents, they also would be in CHECK-pending status.)

In this case, run CHECK DATA against the table space that contains the project activity table to reset the status. When you run CHECK DATA, ensure that all related table spaces are available. Related table spaces means all table spaces that contain either parent tables or dependent tables of any table in the table space that is being checked.

Also, in the CHECK DATA statement, consider the value that you specify for the DELETE option as follows:

YES
DELETE YES deletes invalid records and resets the status, but it is not the default value. If you specify DELETE YES, you use another table that is called an exception table to receive copies of the invalid records. For example, if table Y is the exception table for table X, name it with the following clause in the CHECK DATA statement: 
FOR EXCEPTION IN X USE Y
Deletes by CHECK DATA are not subject to any of the SQL delete rules; they cascade without restraint to the lowest-level descendant. Therefore, if you use DELETE YES, you must name an exception table for every descendant of every table in every table space that is being checked.
NO
Use DELETE NO, the default value, to quickly determine the size of the problem. If many rows are affected, you can choose to correct the problem by reloading the data rather than using CHECK DATA to correct the current situation.
Example:

In the following example, CHECK DATA is to be run against the table space that contains the project activity table (PROJACT). Assume that the exception tables DSN8A10.EPROJACT and DSN8A10.EEPA exist. 

CHECK DATA TABLESPACE DSN8D10A.PROJACT
   DELETE YES
   FOR EXCEPTION IN DSN8A10.PROJACT USE DSN8A10.EPROJACT
                 IN DSN8A10.EMPPROJACT USE DSN8A10.EEPA
   SORTDEVT SYSDA
   SORTNUM 4

If the statement does not name error or work data sets, the JCL for the job must contain DD statements similar to the following DD statements: 

//SYSERR   DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSUT1   DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//UTPRINT DD SYSOUT=A

Example of CHKP status after LOAD RESUME

Suppose that you want to add records to both the sample project (PROJ) and project activity (PROJACT) tables by using LOAD with the RESUME option. Furthermore, you want to run both jobs at the same time, which you can do because the tables belong to separate table spaces. When you load the project activity table, you must use the ENFORCE NO option in the LOAD statement because you cannot assume that the parent project table is already loaded.

After the two jobs are complete, the table spaces are in the following states:

  • If you enforced constraints when loading the project table, the table space is not in CHECK-pending status.
  • Because you did not enforce constraints on the project activity table, the table space is in CHECK-pending status.
  • Because you used LOAD RESUME (not LOAD REPLACE) when loading the project activity table, its dependents (the employee-to-project-activity table) are not in CHECK-pending status. That is, the LOAD operation did not delete any parent rows from the project activity table, and therefore cannot violate the referential integrity of its dependent.

Therefore, you should run CHECK DATA on the project activity table. If you specify DELETE YES, you need an exception table for PROJACT and an exception table for EMPPROJACT, which is dependent on PROJACT.

To speed CHECK DATA processing, specify the SCOPE PENDING option to limit the checking.

DB2® records the identifier of the first row of the table that might violate referential or table check constraints. For partitioned table spaces, that identifier is in SYSIBM.SYSTABLEPART; for nonpartitioned table spaces, that identifier is in SYSIBM.SYSTABLES.

Example:

In the following example, CHECK DATA is to be run against the table space that contains the project activity table after LOAD RESUME: 

CHECK DATA TABLESPACE DSN8D10A.PROJACT
   SCOPE PENDING
   DELETE YES
   FOR EXCEPTION IN DSN8A10.PROJACT USE DSN8A10.EPROJACT
                 IN DSN8A10.EMPPROJACT USE DSN8A10.EEPA
   SORTDEVT SYSDA
   SORTNUM 4

As before, the JCL for the job needs DD statements to define the error and sort data sets.