12.19.5 Procedure for checking table integrity

The following figure shows an overview of the procedure for checking data integrity.

Figure 12-37 Overview of procedure for checking data integrity (referential constraint)

[Figure]

When the value specified in the pd_check_pending operand is USE, or the operand is omitted:
  1. Identify tables in check pending status
    Search SQL_TABLES of the dictionary table to detect the names of tables in check pending status.

    SELECT TABLE_SCHEMA, TABLE_NAME FROM MASTER.SQL_TABLES
       WHERE CHECK_PEND = 'C' OR CHECK_PEND2 = 'C'

    The owners and names of tables in check pending status are returned in the search result. If no rows are returned in the search result, no tables are in check pending status.
  2. Use the integrity check utility to check data integrity.
    Use the integrity check utility to check the data integrity of each table and to correct any data that violates constraint conditions. Repeat the procedure until no table remains in check pending status. For details about how to use the integrity check utility to check data integrity, see 12.19.5(1) Procedure for checking data integrity when check pending status is used (referential constraint).
When the value specified in the pd_check_pending operand is NOUSE:
  1. Identify the tables for which you want to check data integrity, and forcibly place these tables into check pending status.
    To identify tables on which to check data integrity, check the following items:
    • Whether a referencing table references a table on which an operation was performed that caused loss of data integrity
    • Whether a referential constraint has been defined in the table on which an operation was performed that caused loss of data integrity
    The following is an example SQL execution to check these items:

    SELECT N_PARENTS, N_CHILDREN FROM MASTER.SQL_TABLES
       WHERE TABLE_SCHEMA = 'name-of-the-owner-of-the-target-table' AND TABLE_NAME = 'name-of-the-target-table'

    The following search result is returned:
    • The number of foreign keys defined in the target table
    • The number of foreign keys that reference the primary key defined in the target table
    If N_PARENTS is a null value, no referential constraint is defined in the target table.
    If N_CHILDREN is a null value, no referencing table exists that references the target table as a referenced table.
    If the N_CHILDREN value is not null, execute the following SQL to check the name of the referencing table that references the target table.

    SELECT TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME
       FROM MASTER.SQL_REFERENTIAL_CONSTRAINTS
       WHERE R_OWNER = 'name-of-the-owner-of-the-target-table' AND R_TABLE_NAME = 'name-of-the-target-table'

    The owners, names, and referential constraint names of referencing tables that reference a target table as a referenced table are returned in the search results. If no rows are returned in the search result, no referencing table that references a target table as a referenced table exists.
    When one or more tables are identified, use the integrity check utility to forcibly place the tables into check pending status (the integrity check utility cannot be used to check tables that are not in check pending status).
  2. Use the integrity check utility to check data integrity.
    This step is the same as the step 2 used when the value specified in the pd_check_pending operand is USE, or the operand is omitted. For details about using the integrity check utility to check data integrity, see 12.19.5(2) Procedure for checking data integrity when check pending status is not used.
Organization of this subsection
(1) Procedure for checking data integrity when check pending status is used (referential constraint)
(2) Procedure for checking data integrity when check pending status is not used

(1) Procedure for checking data integrity when check pending status is used (referential constraint)

The following figure shows the procedure for checking data integrity using the integrity check utility when the value specified for the pd_check_pending operand is USE or is omitted.

Figure 12-38 Procedure for checking data integrity when check pending status is used (referential constraint)

[Figure]

  1. Check the data integrity of the next table to be checked.
    Check the data integrity for each table and constraint.
  2. Identify constraint violations.
    Based on the results of the data integrity check performed in step 1, determine whether any data violates constraint conditions.
  3. Correct data that violates constraints.
    Decide whether to use the utility or SQL code to correct the violating data. If you choose the utility, proceed to step 6.
  4. Stop operations on the table being checked.
    Stop performing tasks that use the table for which data integrity cannot be guaranteed.
  5. Forcibly cancel the check pending status of the table being checked.
    Before taking action to resolve constraint violations, forcibly cancel the check pending status.
  6. Take action to resolve constraint violations.
    Using the utility:
    The following table lists actions. After taking action, return to step 1 to perform data integrity checking, confirm that no violating data remains, and complete the procedure.
    ConditionAction
    The primary key does not contain the required dataLoad correct data using the addition mode of the database load utility (pdload).
    The foreign key contains constraint violation data
    • Load correct data using the addition mode of the database load utility (pdload).
    • Use UOC for the database reorganization utility (pdrorg) to delete unnecessary data.
    Using SQL code:
    The following table lists actions. After taking action, proceed to step 7.
    ConditionAction
    The primary key does not contain the required dataUse the INSERT statement to insert the required data in the primary key#1, or use the UPDATE statement to update existing data in the referenced table#2.
    The foreign key contains constraint violation dataUse the DELETE statement to delete the constraint violation data in the foreign key, or use the UPDATE statement to change the data to the correct value#1.
    #1
    If a foreign key is also a primary key, and a referencing table has a referenced table for which action is to be taken, you must be careful about the order in which the corrections are performed. For example, assume the following referential relationship exists:
    [Figure]
    [Figure]Notes when taking action for REF1 constraint violations
    If you use the DELETE statement to correct the data in table T2, if ON DELETE RESTRICT is specified in REF2, first delete the corresponding data in table T3 and then delete the data in table T2. If you use the UPDATE statement to correct the data, if ON UPDATE RESTRICT is specified in REF2, first delete the data in table T3 that corresponds to the pre-update data, and then update the data in table T2.
    [Figure]Notes when taking action for REF2 constraint violations
    If you use the INSERT statement to correct the data in table T2, check for insertion target data in table T1. If there is no such data, first insert the data into table T1 and then insert the data into table T2. If you use the UPDATE statement to correct the data, check whether post-update data exists in table T1. If there is no such data, first insert the data into table T1 and then update the data in table T2.
    #2
    About a constraint other than one for which an action is to be taken, if there is a referencing table that references that table as a referenced table, you must be careful about the order of corrections. For example, assume the following referential relationship exists:
    [Figure]
    [Figure]Notes when taking action for REF1 constraint violations
    If you use an UPDATE statement to correct the data in table T1, if ON UPDATE RESTRICT is specified in REF2, first delete the data in table T3 that corresponds to the pre-update date and then update the data in table T2.
  7. Forcibly place the violated constraint into check pending status.
    Execute the integrity check utility on each constraint, and forcibly place each constraint for which an action was taken into check pending status.
  8. Release the stopped operations.
    Resume performance of stopped tasks. Return to step 1 to perform data integrity checking and to check for violating data.

(2) Procedure for checking data integrity when check pending status is not used

The following figure shows the procedure for checking data integrity using the integrity check utility when the value specified for the pd_check_pending operand is NOUSE.

Figure 12-39 Procedure for checking data integrity when check pending status is not used

[Figure]

  1. Stop operations on the tables to be checked.
    Stop performing tasks that use tables for which data integrity cannot be guaranteed.
  2. Forcibly place the tables into check pending status.
    Forcibly place the tables to be checked into check pending status. If you perform data integrity checking for each constraint in step 3, this step is not necessary.
  3. Check the data integrity of the next table to be checked.
    Check the data integrity of each table and constraint.
  4. Identify constraint violations.
    Based on the results of the data integrity check performed in step 3, determine whether any data violates constraint conditions.
  5. Correct data that violates constraints.
    See step 6 in 12.19.5(1) Procedure for checking data integrity when check pending status is used (referential constraint) to correct data that violates constraints.
  6. Release the stopped operations.
    Resume performance of stopped tasks.