5.4.15 constraint statement (specification of check pending status)

The constraint statement specifies settings for check pending status.

When you perform data loading on a table for which check constraints or referential constraints have been defined, pdload may store data whose constraint integrity cannot be guaranteed because the utility does not check data integrity. To restrict data manipulation on a table until its integrity has been verified by pdconstck, the constraint statement places a table that is subject to data loading and referencing tables related to such a table in check pending status.

You can also specify a control statement that will prevent a table's check pending status from being changed. For details about check pending status, see the manual HiRDB Version 8 Installation and Design Guide.

Criteria
If either of the following is applicable, you should specify the constraint statement:
  • When data loading is performed on a table for which check constraints or referential constraints have been defined, the table's check pending status should not be changed (set).
  • When data loading is performed on a referenced table, the check pending status of a referencing table related to that referenced table should not be changed (set).
Rules
  1. This control statement is applicable only when the value of the pd_check_pending operand in the system definition is USE. If any of the following is applicable, the constraint statement is ignored and the table's check pending status is not changed:
    [Figure]The pd_check_pending operand's value in the system definition is NOUSE.
    [Figure]No constraint has been defined for the table.
    [Figure]A divided-input data file is to be created (the src_work statement is specified).
Organization of this subsection
(1) Format
(2) Explanation
(3) Notes
(4) Range of check pending status settings
(5) Example of whether or not check pending status can be set
(6) Timing of setting check pending status

(1) Format

 constraint [pending=no]

          [ref_pending=no]

(2) Explanation

(a) pending=no

Specifies that when data loading is to be performed on a referencing table or check constraint table, the table's check pending status is not to be changed. For details about the default value that is assumed when this operand is omitted, see (4) Range of check pending status settings.

Criteria
Specify pending=no when either of the following is applicable:
  • When data loading is performed on a table for which a referencing table or check constraints have been defined, the integrity of the data that is to be stored in columns has been guaranteed by prior user checking.
  • Data loading is performed with a data count of 0 in order to re-create the indexes for a table for which a referencing table or check constraints have been defined.
(b) ref_pending=no

Specifies that when data loading is to be performed on a referenced table, the check pending status of a referencing table related to the referenced table is not to be changed. For details about the default value that is assumed when this operand is omitted, see (4) Range of check pending status settings.

Criteria
Specify ref_pending=no when either of the following is applicable:
  • When data loading is performed on a referenced table in the creation mode, the integrity of the data in the referenced table and referencing table has been guaranteed by prior user checking.
  • Data loading is performed with a data count of 0 in order to re-create the indexes for a referenced table.

(3) Notes

  1. For notes about setting related referencing tables in check pending status when data loading is performed on a referenced table, see C. RDAREA Status During Command Execution.
  2. The current RDAREA is placed in check pending status when the inner replica facility is being used and the following are applicable: data loading is performed on a referenced table, the referencing table related to the referenced table is to be placed in check pending status, and the replica RDAREA storing the referencing table is not in the generation specified in the -q option.
  3. When check pending status is set, the related resources are locked. Once setting of the check pending status has been completed, the resources are unlocked. For details about the lock, see B.2 Lock mode for utilities.

(4) Range of check pending status settings

Table 5-3 shows the range of check pending status settings for check constraints. Table 5-4 shows the range of check pending status settings for referential constraints.

Table 5-3 Range of check pending status settings for check constraints

Data loading execution conditionCheck pending status for check constraint
pd_check_pending operand value in system definitionUnit of executionData dictionary tableTable information in RDAREA
SQL_TABLES table
CHECK_PEND2 column
SQL_CHECKS table
CHECK_PEND2 column
Check constraint status
USETableCCT#
RDAREACCD#
NOUSETableNNN
RDAREANNN
Legend:
T: Sets table information (check constraint status) in all RDAREAs that store the table.
C: Sets the check pending status.
D: Sets table information (check constraint status) in the RDAREA subject to data loading.
N: Does not change the check pending status (the current status is maintained).
#
If the inner replica facility is used, the following generations are subject to check pending status setting:
  • If the -q option is specified, the generation specified in the -q option is subject to setting.
  • If the -q option is omitted, the current generation is subject to setting.

    Table 5-4 Range of check pending status settings for referential constraints

    Data loading execution conditionTable subject to data loading
    pd_check_pend operand value in system define-d option specUnit of exeReferenced tableReferencing table
    Check pending status of the referencing table related to referenced tableCheck pending status of referencing table
    Data dictionary tableTIRDAData dictionary tableTIRDA
    SQLTCSQLRCREFCSSQLTCSQLRCREFCS
    USESpec'dTableCCT#CCT#
    RDAREACCT#CCD#
    OmittedTableNNNCCT#
    RDAREANNNCCD#
    NOUSESpec'dTableNNNNNN
    RDAREANNNNNN
    OmittedTableNNNNNN
    RDAREANNNNNN
