13.18.3 Check pending status

If data integrity between tables can no longer be guaranteed due to execution of an SQL statement or of a utility, HiRDB restricts data manipulation in the referencing table. The status in which data manipulation is restricted due to loss of guaranteed data integrity is called check pending status. To place a referencing table in check pending status for the purpose of restricting data manipulation, you must either specify USE in the pd_check_pending operand or do not specify (omit) the operand. You can use the integrity check utility (pdconstck) to clear the check pending status of a table. You can also use the integrity check utility to forcibly place a table into check pending status.

If you have specified NOUSE in the pd_check_pending operand, data manipulation is not restricted even when data integrity between tables cannot be guaranteed. In this case, if you execute an SQL statement or a utility that nullifies the guarantee of data integrity, you can use the integrity check facility to forcibly place the table into check pending status, and then check data integrity.

For details about operations that cause loss of guaranteed data integrity, see 13.18.4 Data manipulation and integrity. For details about how to check data integrity, see 13.18.5 Procedure for checking table integrity.

Organization of this subsection
(1) Trigger for setting or clearing check pending status
(2) Managing check pending status
(3) Operations that are restricted for tables in check pending status
(4) Operations restricted for tables that are related to a table in check pending status
(5) When a partitioned table or the inner replica facility is used
(6) Notes on using check pending status

(1) Trigger for setting or clearing check pending status

In addition to the integrity check utility, you can also use the following utilities, commands, and SQL statements to place a referencing table into check pending status or to clear a check pending status that has been applied to a referencing table.

For details about utilities and commands, see the manual HiRDB Version 8 Command Reference. For details about SQL, see the manual HiRDB Version 8 SQL Reference.

(2) Managing check pending status

Check pending status is managed based on dictionary tables and on the table information of the RDAREAs in which the tables are stored. In dictionary tables, check pending status is managed for each table and constraint. In table information, check pending status is managed for each RDAREA if the table is a partitioned table, and for each table if the table is not a partitioned table.

Table 13-15 lists the storage locations of check pending status information and their contents.

Table 13-15 Storage locations of check pending status information and contents (referential constraint)

Storage locationStored information
Dictionary tableSQL_TABLES tableCHECK_PEND columnCheck pending status of referential constraint for each table
SQL_REFERENTIAL_CONSTRAINTS tableCHECK_PEND columnCheck pending status of referential constraint for each constraint
RDAREA table informationFor non-partitioned tableCheck pending status of referential constraint or check constraint for each table
For partitioned tableCheck pending status of referential constraint or check constraint for each RDAREA

(3) Operations that are restricted for tables in check pending status

Table 13-16 lists operations that are disabled for tables in check pending status. When a target table is accessed by a trigger action, restricted operations depend on the availability of SQL operations specified in the triggered SQL statement. If a target table is a view table, the restricted operations depend on the availability of operations on the base table that is the source of the view table.

Table 13-16 Availability of operations on tables in check pending status

Operation on check pending status tablesAvailability
Data manipulation SQLSELECT statementSearches the target tableY#1
Searches a list created from the target table
INSERT statementInserts data into the target table
UPDATE statementUpdates the target table
DELETE statementDeletes a row from the target table
ASSIGN LIST statementCreates a list from the target table
UtilityRebalancing utility (pdrbal)N
Database reorganization utility (pdrorg)ReorganizesY#2
Legend:
Y: The operation cannot be performed in certain cases.
N: The operation cannot be performed.
#1
The operation can be performed only when both of the following conditions are met:
  • The target table is a partitioned table, and the partitioning condition is key range partitioning or FIX hash partitioning.
  • The target RDAREA is not in check pending status.
#2
Reorganizing a table partitioned using flexible hash partitioning may not be possible. For details, see Rules and notes in the Database Reorganization Utility (pdrorg) chapter of the manual HiRDB Version 8 Command Reference.

(4) Operations restricted for tables that are related to a table in check pending status

In this example, tables have the following referential relationship; only tables T2 and T3 are in check pending status.

[Figure]

The following subsections explain operations restricted for each table when either table T2 or T3 or both tables are in check pending status.

(a) When only table T2 is in check pending status

Table 13-17 lists operations restricted for each table when only table T2 is in check pending status.

Table 13-17 Restricted operations when table T2 is in check pending status

Target tableRestricted operationContents
Table T1UPDATE (updates the target table)Restrictions depend on the referential constraint action specification defined in table T2.
  • If CASCADE is specified:
    These operations cannot be performed if the table information of the RDAREA that is the target of referential constraint action is in check pending status. However, update operations can be performed if the values are the same.
  • If RESTRICT is specified:
    These operations can be performed. Referencing table T2 is referenced to perform data integrity checking.
DELETE (deletes rows from the target table)
Table T2SELECT statement (searches the target table or a list created from the target table)These operations can be performed only when both of the following conditions are met:
  • The target table is a partitioned table and the partitioning condition is key range partitioning or FIX hash partitioning.
  • The target RDAREA is not in check pending status.
INSERT statement (inserts data into the target table)
UPDATE statement (updates the target table)
DELETE statement (deletes rows from the target table)
ASSIGN LIST statement (creates a list from the target table)
Rebalancing utility (pdrbal)This operation cannot be performed.
Reorganization by the database reorganization utility (pdrorg)Reorganization may not be possible for a table partitioned using flexible hash partitioning. For details, see Database Reorganization Utility (pdrorg) in the manual HiRDB Version 8 Command Reference.
Table T3There is no restricted operation. For INSERT and DELETE, referenced table T2 is referenced to perform data integrity checking.
(b) When only table T3 is in check pending status

