13.2.1 Integrity check facility

The integrity check facility checks referential constraints or check constraints for integrity errors. If no row violates any constraints, the facility releases the check pending status.

Integrity checking on referential constraints
Based on the referential constraints, the facility checks to see if the foreign key of the target table has the same value as the primary key of the table referenced by the target table. If the checking result indicates that no row violates the constraints, the facility releases the check pending status. If there is a row that violates the constraints, the facility sets the check pending status. Note that the facility does not check rows whose foreign key value is the null value. Integrity checking on referential constraints may take time, depending on the size of the database. To determine the execution time, use the SQL statement presented in (2)(a) How to perform integrity checking on referential constraints.
Integrity checking on check constraints
The facility checks to see if row values are within the ranges of the check constraints in the check constraint definitions. If the checking results indicate that no row violates the constraints, the facility releases the check pending status. If any row violates a constraint, the facility sets the check pending status. Integrity checking on check constraints may take time, depending on the size of the database. To determine the execution time, use the SQL statement presented in (3)(a) How to perform integrity checking on check constraints.
Organization of this subsection
(1) Execution unit of the integrity check facility
(2) Integrity checking on referential constraints
(3) Integrity checking on check constraints
(4) Changing the check pending status based on the results of integrity checking
(5) Order of integrity checking and whether or not integrity checking is executed

(1) Execution unit of the integrity check facility

You can execute the integrity check facility in units of a table or a constraint, as explained below.

When the inner replica facility is used to perform processing by table, the integrity check facility can be executed in the following units:

(a) By table

The facility checks all of the referential and check constraints defined for the table that have been placed in check pending status. You specify the -t option to perform integrity checking on a table.

Integrity checking by table is applied in the following cases:

By all generations
For every referential constraint and check constraint that has been defined for the table and that has resulted in check pending status, integrity checking is performed on the original RDAREA and on each of its replica RDAREA generations that stores the table. The criteria are the same as for checking by table. You specify -q all to perform integrity checking on all generations.
By generation
For every referential constraint and check constraint that has been defined for the table and that has resulted in check pending status, integrity checking is performed only on the generation specified in the -q option. You specify the desired generation number in the -q option to perform integrity checking by generation.
Integrity checking by generation is applied when only the specific generation resulting in the check pending status is to be checked for violations, and when only its check pending status is to be released.
By the current RDAREA's generation
For every referential constraint and check constraint that has been defined for the table and that has resulted in check pending status, integrity checking is performed on the current RDAREA's generation. You omit the -q option to perform integrity checking by generation of the current RDAREA.
If a utility such as pdload is executed on the current RDAREA's generation and if the table results in check pending status, integrity checking by the current RDAREA's generation is applied in order to check only the current RDAREA's generation for violations, and then to release the check pending status.
(b) By constraint

In this case the facility checks only one constraint at a time for integrity errors, regardless of the check pending status. You specify the -c option to perform integrity checking by constraint. When the inner replica facility is used, integrity checking is performed on the original RDAREA that stores the table for which the constraint has been specified, as well as on all generations of its replica RDAREAs.

Integrity checking by constraint is applied in the following case:

(2) Integrity checking on referential constraints

(a) How to perform integrity checking on referential constraints

To perform integrity checking for referential constraints, you use the SQL statement shown below for each constraint to be checked. When the inner replica facility is used, integrity checking is performed on the applicable constraint in a generation.

Note:
Because this SQL statement must create a work table, a work table file for storing the work table is required in the single server or back-end server. For details about how to estimate the size of a work table file, see the manual HiRDB Version 9 Installation and Design Guide.
(b) Checking whether or not integrity has been maintained

The table below shows the relationship between the execution result (SQLCODE) of the SQL statement shown in (a) and whether integrity has been maintained.

Table 13-5 Relationship between SQL's execution result and whether or not integrity has been maintained (referential constraint)

SQLCODE (number of selection rows)Whether or not integrity has been maintained
100 (0)Yes
0 (1 or greater)No
OtherExecution error
(c) Output of key values resulting in constraint violations

After execution of the SQL statement described in (a), the key values resulting in constraint violations are output to the process results file. The key values resulting in constraint violations are output after duplicates have been eliminated. For details about the output format for the key values, see 13.5(2)(a) Processing results of integrity checking on referential constraints.

