8.9.15 constraint statement (specification of check pending status)

The constraint statement specifies settings for check pending status.

When a table for which check constraints or referential constraints have been defined is reorganized, the check pending status is not changed because constraint integrity can be guaranteed except when a row in the referenced table that is being referenced by a referencing table is deleted by a UOC. The check pending status is set only when a referenced table is reorganized by a UOC.

If reloading is performed, such as reloading using an old unload data file or reloading into a separate table, constraint integrity may no longer be guaranteed. Therefore, the applicable table and related referencing tables are placed in check pending status.

You can also specify a control statement that will prevent the check pending status from being changed (set). For details about check pending status, see the manual HiRDB Version 9 Installation and Design Guide.

Criteria
If integrity can be guaranteed, it is recommended that you specify the constraint statement (to not change (set) the table's check pending status). However, if any of the following conditions is applicable, it is recommended that you omit the constraint statement in order to maintain constraint integrity:
  • Reloading using an old unload data file
    If data in a referencing table or referenced table is updated after it was unloaded, and reloading is performed using the unload data file that existed before the table was updated, data integrity may no longer be guaranteed between the referenced table and the referencing table because table data that existed before updating is being used.
  • Reloading to another table or a table whose constraint definition has been changed
    When reloading is performed on a different table from the one used for unloading, data whose integrity cannot be guaranteed may be reloaded, because pdrorg does not perform data integrity checking.
  • Reorganizing involving row deletion by a UOC
    When a UOC is used to reorganize a referenced table and to delete row data, integrity may be lost.
Rules
  1. The constraint statement can be specified when reorganization (-k rorg) or reloading (-k reld) is performed, and when the value of the pd_check_pending operand in the system definition is USE.
    If the value of the pd_check_pending operand in the system definition is NOUSE or if another facility (such as unloading (-k unld)) is used, this control statement is ignored if it is specified, in which case the table's check pending status is not changed.
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 reorganization or reloading is to be performed on a referencing table or a table for which check constraints have been defined, the table's check pending status is not to be changed (set). For details about the default value that is assumed when this operand is omitted, see (4) Range of check pending status settings.

(b) ref_pending=no

Specifies that when reorganization or reloading 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 (set). For details about the default value that is assumed when this operand is omitted, see (4) Range of check pending status settings.

(3) Notes

  1. For notes about setting related referencing tables in check pending status when reorganization or reloading 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 reorganization or reloading is being 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. The table is placed in check pending status even when the reload count is 0 and constraint integrity can be guaranteed.
  4. If reorganization involving the setting of check pending status results in an error and reorganization is re-executed, the table is placed in check pending status again.
  5. When check pending status is set, the related resources are locked. Once the 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

The table below shows the pdrorg execution conditions and whether the check pending status is set.

Table 8-24 pdrorg execution conditions and whether or not the check pending status is set

Execution conditionWhether or not check pending status is set
pd_check_pending operand value in the system definitionExe facilityOperand specification in the constraint statementUse of UOCTarget table
Ref'd tableRef'ing tableTable with check constraints defined
USEReorgpending=noYesYesNCNCNC
NoNCNCNC
NoYesNCNCNC
NoNCNCNC
ref_pending=noYesYesNCNCNC
NoNCNCNC
NoYesSNCNC
NoNCNCNC
Reloadpending=noYes--NCNCNC
No--NCSS
ref_pending=noYes--NCNCNC
No--SNCNC
Other------NCNCNC
Other--------NCNCNC
Legend:
Exe facility: Executing facility
Ref'd table: Referenced table
Ref'ing table: Referencing table
Reorg: Reorganization
S: Check pending status is set.
NC: Check pending status is not changed (current status is maintained).
--: Not applicable

Table 8-25 Range of check pending status settings for check constraints shows the range of check pending status settings for check constraints during reorganization or reloading. Table 8-26 Range of check pending status settings for referential constraints shows the range of check pending status settings for referential constraints.

Table 8-25 Range of check pending status settings for check constraints

Unit of executionRange of check pending status settings for check constraints
Data dictionary tableTable information in RDAREA
SQL_TABLES table
CHECK_PEND2 column
SQL_CHECKS table
CHECK_PEND2 column
Check constraint status
TableCCT#
RDAREACCR#
ServerCCA#
Legend:
C: Sets the check pending status.
T: Sets table information (check constraint status) in all RDAREAs that store the table.
R: Sets table information (check constraint status) in the RDAREA that was specified in the -r option.
A: Sets table information (check constraint status) in all RDAREAs that store tables in the back-end servers in all unload statements specified.
#: 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 8-26 Range of check pending status settings for referential constraints

    Unit of executionTarget table
    Referenced tableReferencing table
    Check pending status of the referencing table related to referenced tableCheck pending status of referencing table
    Data dictionary tableTIRDAData dictionary tableTIRDA
    SQLTCSQLRCREFCSSQLTCSQLRCREFCS
    TableCCT#CCT#
    RDAREACCT#CCR#
    SchemaCCT#CCT#
    ServerCCT#CCA#
Legend:
TIRDA: Table information in RDAREA
SQLTC: SQL_TABLES table CHECK_PEND column
SQLRC: SQL_REFERENTIAL_CONSTRAINTS table CHECK_PEND column
REFCS: Referential constraint status
C: Sets the check pending status.
T: Sets table information (check constraint status) in all RDAREAs that store the table.
R: Sets table information (check constraint status) in the RDAREA that was specified in the -r option.
A: Sets table information (check constraint status) in all RDAREAs that store tables in the back-end servers in all unload statements specified.
Note:
If a UOC is used, the check pending status is set.
#: 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 reorganization or reloading 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 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

The table below shows the table's check pending status when reloading is performed on tables T1 through T5.

Table 8-27 Table's check pending status when reloading is performed

Table subject to reloading 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--P------
Spec'd--N------
Spec'dOmit'd--P------
Spec'd--N------
T2YYNOmit'dOmit'd--PP----
Spec'd--PN----
Spec'dOmit'd--NP----
Spec'd--NN----
T3NYNOmit'dOmit'd----P----
Spec'd----P----
Spec'dOmit'd----N----
Spec'd----N----
T4NNYOmit'dOmit'd------P--
Spec'd------P--
Spec'dOmit'd------N--
Spec'd------N--
T5NNNOmit'dOmit'd----------
Spec'd----------
Spec'dOmit'd----------
Spec'd----------
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).
--: Not applicable (pending and ref_pending operands are ignored).

(6) Timing of setting check pending status

The target table subject to reorganization or reloading and the referencing tables related to that target table are placed in check pending status before reorganization or reloading is executed. If pdrorg 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).

If reorganization or reloading is performed for each schema, each target table is placed in check pending status before reorganization or reloading is executed. If the process that sets the check pending status on the table results in an error, pdrorg terminates with an error. Therefore, all tables processed before the occurrence of the error are placed in check pending status, but unprocessed tables are not placed in check pending status.