Temporal Support of Db2 for z/OS – Part III

Temporal RI

By Xiaohong Fu, Db2 for z/OS Development, Silicon Valley Lab, IBM

Background and Motivation

What is referential integrity (RI)?

Referential integrity (RI) is the state in which all values of all foreign keys are valid. Both traditional RI and temporal RI are relationships between pairs of tables, in which the business rules are represented. As early as 1988, Db2 for z/OS (hereafter referred to as "Db2") introduced RI support (V2R1) – the traditional one. Traditional RI constraint requires every row in a child table with non-null values of foreign key to have one corresponding row in a parent table with matching key. In Db2 V8, informational RI (with NOT ENFORCED keyword) was added to benefit certain query execution. At Db2 12 GA time, temporal RI became available.

What is temporal RI?

Compared to traditional RI, temporal RI is an even stronger condition that can be enforced between two tables with business time periods. For every row in a child table, one or more corresponding rows in a parent table exist with matching key and the business time period(s) of the parent row(s) completely cover the business time period of the child row without gaps. For example, an administration system may need to ensure that every employee at every point in time during his/her employment belongs to a department that actually exists at that point.

A temporal referential constraint is satisfied if:

• every non-null value of the foreign key matches a value of the parent key, and
• the application period value in the child table is contained in the application period in the parent table that is represented by the union of the application periods of one or more contiguous matching rows in the parent table.

Examples of cases that meet the criteria for a temporal referential constraint:

The business time period may be the same in the parent and child tables.

 The business time period in the parent table starts before and ends after the period in the child table.

The business time period in the parent starts at the same point but ends after the period in the child table.

The business time period in the parent starts before, but ends at the same point as the period in the child table.

Unlike a normal referential constraint, for a temporal referential constraint, it is possible that the business time period of the child table will be contained in the time period represented by multiple contiguous rows of the parent. The business time period of multiple rows in the parent table together can logically cover the period in the child table, without any “gaps”. In this case, the business time period values of the parent rows must be “contiguous”. As shown below, the contiguous parent periods and the child period may overlap totally or partially (overlap partially without any edge met, overlap partially with left edge met, or overlap partially with right edge met).

It is also possible that the business time period of multiple rows in the child table may be contained in the business time period of a single row in the parent table.

Why temporal RI?

Temporal RI support is a common requirement of Db2 temporal customers. Today, although temporal table features have already been built in some database management systems (DBMS), there is no known other DBMS that includes integrated mechanism to support temporal RI. Instead, DBMS applications have to build their own logic to define and enforce the business rules that involve temporal RI. For application-enforced temporal RI, triggers and/or stored procedures are usually used to handle the referential integrity, which lead to more application complexity and place a burden on application developers.

Compared to the application-enforced temporal RI, Db2 temporal RI has following advantages:

• Declarative temporal RI is defined using SQL statements and stored in database definition, which centralizes business rules and temporal RI documentation. While business rules are changed, only the temporal RI constraints need to be redefined at table level.
• By moving business rules from application code to declarative and centralized temporal RI constraints, the complexity of Db2 applications and programming errors are eliminated, and the maintenance overhead on customer side is minimized.
• Although Db2-enforced temporal RI checking adds extra cost to data change operations against temporal tables compared to those without temporal RI constraints, the overall performance gain is still significant. In addition, it requires specific index to be defined on the temporal foreign key, which greatly improves performance.
• The feature includes deferred temporal RI checking, which is difficult and challenging, sometimes even infeasible, for DBMS applications and database triggers.
• Db2 temporal RI is extended to both LOAD and CHECK DATA utilities. It is also compatible with existing REPORT RECOVERY utility, which will list objects with referential relationships for point-in-time recovery. Thus, both the parent and child tables with a temporal RI relationship can be recovered all together to the same point-in-time.

Description

Db2 temporal RI support applies to business time period and applications using Db2 application-period temporal table (ATT). The business time period can be either inclusive-exclusive (existing support since Db2 10) or inclusive-inclusive (Db2 12) period. The difference is in the interpretation of the end value of the period. For an inclusive-exclusive period the end value is not considered to be part of the period. For an inclusive-inclusive period the end value is considered to be part of the period. However, Db2 temporal RI requires that both the parent and child ATT must have same semantics.

To enforce temporal RI, i.e., the valid-time period of each child row is contained in the valid-time period of parent row (or of a set of contiguous rows without gap), Db2 performs no gap checking against parent table as needed. How a gap is detected depends on the semantic for the business time periods.

