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.
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:
- 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’ - Modify the SQL in UDF_1 as follows:
SELECT * FROM STT3 FOR SYSTEM_TIME AS OF ‘2012-03-19-17.44.49’ - Precompile, compile, and bind both the application and UDF_1.
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.