Temporal Support of Db2 for z/OS – Part II

Temporal Logical Transaction

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

Temporal Logical Transaction

Background and Motivation

Transactions are omnipresent in today’s database systems, providing data integrity even in highly concurrent environments. As one of the most powerful engine in the world, IBM Db2 provides availability, reliability and security at low cost per transaction. Since Version 10, Db2 system-period data versioning uses the physical transaction time. A physical transaction groups multiple SQL statements as one unit of temporal work so that they are either all committed (applied to the database) or all rolled back (undone from the database).  When multiple changes are made to a row of a system-period temporal table (STT) within the same physical transaction scope, the intermediate changes are not reflected in the history table.

Besides physical transaction time, there are also business requirements and market value to support system-period data versioning using a logical transaction time. Several Db2 for z/OS customers have expressed a requirement to allow an application to manage ‘temporal logical transactions'. For example, there is long running batch job, and the customers want to control the scope of temporal transactions: they want the same begin or end value and consolidated history generation across commits.

With the temporal logical transaction support of Db2 12, Db2 allows an application to specify and manage the scope of temporal work easily and concisely. Both physical and temporal logical transactions can happen sequentially and/or concurrently without violating the integrity of the data. In addition, the database user will be able to control the begin column and end column value by specifying the temporal logical transaction start time. What the application needs to do is just tell the Db2 the expected logical transaction starting point and time, and Db2 will take care of everything else. It is compatible with temporal query processing and with great performance, because the major logic is on Db2 engine side.

Description

The new idea is to define a logical scope for temporal transactions (see Figure 1). This is to meet the different business needs and different scenarios about physical transactions and temporal logical units of work mentioned earlier. In the example of Figure 1, there are three physical transactions, determined by commit and rollback, with time t1, t2, and t3 respectively. In contrast, associated with the settings of a built-in global variable SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME (TLTT), there are three temporal logical transactions with starting time t01, t02, and t03.

Figure 1. System-period Data Versioning with Temporal Logical Transactions

a. Introduction of SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME

A built-in global variable TLTT is introduced to allow for fine grained control of the temporal unit of work. This global variable is of data type TIMESTAMP(12) with a default value NULL. An authorization to read the built-in global variable is granted to public. The authorization rules for built-in global variables are consistent with the rules of user-defined global variables. With appropriate write authority, a Db2 user can set this variable to different time values in order to define different logical transaction scopes.

When the global variable contains a non-null value, a temporal logical transaction scope is in effect, i.e., Db2 will no longer get a new timestamp from system clock at transaction start and after commit for begin column (current table) and end column (history table). An application is subsequently responsible for setting the values of the global variable to control the scope of temporal logical transaction. The value, excluding the last 3 digits, will be assigned to the begin and end columns. This global variable has no impact on transaction-start-ID column. If the transaction-start-ID column is defined as not null, it still contains the original start time of the physical transaction that impacts the row.

b. Data change operations against STT during logical transactions

When a temporal logical transaction is in effect:

• During inserts against an STT, a begin column value for the current table is generated based on the value of TLTT.

• During updates against an STT, a begin column value for the current table is generated based on the value of TLTT.
• During updates and deletes against an STT, an end column value for the history table is generated based on the value of TLTT.
• During deletes against an STT with extra row generation (ON DELETE ADD EXTRA ROW clause was specified in the definition of STT), begin and end column values for the extra record of the history table are generated based on the value of TLTT.

The insert operation against an STT can be a “direct” insert or an insert activated by triggers. The update and delete operations against an STT can be a “direct” update or delete, an update or delete activated by triggers, or an update or delete triggered as cascading referential actions.

c. Temporal logical transaction management for conflicts

A database is highly concurrent system, and there is always a chance of update conflicts. The temporal logical transaction mechanism allows interleaving operations in a controlled manner. Db2 ensures data integrity by blocking any data change operations that will cause a history row to have a begin value greater than the end value. During the execution of data change operations on a system-period temporal table, Db2 will not populate a history row if the begin value is equal to the end value. This is to ensure that the intermediate changes are not captured in the history table within same temporal logical transaction. Such management applies to all temporal transactions, regardless of whether they are concurrent transactions or not. Please see the Examples section for different cases.

d. Support temporal logical transactions when a system time period includes a time zone

The TLTT global variable is defined as TIMESTAMP(12) but is applicable to temporal system time with or without time zone. When it is set to a valid non-null value, Db2 derives internally a value of TIMESTAMP(12) WITH TIME ZONE to be used for begin and end columns that are defined with time zone.  The conversion between timestamp without time zone values and timestamp with time zone values is based on the CURRENT TIME ZONE special register.

For example, suppose CURRENT TIME ZONE is -08:00, and the global variable has value '2014-11-14-08.05.01.123456789000': the converted value '2014-11-14-16.05.01.123456789000+00:00' is used to populate affected begin and end columns that are defined as TIMESTAMP(12) WITH TIME ZONE.

e. Control over physical and temporal logical transactions