• Inclusive-exclusive model: A gap does not exist if the end of the business time period of one row equals the begin value of the business time period of another row.
• Inclusive-inclusive model: A gap does not exist if the end of the business time period of one row plus 1 increment equals the begin value of the business time period of another matching row. The increment depends on the data type of the business time period.
  • DATE: the increment is one day.
  • TIMESTAMP(6): the increment is one microsecond.

When executing the update statement on parent table, Db2 makes a determination as to whether to defer temporal referential integrity checking. When it is determined that temporal referential integrity checking is to be deferred, old key images are saved in work file, and deferred temporal referential constraint checking is done at commit time to avoid unnecessary early abortion of the data manipulation statement.

Definition of temporal RI

To support temporal RI on business time, the first step is to define the constraint using data definition language (DDL). New syntax fragment PERIOD BUSINESS_TIME is added to CREATE TABLE and ALTER TABLE DDL statements. To make temporal RI meaningful, both the parent and child table must be application-period temporal table (ATT) with business time period. For example:

For a temporal referential constraint, the parent table must have a unique index defined with the BUSINESS_TIME WITHOUT OVERLAPS clause. The index enforces that the values for the rest of the specified keys are unique with respect to any period of time. Additionally, a non-unique index must be defined on the child table with the BUSINESS_TIME WITH OVERLAPS clause, or an equivalent index that explicitly references the columns of the business time period (i.e., the end of the index key includes the end column followed by the begin column of the business time period, both in ascending order). In other words, Db2 requires index on primary key (or unique key alternatively) of the parent table and index on foreign key of the child table respectively. The last two columns of the primary key and foreign key indexes must be the end and begin columns of the business time period. 

• Index on parent table

   CREATE UNIQUE INDEX dept_primary_key_ix

   ON dept(DNo, BUSINESS_TIME WITHOUT OVERLAPS);

• Index on child table

   CREATE INDEX emp_foreign_key_ix

   ON emp (EDept, BUSINESS_TIME WITH OVERLAPS);

To define temporal RI, besides the required indexes as shown above, following requirements and restrictions apply:

• Self-referencing is not allowed within a temporal RI relationship, i.e., an ATT cannot be a dependent of itself in a temporal RI relationship.
• Referential cycle is not allowed as long as a temporal RI relationship is involved in the cycle.
• For temporal referential constraint, the delete rule must be RESTRICT. Delete rules NO ACTION/CASCADE/SET NULL are restricted.
• The parent and child tables must have same semantics, i.e., it is not allowed that one table is with inclusive-exclusive period but the other is with inclusive-inclusive period.

Enforcement of Temporal RI

Db2 enforces that a temporal referential constraint is satisfied in the following situations:

• An insert operation where the target table is an application-period temporal table, and the table is a child in a temporal referential constraint.
• An update operation where the target table is an application-period temporal table, the table is a child in a temporal referential constraint, and the value of the foreign key is changed. The update can also be a temporal update with FOR PORTION OF BUSINESS_TIME FROM value1 TO value2 period clause, which means that the update only applies to row values for the portion of the business time period in the row that is specified by the period clause.

• An update operation where the target table is an application-period temporal table, the table is a parent in a temporal referential constraint, and a parent key value is changed.
• A delete operation that results in deleting a row of an application-period temporal table, and the table is a parent in a temporal referential constraint.
• The LOAD utility, specified with the ENFORCE CONSTRAINTS option, is invoked to load data into an application-period temporal table, and the table is a child in a temporal referential constraint.
• The CHECK DATA utility, specified with the SCOPE option, is run against a table space that contains an application-period temporal table, and the table is a child in a temporal referential constraint.

Example

Schema definition

 Initial data in parent and child table

INSERT into child

The first insert violates temporal RI since the child period is longer (no parent periods can completely cover it without any gaps).

UPDATE the parent and child table

Both updates below violate temporal RI. The first update against parent table makes the period shorter, but there is a child record dependent on it. The second update against child table makes the period longer without a parent record containing the extended period.

Depending on the business time periods of the records and the period clause specified in the update statement, there may be row splitting during temporal update against child table, i.e., Db2 may implicitly insert 1 or 2 rows for the periods outside of the specified portion of business time. During this row splitting, temporal RI is enforced on both the update part and the insert part:

Temporal UPDATE against the parent table is restricted (see example below).

DELETE from the parent table

Temporal DELETE against the parent table is restricted. The regular delete in following example violates temporal RI since there is a child record dependent on it, as a result, Db2 fails the data change operation with SQL error.