“As time goes by…..” DB2 for z/OS continues to enhance support for temporal data. Since the availability of the initial temporal support with DB2 10 for z/OS, the functionality has been extended several times. The subject of this article is one of the temporal extensions, the temporal special registers, introduced in DB2 11 for z/OS. The temporal special registers allow you to easily “travel in time” as you run a query, and they are especially useful with packaged applications that need to be run against the state of the business as of a particular point in time such as the end of the quarter or the end of last year.
The DB2 temporal support provides an efficient and cost effective way for organizations to track changes to their ever increasing amounts of data over time, address application requirements, as well as auditing and compliance requirements for data retention. With the DB2 temporal support you can write queries against a temporal table for a point in time in the past, the current time, or some future point in time that will be valid for an application.
First let’s revisit the basics of the DB2 for z/OS temporal support. Every row in a temporal table is conceptually associated with a pair of datetime values representing a period of time for the row. One datetime value records the begin time, i.e., the time when the row was inserted into the table or becomes “valid”, and the other datetime value records the end time, i.e., the time when the row was either updated or deleted, or becomes “invalid”. A temporal table contains a pair of datetime columns that define a “period”. DB2 for z/OS supports the following types of temporal tables:
- system-period temporal table. DB2 automatically keeps historical versions of rows in a separate table referred to as the history table. A system-period temporal table includes a SYSTEM_TIME period allowing you to "go back in time" and query any past state of your data. System-period temporal tables record occurrences of database changes, such as updating the value of an insurance rate in a row for an insurance policy.
- application-period temporal table. Users or applications maintain a “valid” period of time for each row of data. The values for the BUSINESS_TIME period of an application-period temporal table record when the information in the row is deemed valid in the real world. An application-period temporal table allows you to go back or forward in time to query the state of your data at a particular point in time. Application-period temporal tables record the occurrence of business events, such as the discounting of artificial Christmas trees between December 24th and December 30th of 2015.
- bitemporal table. A table that is both a system-period temporal table and an application-period temporal table is a bitemporal table. For illustration purposes, assume that an insurance company has defined a POLICY table that contains a row for each insurance policy. The following query returns each row for insurance policies for account 54667:
SELECT Customer, Policy_type, Rate
FROM POLICY
WHERE AccountId=‘54667’
Let’s first assume that the POLICY table is defined as a system-period temporal table. This means that the table includes two datetime columns for the SYSTEM_TIME period which can be referenced in a temporal query. To formulate a temporal query using the initial temporal support, include a “period specification” to indicate the desired time period to retrieve the data for. With this approach, a period specification (such as an AS OF clause) is included in each query that you want to explicitly indicate the time period that you are interested in. For example, using the POLICY table, the following query that insurance policy information for account 54667 at a historical point, the end of 2015.
SELECT Customer, Policy_type, Rate
FROM POLICY
FOR SYSTEM_TIME AS OF TIMESTAMP ‘2015-12-31 00:00:00'’
WHERE AccountId=‘54667’
What if you don’t want to modify the queries in your existing applications, but you still want to use a “filter” each time you run the queries to control the time period? Customers indicated that they would like to get data at certain point of time from temporal tables without changing SQL query and data change statements. Consider a scenario where the customer has an application that they would like to run against the state of the business as of today, the same application against the state of the business as of the end of last quarter, the same application against the state of the business as of last year, etc. With the initial temporal support this requires changing the application and adding period specifications to each SQL statement. This is cumbersome, and may not even be possible in case of packaged applications. DB2 11 for z/OS delivers an extension to simplify this called “temporal special registers”.
Two temporal special registers are supported, CURRENT TEMPORAL SYSTEM_TIME for system-period temporal tables, and CURRENT TEMPORAL BUSINESS_TIME for application-period temporal tables. These temporal special registers affect SQL statements that directly reference temporal tables, as well as those that indirectly reference temporal tables by referencing views in both static and dynamic SQL statements.
When a temporal special register is in effect, DB2 generates an implicit period specification for the associated period in SQL statements that directly or indirectly reference one of the corresponding type of temporal tables. By setting a temporal special register to a specific point in time, data can be retrieved from temporal tables without needing to add explicit period specifications or predicates to existing SQL statements. In this way DB2 provides a simple “time machine” that requires fewer application changes for temporal support. For example, when the CURRENT TEMPORAL SYSTEM_TIME special register is in effect, DB2 generates an implicit period specification for the SYSTEM_TIME period, and this is applied to queries that directly or indirectly reference system-period temporal tables. Likewise, when the CURRENT TEMPORAL BUSINESS_TIME special register is in effect, DB2 generates an implicit period specification for the BUSINESS_TIME period, and this is applied to queries that directly or indirectly reference application-period temporal tables.
What does it mean for a temporal special register to be “in effect”? It means that the special register contains a non-null value, and the corresponding bind (or routine) option is set to YES to make applications sensitive to the setting of the temporal special register. These new options are introduced to isolate existing applications from inadvertent effects when the new special registers are set to non-null values. I’ll tell you more about these options later in this article.
Back to our example with the POLICY table of insurance records. Assume that the bind (or routine) option for sensitivity to the temporal special register for system-period temporal tables is set to YES. By setting the CURRENT TEMPORAL SYSTEM_TIME special register to the end of 2015, we can reuse the original unmodified query, to obtain the results as the end of December 2015. For example:
-- Set the temporal special register to indicate that temporal queries on
-- system-period temporal tables will be run as of the end of 2015.
SET CURRENT TEMPORAL SYSTEM_TIME = TIMESTAMP ‘2015-12-31 00:00:00’
-- Reuse the original unmodified query without an explicit time specification
SELECT Customer, Policy_type, Rate
FROM POLICY
WHERE AccountId=‘54667’
Since the CURRENT TEMPORAL SYSTEM_TIME special register is not null, DB2 implicitly adds a period specification that references the value in the special register, resulting in the following query:
SELECT Customer, Policy_type, Rate
FROM POLICY
FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME -- refers to special register
WHERE AccountId=‘54667’
This query with the explicit reference to the temporal register ( implicitly added by DB2) is logically equivalent to the following user-written query, which I showed you earlier. The following query explicitly includes the time specification as part of the query, rather than implicitly using the temporal special register.
SELECT Customer, Policy_type, Rate
FROM POLICY
FOR SYSTEM_TIME AS OF TIMESTAMP ‘2015-12-31 00:00:00'’ -- explicit time specification
WHERE AccountId=‘54667’
Just as we demonstrated use of the CURRENT TEMPORAL SYSTEM_TIME special register to simplify our temporal queries against system-period temporal tables, we can use the CURRENT TEMPORAL BUSINESS_TIME special register to simplify temporal queries against application-period temporal tables.
Let’s continue using the POLICY table that contains insurance records for different accounts. Assume that the POLICY table is actually a bitemporal table, meaning that it is both a system-period temporal table as well as an application-period temporal table. This also means that the POLICY table contains a BUSINESS_TIME period. Similar to the illustration using one of the temporal special registers with the POLICY table as a system-period temporal table, we can also use the other temporal special register with the POLICY table as an application-period temporal table.
The following query retrieves insurance premium information for account 54667 for the next renewal of the insurance policy which will occur on July 31, 2016. The period specification requests the information as of a future point in time, which is at the end of July 2016.
SELECT Customer, Policy_type, Rate
FROM POLICY
FOR BUSINESS_TIME AS OF TIMESTAMP ‘2016-07-31 00:00:00'’
WHERE AccountId=‘54667’
We can simplify this query by removing the period specification on BUSINESS_TIME, and instead set the CURRENT TEMPORAL BUSINESS_TIME special register to have DB2 generate an implicit period specification for the BUSINESS_TIME period. Assume that the bind (or routine) option for sensitivity to the temporal special register for application-period temporal tables is set to YES. We can set the CURRENT TEMPORAL BUSINESS_TIME special register to July 31, 2016, and run a simpler query, without a period specification:
-- Set the temporal special register to indicate that temporal queries on
-- application-period temporal tables will be run as of the end of July 2016.
SET CURRENT TEMPORAL BUSINESS_TIME = TIMESTAMP ‘2016-07-31 00:00:00’
SELECT Customer, Policy_type, Rate
FROM POLICY -- The simpler query
WHERE AccountId=‘54667’
Since the CURRENT TEMPORAL BUSINESS_TIME special register is not null, DB2 implicitly adds a period specification that references the value in the special register, resulting in the following query:
SELECT Customer, Policy_type, Rate
FROM POLICY
FOR BUSINESS_TIME AS OF CURRENT TEMPORAL BUSINESS_TIME
WHERE AccountId=‘54667’
I haven’t quite told you the whole story about the special registers. I mentioned that they are only “in effect” when they contain a non-null value and when the corresponding bind (or routine) option is set to YES. When these two conditions are met, applications are sensitive to the setting of the temporal special register. These new options are introduced to shield existing applications from the effects of the new temporal special registers. Let’s get more specific.
Bind options
DB2 11 for z/OS supports two new bind options, SYSTIMESENSITIVE and BUSTIMESENSITIVE. These bind options can be specified on the BIND and REBIND commands, and the valid values are YES and NO. The default value is YES.
SYSTIMESENSITIVE determines whether implicit period specifications are generated for system-period temporal tables if the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value. A YES value means that DB2 generates the following implicit period specification for system-period temporal tables if the special register contains a non-null value:
FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME
BUSTIMESENSITIVE determines whether implicit period specifications are generated for application-period temporal tables if the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value. A YES value means that DB2 generates the following implicit period specification for application-period temporal tables if the special register contains a non-null value:
FOR BUSINESS_TIME AS OF CURRENT TEMPORAL BUSINESS_TIME
Note that when a temporal special register is set to a non-null value and the corresponding bind (or routine) option is set to YES, that you cannot explicitly specify a period specification for that type of temporal table. For example, when the value of the CURRENT TEMPORAL SYSTEM_TIME special register is not null and the SYSTIMESENSITIVE bind option is set to YES, you cannot explicitly specify FOR SYSTEM_TIME in a query.
SQL Routine options
Similar to the bind options just discussed, DB2 11 for z/OS supports two new options for SQL routines, SYSTEM_TIME SENSITIVE and BUSINESS_TIME SENSITIVE. These routine options can be specified on the CREATE and ALTER statements for SQL compiled scalar functions and SQL native procedures. The valid values are YES and NO, and the default value is YES.
Data change statements
There are special considerations for data change statements when the temporal special registers are in effect. When a temporal special register is in effect it impacts the selection of rows for the data change operation for which the target is a temporal table that corresponds to the temporal special register.
When the CURRENT TEMPORAL SYSTEM_TIME special register is in effect, data change statements are allowed, as long as the target of the statement is not directly or indirectly a system-period temporal table.
When the CURRENT TEMPORAL BUSINESS_TIME special register is in effect, data change statements are allowed, including when the target of the statement is directly or indirectly an application-period temporal table. Assume that bus_start is the row begin column and bus_end is the row end column of the BUSINESS_TIME period in the application-period temporal table. DB2 implicitly adds the following predicates to the data change statement.
WHERE bus_start <= CURRENT TEMPORAL BUSINESS_TIME AND
bus_end > CURRENT TEMPORAL BUSINESS_TIME
To illustrate the use of the CURRENT TEMPORAL BUSINESS_TIME special register with a data change statement we’ll use the POLICY table again. We want to process a 10% rate increase for existing policies as of August 1, 2016. First we set the CURRENT TEMPORAL BUSINESS_TIME special register to desired effective date:
SET CURRENT TEMPORAL BUSINESS_TIME = TIMESTAMP ‘2016-08-01 00:00:00’
The non-null value in the temporal special register causes the following update statement to only increase the rate for policies effective on August 1, 2016:
UPDATE POLICY
SET Rate = Rate * 1.10 -- increase the premium by 10%
Since the CURRENT TEMPORAL BUSINESS_TIME special register is not null, DB2 implicitly adds additional predicates that reference the value in the special register, resulting in the following update statement:
UPDATE POLICY
SET Rate = Rate * 1.10 -- increase the premium by 10%
WHERE bus_start <= CURRENT TEMPORAL BUSINESS_TIME AND
bus_end > CURRENT TEMPORAL BUSINESS_TIME
This update statement only increases the rate by 10% for those policies effective on August 1, 2016.
The temporal special registers are one of several enhancements that have been introduced for the DB2 for z/OS temporal support that began with V10. Using the temporal special registers, available beginning with V11, can minimize the impact on existing applications of introducing temporal support.