In reality, both physical and logical transactions can occur within each other, and this may or may not be expected. Another built-in global variable, SYSIBM.TEMPORAL_LOGICAL_TRANSACTIONS (TLT), is introduced. This system global variable can be used to help Db2 users to “intentionally” enable the temporal logical transaction feature as needed. Authorization to read this built-in global variable is granted to public. It is another layer of fencing the setting time of the TLTT built-in global variable.

TLT is of data type SMALLINT, and the value can be either 0 or 1:

• value 0 -- multiple temporal logical transaction scopes are disallowed within a single physical transaction scope. This is the default.
• value 1 -- multiple temporal logical transaction scopes are allowed within a single physical transaction scope.

f. Examples

f.1 Common setup for all examples

Schema

Initial Data

Suppose the built-in global variable SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME is NULL, and the start time of following INSERT transaction is “2013-11-25-11.15.00.000000000000”. For ease of reading, the fractional seconds are not shown in all examples.

INSERT INTO policy VALUES (‘C882’, ‘PPO’,10);

POLICY table contains following initial data:

 POLICY_HIST table is empty:

The following sections will make use of these tables in examples.

f.2 Examples using SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME

This section presents several examples that use TLTT to control the scope of temporal logical transactions. If there is a conflict, the examples show the conflict in history table (shaded, won't appear, an error will be issued to fail the operation that would have caused a conflict).

• Case ONE - Single application: single temporal logical transaction with multiple physical transactions
• Case TWO - Single application: multiple temporal logical transactions within a single physical transaction
• Case THREE - Single application: multiple temporal logical transactions within a single physical transaction (with conflict)
• Case FOUR - Two concurrent applications: multiple temporal logical transactions competing with each other
• Case FIVE - Two concurrent applications: multiple temporal logical transactions competing with each other (with conflict)
• Case SIX (Scenario A & B) - Two concurrent applications: temporal logical transactions competing with physical transaction (with conflict)

Case ONE - Single Application

In the example below, a single temporal logical transaction makes two updates to the same row. Only one history row is generated from the first updae, and that row reflects the state of the row before any changes were made by the temporal logical transaction. The second update does not cause any creation of history row, and no error either.

Example 1. Single Temporal Logical Transaction with Multiple Physical Transactions

Case TWO - Single Application

In following example (Example 2), one physical transaction contains two temporal logical transactions. The update of each temporal logical transaction generates a historical row, and the value of the sys_end column represents when the row was added to the history table (i.e., the start time of each temporal logical transaction). In contrast, the values of trans_id column in policy and its history table identify the start time of the physical transactions.

Example 2. Multiple Temporal Logical Transactions within a Single Physical Transaction

Case THREE - Single Application

Here is an example of temporal logical transaction management for conflicts (Example 3). Similar as Example 2, two temporal logical transactions are contained in a single physical transaction. The update of the first temporal logical transaction generates a historical row, and the value of the sys_end column represents when the row was added to the history table (i.e., the start time of the first temporal logical transaction). However, the update of the second temporal logical transaction is blocked by Db2 because the update operation would cause a history row to have a begin value greater than the end value. As a result, no historical row is generated in the second temporal logical transaction scope.

Example 3. Multiple Temporal Logical Transactions within a Single Physical Transaction

 (with conflict)

Case FOUR – Two Concurrent Applications

Multiple temporal logical transactions can execute concurrently (Example 4). Following is an example without any conflict. The updates in the temporal logical transactions of application 1 and 2 succeed and generate a historical row in history table respectively.

Example 4. Multiple Temporal Logical Transactions Competing with Each Other

Case FIVE – Two Concurrent Applications

Multiple temporal logical transactions can execute concurrently (Example 5). Based on the start time of the physical transactions of application 1 and 2, the update of application 1 happens first and succeeds without error. The begin value of current version of the updated row is the start time of the temporal logical transaction on the left. When the update of application 2 happens, the current row would end with the temporal logical transaction time on the right. However, that is an error case (sys_start > sys_end). The update on the right side fails with no creation of history row in history table. The current table stays unchanged too.

Example 5. Multiple Temporal Logical Transactions Competing with Each Other

(with conflict)

Case SIX (Scenario A) – Two Concurrent Applications

The scenario in Example 6A below shows that the update in the temporal logical transaction of application 1 (on the left) happens before the update in the physical transaction of application 2. After this update, the value of the begin column in current table represents the start time of the temporal logical transaction, and the value of the transaction-start-ID column shows the physical transaction start time of application 1. The first update (of application 1) execute successfully then the second update (of application 2) execute. Regarding the potential history row generated for the second update, the begin value is the start time of the temporal logical transaction (as a result of the first update), and the end value is the start time of the physical transaction (of application 2). This is an error case (sys_end < sys_start), hence the second update fails and is rolled back. There is no creation of historical row for the second update.

Example 6A. Temporal Logical Transaction Competing with Physical Transaction

(with conflict)

Case SIX (Scenario B) – Two Concurrent Applications

In following example (Example 6B), conflicting update is introduced in the temporal logical transaction scope of application 1 (on the left). That specific update would cause a history row to have a begin value greater than the end value. To ensure data integrity, Db2 issues error and fails the update operation.

Example 6B. Temporal Logical Transaction Competing with Physical Transaction

(with conflict)