Improvements to querying temporal tables

In Db2 11, you can retrieve data from temporal tables for different points in time without modifying the SQL statement. Instead of changing the query, you can now use special registers to specify different points in time and run the same query.

The two new special registers are CURRENT TEMPORAL BUSINESS_TIME and CURRENT TEMPORAL SYSTEM_TIME. Both of these special registers set timestamp values to be used in queries against temporal tables. Use the CURRENT TEMPORAL BUSINESS_TIME for queries against application-period temporal tables. Use CURRENT TEMPORAL SYSTEM_TIME for queries against system-period temporal tables. When you set one of these special registers, Db2 returns rows as if the query included one of the following period specification clauses:

  • FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME
  • FOR BUSINESS_TIME AS OF CURRENT TEMPORAL BUSINESS TIME

In DB2® 10, if you wanted to retrieve data from a temporal table for a different point time, you had to modify the query.

Begin general-use programming interface information.

For example, assume the following scenario: you have three system-period temporal tables, STT1, STT2 and STT3, and you have an application that contains the following SQL statement:

SELECT UDF_1(STT1.C1) FROM STT1,STT2

The user-defined function UDF_1 consists of the following SQL statement:

SELECT * FROM STT3

Today is 2012-03-26-17.44.49.000000, and the application wants to find the result from last week.

In DB2 10, you had to take the following steps:

  1. Modify the SQL statement as follows:
    SELECT UDF_(STT1.C1) FROM
    STT1 FOR SYSTEM_TIME AS OF  ‘2012-03-19-17.44.49’,
    STT2 FOR SYSTEM_TIME AS OF  ‘2012-03-19-17.44.49’
  2. Modify the SQL in UDF_1 as follows:
    SELECT * FROM
    STT3 FOR SYSTEM_TIME AS OF  ‘2012-03-19-17.44.49’
  3. Precompile, compile, and bind both the application and UDF_1.
In Db2 11, set the CURRENT TEMPORAL SYSTEM_TIME special register before you call the application:
SET CURRENT TEMPORAL SYSTEM_TIME = ‘2012-03-19-17.44.49’
You do not have to change the application or UDF_1. Alternatively, if you want the active data, set the special register to NULL, which is the default value for the special register.
End general-use programming interface information.