Table 13-18 lists operations restricted for each table when only T3 is in check pending status.

Table 13-18 Restricted operations when table T3 is in check pending status

Target tableRestricted operationContents
Table T1UPDATE (updates the target table)When the referential constraint action defined for table T2 and T3 is CASCADE, these operations cannot be performed if the table information of the RDAREA that is the target of referential constraint action is in check pending status. However, update operations can be performed if the values are the same.
DELETE (deletes rows from the target table)
Table T2UPDATE (updates the target table)Restrictions depend on the referential constraint action specification defined for tables T2 and T3.
  • If CASCADE is specified:
    These operations cannot be performed if the table information of the RDAREA that is the target of referential constraint action is in check pending status. However, update operations can be performed if the values are the same.
  • If RESTRICT is specified:
    These operations can be performed. Referencing table T3 is referenced to perform data integrity checking.
DELETE (deletes rows from the target table)
Table T3SELECT statement (searches the target table or a list created from the target table)These operations can be performed only when both of the following conditions are met:
  • The target table is a partitioned table and the partitioning condition is key range partitioning or FIX hash partitioning.
  • The target RDAREA is not in check pending status.
INSERT statement (inserts data into the target table)
UPDATE statement (updates the target table)
DELETE statement (deletes rows from the target table)
ASSIGN LIST statement (creates a list from the target table)
Rebalancing utility (pdrbal)The operation cannot be performed.
Reorganization by the database reorganization utility (pdrorg)Reorganization may not be possible for a table partitioned using flexible hash partitioning. For details, see Database Reorganization Utility (pdrorg) in the manual HiRDB Version 8 Command Reference.
(c) When both tables T2 and T3 are in check pending status

Table 13-19 lists operations restricted for each table when tables T2 and T3 are in check pending status.

Table 13-19 Restricted operations when tables T2 and T3 are in check pending status

Target tableRestricted operationContents
Table T1UPDATE (updates the target table)If the referential constraint action defined for table T2 and T3 is CASCADE, these operations cannot be performed if the table information of the RDAREA that is the target of referential constraint action is in check pending status. However, update operations can be performed if the value are the same.
These operations can be performed if the referential constraint action specification defined for tables T2 and T3 is RESTRICT. Referencing table T2 is referenced to perform data integrity checking.
DELETE (deletes rows from the target table)
Table T2SELECT statement (searches the target table or a list created from the target table)These operations can be performed only when both of the following conditions are met:
  • The target table is a partitioned table and the partitioning condition is key range partitioning or FIX hash partitioning.
  • The target RDAREA is not in check pending status.
INSERT statement (inserts data into the target table)
UPDATE statement (updates the target table)
DELETE statement (deletes rows from the target table)
ASSIGN LIST statement (creates a list from the target table)
Rebalancing utility (pdrbal)This operation cannot be performed.
Reorganization by the database reorganization utility (pdrorg)Reorganization may not be possible for a table partitioned using flexible hash partitioning. For details, see Database Reorganization Utility (pdrorg) in the manual HiRDB Version 8 Command Reference.
Table T3SELECT statement (searches the target table or a list created from the target table)These operations can be performed only when both of the following conditions are met:
  • The target table is a partitioned table and the partitioning condition is key range partitioning or FIX hash partitioning.
  • The target RDAREA is not in check pending status.
INSERT statement (inserts data into the target table)
UPDATE statement (updates the target table)
DELETE statement (deletes rows from the target table)
ASSIGN LIST statement (creates a list from the target table)
Rebalancing utility (pdrbal)This operation cannot be performed.
Reorganization by the database reorganization utility (pdrorg)Reorganization may not be possible for a table partitioned using flexible hash partitioning. For details, see Database Reorganization Utility (pdrorg) in the manual HiRDB Version 8 Command Reference.

(5) When a partitioned table or the inner replica facility is used

Since the check pending status is managed for each RDAREA, if a partitioned table or the inner replica facility is used, and the table information in the RDAREA actually used is in check pending status, operation on the partitioned table or generation may be restricted. The following subsections explain these cases.

(a) For partitioned tables

Figure 13-34 shows an example wherein part of an RDAREA that stores data in a partitioned table is in check pending status.

Figure 13-34 Data manipulation availability when managing check pending status for each RDAREA in a partitioned table

[Figure]

Explanation:
When you execute a SELECT statement for partitioned table A, if data actually manipulated is in RDAREA 2 (whose table information is in check pending status), a SELECT statement error occurs. When manipulating data in RDAREAs 1 and 3, the SELECT statement can be executed normally.
Notes on partitioned table
If you specify USE in the pd_check_pending operand and re-initialize the RDAREA where referencing table data is partitioned and stored, use the integrity check utility to check the data integrity of each table.
(b) When the inner replica facility is used

Figure 13-35 shows an example in which the inner replica facility is used, and, in one of the generations of an RDAREA, a part of it is in check pending status.

Figure 13-35 Data manipulation availability when using the inner replica facility and managing check pending status by RDAREA

[Figure]

Explanation:
When manipulating data of generation 1 (the generation that includes the RDAREA whose table information is in check pending status), if the data that is actually being manipulated is in replica RDAREA 2-1, the SQL code results in an error.

(6) Notes on using check pending status