(d) Cancellation of integrity checking because of the maximum number of key value violations that can be output

If the number of row data items resulting in constraint violations exceeds the maximum output count for violation key values, the facility terminates integrity checking on that constraint (generation). You use the -w option to change the maximum number of violation key values that can be output.

(3) Integrity checking on check constraints

(a) How to perform integrity checking on check constraints

To perform integrity checking for check constraints, you use the SQL statement shown below for each constraint to be checked. When the inner replica facility is used, integrity checking is performed on the applicable constraint in a generation.

Note:
Because this SQL statement must create a work table, a work table file for storing the work table is required in the single server or back-end server. For details about how to estimate the size of a work table file, see the manual HiRDB Version 9 Installation and Design Guide.
#: DISTINCT must be omitted when the column definition specified in the search condition is the BLOB or BINARY type. In this case, no work table is created.
(b) Checking whether or not integrity has been maintained

The table below shows the relationship between the execution result (SQLCODE) of the SQL statement shown in (a) and whether integrity has been maintained.

Table 13-6 Relationship between SQL's execution result and whether or not integrity has been maintained (check constraint)

SQLCODE (number of selection rows)Whether or not integrity has been maintained
100 (0)Yes
0 (1 or more)No
OtherExecution error
(c) Output of key values resulting in constraint violation

After execution of the SQL statement described in (a), the key values resulting in constraint violations are output to the process results file. If the data types of the columns specified in the search conditions do not include BLOB or BINARY, the key values resulting in constraint violations are output after duplicates have been eliminated. If the columns include any that are of the BLOB or BINARY type, duplicates of key values resulting in constraint violations are not eliminated. For details about the output format for the key values, see 13.5(2)(b) Processing results of integrity checking on check constraints.

(d) Cancellation of integrity checking because of the maximum number of key value violations that can be output

If the number of row data items resulting in constraint violations exceeds the maximum output count for violation key values, the facility terminates integrity checking on that constraint (generation). You use the -w option to change the maximum number of violation key values that can be output.

(4) Changing the check pending status based on the results of integrity checking

pdconstck sets a table, constraint, or RDAREA in check pending status or non-check pending status on the basis of the results of integrity checking for each constraint (and when the inner replica facility is used, for each generation for each constraint). The change in the check pending status for an RDAREA depends on the unit of execution of pdconstck.

This subsection describes the change in check pending status for a constraint, table, and RDAREA. A change in check pending status for a table, constraint, or RDAREA occurs separately for check constraints and referential constraints.

Change in the check pending status for a constraint
The check pending status for constraints changes for each constraint (each generation) based on the results of integrity checking. The table below shows the change in the check pending status for a constraint.

Table 13-7 Change in the check pending status for a constraint

Inner replica facilityResult of integrity checking on a constraint#Constraint check pending status (setting)
Not usedIntegrity is maintainedNon-check pending status (NULL value)
Integrity is not maintainedCheck pending status ('C')
UsedIntegrity is maintained in all generationsNon-check pending status (NULL value)
Integrity is maintained in some of the generationsCheck pending status ('C')
#: The facility assumes that integrity is maintained for a constraint (generation) whose integrity was not checked.

Change in the check pending status for a table
The check pending status for a table changes based on each constraint's check pending status. The table below shows the change in the check pending status for a table.

Table 13-8 Change in the check pending status for a table

Constraint check pending statusTable check pending status (setting)
All constraints are in non-check pending statusNon-check pending status (NULL value)
At least one constraint is in check pending status (even if the others are in non-check pending status)Check pending status ('C')
Change in the check pending status for an RDAREA
The check pending status for an RDAREA changes for each constraint (each generation) based on the results of integrity checking. The table below shows the change in the check pending status for an RDAREA.

Table 13-9 Change in the check pending status for an RDAREA

Result of integrity checkingRDAREA check pending status (setting)
Integrity is maintained for all constraintsNon-check pending status (NULL value)
Integrity is not maintained for at least one constraint (even if it is maintained for the others)Check pending status ('C')
(a) Changes in the check pending status when integrity checking is performed by table

When integrity checking is performed by table, the check pending status of the table, constraints, or RDAREAs changes based on the results of integrity checking for each constraint.