Legend:
T: Sets table information (check constraint status) in all RDAREAs that store the table.
C: Sets the check pending status.
D: Sets table information (check constraint status) in the RDAREA subject to data loading.
N: Does not change the check pending status (the current status is maintained).
pd_check_pend operand value in system define: pd_check_pending operand value in system definition
-d option spec: -d option specification
Spec'd: Specified
Unit of exe: Unit of execution
TIRDA: Table information in RDAREA
SQLTC: SQL_TABLES table CHECK_PEND column
SQLRC: SQL_REFERENTIAL_CONSTRAINS table CHECK_PEND column
REFCS: Referential constraint status
#
If the inner replica facility is used, the following generations are subject to check pending status setting:
  • If the -q option is specified, the generation specified in the -q option is subject to setting. However, if data loading is to be performed on a referenced table and the replica RDAREA storing the related referencing table is not in the generation specified in the -q option, the current RDAREA generation becomes subject to setting.
  • If the -q option is omitted, the current RDAREA generation is subject to setting.

(5) Example of whether or not check pending status can be set

This subsection describes whether or not check pending status can be set for tables T1 through T5 when the pending and ref_pending operands are specified in the constraint statement.

Description of the tables
T1: Table with the primary key (referenced table for T2)
T2: Table with the primary key and the foreign key that references T1's primary key (referencing table for T1 and referenced table for T3)
T3: Table with the foreign key that references T2's primary key (referencing table for T2)
T4: Table for which check constraints have been defined
T5: Table with no constraint definition

Table 5-5 shows the table's check pending status when data loading is performed on tables T1 through T5.

Table 5-5 Table's check pending status when data loading is performed

Table subject to data loading and whether or not there are constraint definitionsOperand specification in the constraint statementCheck pending status for table
Table nameReferential constraintCheck constraintpendref_ pendT1T2T3T4T5
Referenced tableReferencing table
T1YNNOmit'dOmit'd[Figure]P[Figure][Figure][Figure]
Spec'd[Figure]N[Figure][Figure][Figure]
Spec'dOmit'd[Figure]P[Figure][Figure][Figure]
Spec'd[Figure]N[Figure][Figure][Figure]
T2YYNOmit'dOmit'd[Figure]PP[Figure][Figure]
Spec'd[Figure]PN[Figure][Figure]
Spec'dOmit'd[Figure]NP[Figure][Figure]
Spec'd[Figure]NN[Figure][Figure]
T3NYNOmit'dOmit'd[Figure][Figure]P[Figure][Figure]
Spec'd[Figure][Figure]P[Figure][Figure]
Spec'dOmit'd[Figure][Figure]N[Figure][Figure]
Spec'd[Figure][Figure]N[Figure][Figure]
T4NNYOmit'dOmit'd[Figure][Figure][Figure]P[Figure]
Spec'd[Figure][Figure][Figure]P[Figure]
Spec'dOmit'd[Figure][Figure][Figure]N[Figure]
Spec'd[Figure][Figure][Figure]N[Figure]
T5NNNOmit'dOmit'd[Figure][Figure][Figure][Figure][Figure]
Spec'd[Figure][Figure][Figure][Figure][Figure]
Spec'dOmit'd[Figure][Figure][Figure][Figure][Figure]
Spec'd[Figure][Figure][Figure][Figure][Figure]
Legend:
pend: pending
ref_ pend: ref_pending
Y: Defined
N: Not defined
Omit'd: Omitted
Spec'd: Specified
P: Check pending status is set.
N: Check pending status is not changed (the current status is maintained).
[Figure]: Not applicable (pending and ref_pending operands are ignored).

(6) Timing of setting check pending status

The timing of setting check pending status for a target table subject to data loading and for referencing tables related to that target table depends on the data loading mode. Table 5-6 shows the relationship between the data loading mode and when check pending status is set.

Table 5-6 Check pending status setting timing

Data loading modeWhen check pending status is set
Creation modeBefore data deletion processing begins
Addition modeBefore data loading begins

If pdload rolls back due to an error after it has set the table's check pending status, the table is still placed in check pending status because setting of the check pending status has been completed (commit is completed).