12.19.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 12.19.4 Data manipulation and integrity. For details about how to check data integrity, see 12.19.5 Procedure for checking table integrity.

Organization of this subsection
(1) 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 is used
(6) Notes on using check pending status

(1) Setting or clearing check pending status

You can also decide whether to set a referencing table to check pending status or clear its check pending status using the following utilities, commands and SQL statements, in addition to the integrity check utility.

For details about utilities and commands, see the manual HiRDB Version 9 Command Reference. For details about SQL, see the manual HiRDB Version 9 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.

The following table lists and describes the storage locations of check pending status information items.

Table 12-16 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 unpartitioned 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

The table below lists operations that are no longer available for tables once they enter 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 12-17 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 9 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

The following table lists operations that are restricted for particular tables when table T2 alone is in check pending status.

Table 12-18 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 9 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

The following table lists operations that are restricted for particular tables when table T3 alone is in check pending status.

Table 12-19 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 9 Command Reference.
(c) When both tables T2 and T3 are in check pending status

The following table lists operations that are restricted for particular tables when both table T2 and table T3 are in check pending status.

Table 12-20 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 9 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 9 Command Reference.

(5) When a partitioned table is used

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

The following figure shows an example of when some RDAREAs that store data in a partitioned table are in check pending status.

Figure 12-36 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.

(6) Notes on using check pending status