Figure 13-1 Example of integrity checking by table shows an example of executing integrity checking by table on a table for which referential constraints have been defined. Table 13-10 Changes in the check pending status by table (example) shows the changes in the check pending status in this example.

Figure 13-1 Example of integrity checking by table

[Figure]

Legend:
T1, T2: Tables in which the primary key has been defined
T3: Table in which the foreign keys (REF1 and REF2) have been defined
CHK1, CHK2: Referential constraints
USR1 to USR4: User RDAREAs storing the table
REF1, REF2: Foreign keys that reference another table

Table 13-10 Changes in the check pending status by table (example)

Result of integrity checkingCheck pending status
Integrity of REF1Integrity of REF2Table check pending statusConstraint check pending statusTable storage RDAREA check pending status
REF1REF2USR1USR2
MaintainedMaintainedNNNNN
LostPNPPP
LostMaintainedPPNPP
LostPPPPP
Legend:
P: Check pending status
N: Non-check pending status

(b) Changes in the check pending status when integrity checking is performed on all generations

If integrity checking is performed on all generations when the inner replica facility has been used, the check pending status of the table, constraints, or RDAREAs changes based on the results of integrity checking for each constraint (or each generation).

Figure 13-2 Example of integrity checking by all generations shows an example of performing integrity checking on all generations when the inner replica facility has been used to create one generation of replica RDAREAs containing a table for which check constraints have been defined. Table 13-11 Changes in the check pending status by all generations (example) shows the changes in the check pending status in this example.

Figure 13-2 Example of integrity checking by all generations

[Figure]

Legend:
U1, U2: Original RDAREAs
U1G1, U2G1: Replica RDAREAs
CHK1, CHK2: Check constraints for table T1

Table 13-11 Changes in the check pending status by all generations (example)

Result of integrity checkingCheck pending status
Integrity of CHK1Integrity of CHK2Table check pend statusConstraint check pending statusRDAREA check pending status
GEN 0GEN 1GEN 0GEN 1CHK1CHK2U1U2U1G1U2G1
MntdMntdMntdMntdNNNNNNN
MntdMntdMntdLostPNPNNPP
MntdMntdLostMntdPNPPPNN
MntdMntdLostLostPNPPPPP
MntdLostMntdMntdPPNNNPP
MntdLostMntdLostPPPNNPP
MntdLostLostMntdPPPPPPP
MntdLostLostLostPPPPPPP
LostMntdMntdMntdPPNPPNN
LostMntdMntdLostPPPPPPP
LostMntdLostMntdPPPPPNN
LostMntdLostLostPPPPPPP
LostLostMntdMntdPPNPPPP
LostLostMntdLostPPPPPPP
LostLostLostMntdPPPPPPP
LostLostLostLostPPPPPPP
Legend:
GEN: Generation
Table check pend status: Table check pending status
Mntd: Maintained
P: Check pending status
N: Non-check pending status

(c) Changes in the check pending status when integrity checking is performed by generation

When the inner replica facility is used and integrity checking is performed by generation, the check pending status of the table, constraints, or RDAREAs changes based on the results of integrity checking on the specified generation for each constraint.

The table below shows the changes in the check pending status of a constraint and RDAREA based on the results of integrity checking by generation.

Table 13-12 Changes in the check pending status of a constraint and RDAREA based on the results of integrity checking by generation

Execution resultConstraint check pending statusRDAREA check pending status
Result of checking on the corresponding generationCheck pending status in other generations of the RDAREA
Integrity is maintained in all constraintsAll non-check pending statusAll constraints are placed in non-check pending status.The corresponding generation is placed in non-check pending status.
OtherNo changeThe corresponding generation is placed in non-check pending status.
OtherConstraint whose integrity is maintained--No changeThe corresponding generation is placed in check pending status.
Constraint whose integrity is lost--Placed in check pending status.The corresponding generation is placed in check pending status.
Legend:
--: Not applicable

The table below shows the changes in the check pending status when integrity checking by generation is performed on Generation 1 based on the example shown in Figure 13-2 Example of integrity checking by all generations.

Table 13-13 Changes in the check pending status by generation (example)

