DB2 10.5 for Linux, UNIX, and Windows

Setting the system time for a session

Setting the system time with the CURRENT TEMPORAL SYSTEM_TIME special register can reduce or eliminate the changes required when running an application against different points in time.

About this task

When you have an application that you want to run against a system-period temporal table to query the state of your business for a number of different dates, you can set the date in a special register. If you need to query your data as of today, as of the end of the last quarter, and as of the same date from last year, it might not be possible to change the application and add AS OF specifications to each SQL statement. This restriction is likely the case when you are using packaged applications. To address such scenarios, you can use the CURRENT TEMPORAL SYSTEM_TIME special register to set the date or timestamp at the session level.

Setting the CURRENT TEMPORAL SYSTEM_TIME special register does not affect regular tables. Only queries on temporal tables with versioning enabled (system-period temporal tables and bitemporal tables) use the time set in the special register. There is also no affect on DDL statements. The special register does not apply to any scans run for referential integrity processing. .

Important: When the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value, data modification statements like INSERT, UPDATE, and DELETE against system-period temporal tables are blocked. If the special register was set to some time in the past, for example five years ago, then allowing data modification operations might result in changes to your historical data records. Utilities like IMPORT and LOAD are also blocked against system-period temporal tables when the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value.

The BIND command contains the SYSTIMESENSITIVE option and is set to YES by default. The SYSTIMESENSITIVE option indicates whether references to system-period temporal tables in static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. For more information about the type of objects that CURRENT TEMPORAL SYSTEM_TIME also affects, see CURRENT TEMPORAL SYSTEM_TIME special register. If queries in the application are meant to access only the current version of data, set SYSTIMESENSITIVE to NO to optimize the static queries. For SQL procedures, use the SET_ROUTINE_OPTS procedure to set the bind-like options, called query compiler variables.

Procedure

When this special register is set to a non-null value, applications that issue a query will return data as of that date or timestamp. The following examples request information from the result tables in the Deleting data from a system-period temporal table topic.

Set the special register to the current timestamp and query data from one year ago. Assuming a current timestamp of 2011-05-17-14.45.31.434235000000:
```
SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP - 1 YEAR;
SELECT policy_id, coverage FROM policy_info;
```

Set the special register to a timestamp and reference a system-period temporal table in view definitions.

CREATE VIEW view1 AS SELECT policy_id, coverage FROM policy_info;
CREATE VIEW view2 AS SELECT * FROM regular_table 
   WHERE col1 IN (SELECT coverage FROM policy_info);
SET CURRENT TEMPORAL SYSTEM_TIME = TIMESTAMP '2011-02-28-09.10.12.649592000000';
SELECT * FROM view1;
SELECT * FROM view2;

Set the special register to the current timestamp and issue a query that contains a time period specification. Assuming a current timestamp of 2011-05-17-14.45.31.434235000000:
```
SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP - 1 YEAR;
SELECT * 
  FROM policy_info FOR SYSTEM_TIME AS OF '2011-02-28-09.10.12.649592000000';
```

Results

The policy_info table and its associated history table are as follows:

Table 1. Data in the system-period temporal table (policy_info) after the DELETE statement
policy_id	coverage	sys_start	sys_end	ts_id
A123	12000	2010-01-31-22.31.33.495925000000	9999-12-30-00.00.00.000000000000	2010-01-31-22.31.33.495925000000
C567	25000	2011-02-28-09.10.12.649592000000	9999-12-30-00.00.00.000000000000	2011-02-28-09.10.12.649592000000

Table 2. History table (hist_policy_info) after delete
policy_id	coverage	sys_start	sys_end	ts_id
C567	20000	2010-01-31-22.31.33.495925000000	2011-02-28-09.10.12.649592000000	2010-01-31-22.31.33.495925000000
B345	18000	2010-01-31-22.31.33.495925000000	2011-09-01-12.18.22.959254000000	2010-01-31-22.31.33.495925000000

The request for data from one year ago queries the policy_info table as of 2010-05-17-14.45.31.434235000000. The query is implicitly rewritten to:
```
SELECT policy_id, coverage FROM policy_info 
   FOR SYSTEM_TIME AS OF TIMESTAMP '2010-05-17-14.45.31.434235000000';
```
The SELECT queries both the policy_info and the hist_policy_info tables and returns:
```
A123, 12000
C567, 20000
B345, 18000
```
The query on view1 is implicitly rewritten to:
```
SELECT * FROM view1 FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME;
```
The query is then rewritten to:
```
SELECT policy_id, coverage FROM policy_info 
   FOR SYSTEM_TIME AS OF TIMESTAMP '2011-02-28-09.10.12.649592000000';
```
The SELECT queries both the policy_info and the hist_policy_info tables and returns:
```
A123, 12000
C567, 25000
B345, 18000
```
The query on view2 involves a view on a regular table that references a system-period temporal table, causing an implicit relationship between a regular table and the special register. The query is implicitly rewritten to:
```
SELECT * FROM view2 FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME;
```
The query is then rewritten to:
```
SELECT * FROM regular_table WHERE col1 in (SELECT coverage FROM policy_info 
   FOR SYSTEM_TIME AS OF TIMESTAMP '2011-02-28-09.10.12.649592000000');
```
The SELECT returns rows where col1 values match values in coverage.
An error is returned because there are multiple time period specifications. The special register was set to a non-null value and the query also specified a time.