13.19.5 Procedure for checking table integrity

Figure 13-39 shows the procedure for checking data integrity.

Figure 13-39 Overview of the procedure for checking data integrity (check 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.
    To check whether each generation table is in check pending status when the inner replica facility is used, you can use the condition analysis utility (pddbst).
  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 13.19.5(1) Procedure for checking data integrity when check pending status is used (check 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.
    Check whether a check constraint is defined for a table on which an operation that causes loss of guaranteed data integrity was performed. The following shows an example of SQL code for checking this.

    SELECT N_CHECK 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 check constraint definitions
    When N_CHECK is a null value, no check constraint is defined for the target table.
    After identifying the tables, use the integrity check utility to forcibly place the tables into check pending status (you cannot use the integrity check utility to check tables that are not in check pending status).
  2. Use the integrity check utility to check 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. The procedure for checking data integrity is the same as that used for a referential constraint; for details, see 13.18.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 (check constraint)

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

Figure 13-40 shows how to use the integrity check utility to check data integrity when the value specified in the pd_check_pending operand is USE, or the operand is omitted.

Figure 13-40 Procedure for checking data integrity when check pending status is used (check constraint)

[Figure]

  1. Check the data integrity of the tables to be checked.
    Check the data integrity for each table and constraint.
    If you use the inner replica facility, specify the generation numbers of the tables to be checked. If you do not use the inner replica facility or if you plan to check the data integrity of all generations, you do not need to specify the generation numbers.
  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 tables to be checked.
    Stop performing tasks that use tables for which data integrity cannot be guaranteed.
  5. Forcibly cancel the check pending status of the tables to be 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
    When correcting search conditions specified in the check constraintTo correct search conditions:
    1. Unload all data in the table.
    2. Use DROP TABLE to delete the table definition.
    3. Use CREATE TABLE to redefine the table. At this time, specify the correct check constraint search conditions.
    4. Load the data that was unloaded in step 1.
    When there is constraint violation data in the table
    • Use the database load utility (pdload) to load data in creation mode.
    • 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
    When correcting search conditions specified in the check constraintSame as the action when the utility is used.
    When there is constraint violation data in the tableUse the DELETE statement to delete the constraint violation data, or use the UPDATE statement to update it to the correct value.#
    #
    If a referencing table references the table for which an action is to be taken, as a referenced table, you must follow a specific order of corrections. For example, assume the following referential relationship exists:
    [Figure]
    [Figure]Notes when taking action for CHK1 constraint violations
    If you use the DELETE statement to correct the data in table T1, if ON DELETE RESTRICT is specified in REF1, first delete the corresponding data in table T2 and then delete the data in table T1. If you use the UPDATE statement to correct the data, if ON UPDATE RESTRICT is specified in REF1, first delete the data in table T2 that corresponds to the pre-update date and then update the data in T1.
  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 jobs. Return to step 1 to perform data integrity checking and to check for violating data.
  9. Check for the existence of more generations to be checked.
    When you have created replica RDAREAs of multiple generations or have performed data integrity checking for each generation, return to step 1 to check the data integrity of each generation.