Result of integrity checkingRDAREA check pending statusCheck pending status
Table check pending statusConstraint check pending statusRDAREA check pending status
Integrity of CHK1Integrity of CHK2GEN 0CHK1CHK2U1G1U2G1U1U2
MntdMntdNon-check pending statusNNNNN(N)(N)
MntdMntdCheck pending status(P)(P)(P)NN(P)(P)
MntdLostNon-check pending statusP(P)PPP(N)(N)
MntdLostCheck pending statusP(P)PPP(P)(P)
LostMntdNon-check pending statusPP(P)PP(N)(N)
LostMntdCheck pending statusPP(P)PP(P)(P)
LostLostNon-check pending statusPPPPP(N)(N)
LostLostCheck pending statusPPPPP(P)(P)
Legend:
GEN: Generation
Mntd: Maintained
P: Check pending status
N: Non-check pending status
(P): No change (remains in check pending status)
(N): No change (remains in non-check pending status)

(d) Changes in the check pending status when integrity checking is performed by the current RDAREA's generation

When the inner replica facility is used and integrity checking is performed by the current RDAREA's generation, the check pending status of the table, constraints, or the specified generation of RDAREA changes based on the results of integrity checking on the specified generation for each constraint.

The changes in the check pending status when integrity checking is performed by the current RDAREA's generation are the same as in (c) Changes in the check pending status when integrity checking is performed by generation.

(e) Changes in the check pending status when integrity checking is performed by constraint

When integrity checking is performed by constraint, the check pending status of the table, the specified constraint, or the RDAREA changes based on the results of integrity checking on the specified constraint. The following describes the changes in the check pending status.

Changes in the check pending status (when the inner replica facility is not used)

When the inner replica facility is not used, the constraint's check pending status changes based on the results of integrity checking on the specified constraint.

The table below shows the changes in the check pending status when integrity checking by constraint is performed on constraint CHK1 based on the example shown in Figure 13-1 Example of integrity checking by table.

Table 13-14 Changes in the check pending status by constraint (example)

Result of integrity checkingConstraint check pending statusCheck pending status
Integrity of CHK1CHK2Table check pending statusConstraint check pending statusRDAREA check pending status
CHK1CHK2U1U2
MaintainedNon-check pending statusNN--NN
MaintainedCheck pending statusPN--PP
LostNon-check pending statusPP--PP
LostCheck pending statusPP--PP
Legend:
P: Check pending status
N: Non-check pending status
--: The status remains unchanged.

Changes in the check pending status (when the inner replica facility is used)
When the inner replica facility is used, the check pending status of a constraint, each generation of the RDAREA, and the table change based on the results of integrity checking performed on the specified constraint in each generation.
The table below shows the changes in the check pending status when integrity checking by constraint is performed on constraint CHK1 based on the example shown in Figure 13-2 Example of integrity checking by all generations.

Table 13-15 Changes in the check pending status by constraint when the inner replica facility is used (example)

Result of integrity checkingOther check pending statusCheck pending status
Table check pending statusConstraint check pending statusRDAREA check pending status
Integrity of CHK1 generation 0Integrity of CHK1 generation 1CHK2CHK1CHK2U1U2U1G1U2G1
MaintainedMaintainedNon-check pending statusNN--NNNN
MaintainedMaintainedCheck pending statusPN--PPPP
MaintainedLostNon-check pending statusPP--PPPP
MaintainedLostCheck pending statusPP--PPPP
LostMaintainedNon-check pending statusPP--PPPP
LostMaintainedCheck pending statusPP--PPPP
LostLostNon-check pending statusPP--PPPP
LostLostCheck pending statusPP--PPPP
Legend:
P: Check pending status
N: Non-check pending status
--: The status remains unchanged.

(5) Order of integrity checking and whether or not integrity checking is executed

(a) By table
Order of integrity checking
When integrity checking is performed by table, the order in which it is performed is as follows:
Order between referential and check constraints
If referential constraints and check constraints are both defined for the table specified in the -t option, the referential constraints (foreign keys) are checked first, and then the check constraints are checked.
Order within the same type of constraints
If multiple constraints of the same type are defined for the table specified in the -t option, integrity is checked in the alphanumeric order of the constraint names.
The table below shows an example of the order in which integrity checking by table is performed.

Table 13-16 Example of order in which integrity checking by table is performed

Constraint for tableOrder of integrity checking
Referential constraint (foreign key)Check constraintReferential constraint (foreign key)Check constraint
F1F2C1C2
MaintainedMaintained1.2.3.4.
Lost1.2.----
LostMaintained----1.2.
Lost--------
Legend:
F1, F2: Constraint names for referential constraints (foreign key)
C1, C2: Constraint names for check constraints
1. to 4.: Order in which constraint checking is performed
--: Not applicable

Order of integrity checking by generation and whether or not integrity checking is executed by generation when the inner replica facility is used
Integrity checking by table (by all generations) is performed on the original RDAREAs (generation number 0) and then on their replicas for each constraint in ascending order of the generation numbers (starting at 1).
Whether or not integrity checking is executed
Integrity checking by table is performed only on constraints resulting in check pending status. When the inner replica facility is used, integrity checking is performed only on the generations of a constraint that have resulted in check pending status. The table below shows the conditions for performing integrity checking and whether integrity checking is executed.

Table 13-17 Conditions for performing integrity checking and whether or not integrity checking is executed

Check pending statusWhether or not integrity checking is executedRemarks
Table check pending statusConstraint check pending statusTable storage RDAREA check pending status#1
Check pending status ('C')Check pending status ('C')Check pending statusYespdconstck determines that the status is normal (check pending status).
Non-check pending statusYespdconstck checks integrity because the data dictionary table is in check pending status although the RDAREA is in non-check pending status.
Non-check pending status (NULL value)#2Check pending statusNopdconstck determines that the status is normal (other constraints are in check pending status).
Non-check pending statusYespdconstck checks integrity because the data dictionary table is in check pending status although the RDAREA is in non-check pending status.
Non-check pending status (NULL value)Check pending status ('C')Check pending statusNopdconstck terminates with an error because the data dictionary table is invalid.
Non-check pending statusNo
Non-check pending status (NULL value)#2Check pending statusYespdconstck checks integrity because the RDAREA is in check pending status.
Non-check pending statusNopdconstck determines that the status is normal (non-check pending status).
#1
A partitioned table is placed in check pending status or non-check pending status depending on the table information in each RDAREA that stores the table. The table below shows the check pending status based on the table information in each table storage RDAREA.
When the inner replica facility is used, the status (check pending status or non-check pending status) depends on the generation. Table 13-19 Check pending status based on the table information in each table storage RDAREA in the generation shows the check pending status based on the table information in each table storage RDAREA in the generation.

Table 13-18 Check pending status based on the table information in each table storage RDAREA

ConditionTable information in table storage RDAREA
All storage RDAREAs are in check pending statusCheck pending status
Some storage RDAREAs are in check pending status and some are in non-check pending statusCheck pending status
All storage RDAREAs are in non-check pending statusNon-check pending status

Table 13-19 Check pending status based on the table information in each table storage RDAREA in the generation

ConditionTable information in table storage RDAREA
All storage RDAREAs subject to processing in the generation are in check pending statusCheck pending status
Storage RDAREA subject to processing in the generation is in non-check pending statusCheck pending status
All storage RDAREAs subject to processing in the generation are in non-check pending statusNon-check pending status
#2
If consistency is lost between the table's check pending status and the constraint's check pending status, pdconstck terminates with an error. The table below shows the pdconstck processing when the data dictionary table is in check pending status.

Table 13-20 pdconstck processing when the data dictionary table is in check pending status

Check pending statuspdconstck processing
Table check pending statusConstraint check pending status
Check pending statusSome constraints were placed in check pending statusResumes processing
All constraints are in non-check pending statusTerminates with an error
Non-check pending statusSome constraints were placed in check pending statusTerminates with an error
All constraints are in non-check pending statusResumes processing
(b) By constraint
Order of integrity checking
When integrity checking is performed by constraint, there is no specific order in which integrity checking is performed, because only constraints are checked. However, if the inner replica facility is used, integrity is checked in the following order:
Checking order by generation when the inner replica facility is used
Integrity checking by constraint is performed on the original RDAREAs (generation number 0) and then on their replicas in ascending order of the generation numbers (starting at 1).
Whether or not integrity checking is executed
Integrity checking by constraint is performed in the above order, regardless of the table's check pending status.