Setting the application time for a session

Setting the application time in the CURRENT TEMPORAL BUSINESS_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 an application-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, or if you are simulating future events, AS OF some future date, 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 BUSINESS_TIME special register to set the date at the session level.

Setting the CURRENT TEMPORAL BUSINESS_TIME special register does not affect regular tables. Only queries on temporal tables with a BUSINESS_TIME period enabled (application-period temporal tables and bitemporal tables) use the time set in the special register. There is also no affect on DDL statements.

Note: When the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value, data modification statements like INSERT, UPDATE, DELETE, and MERGE against application-period temporal tables are supported. This behavior differs from the CURRENT TEMPORAL SYSTEM_TIME special register which blocks data modification statements against system-period temporal table and bitemporal tables.

The BIND command contains the BUSTIMESENSITIVE option and is set to YES by default. The BUSTIMESENSITIVE option determines whether references to application-period temporal tables and bitemporal tables in both static SQL statements and dynamic SQL statements in a package are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register. The bind option can be set to YES or NO. For more information about the type of objects that CURRENT TEMPORAL BUSINESS_TIME also affects, see CURRENT TEMPORAL BUSINESS_TIME special register. If queries in the application are meant to access only the current version of data, set BUSTIMESENSITIVE 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 returns data as of that date. The following examples request information from the result tables in the Deleting data from an application-period temporal table topic.

Set the special register to a non-null value and query data as of that date.
For example:
```
SET CURRENT TEMPORAL BUSINESS_TIME = '2008-01-01';
SELECT * FROM policy_info;
```

Set the special register to a time and reference an application-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 BUSINESS_TIME = '2008-01-01';
SELECT * FROM view1;
SELECT * FROM view2;

Set the special register to a past date and issue a query that contains a time period specification.

For example:

SET CURRENT TEMPORAL BUSINESS_TIME = CURRENT DATE - 1 YEAR;
SELECT * FROM policy_info FOR BUSINESS_TIME AS OF '2008-01-01';

Results

The policy_info table is as follows:

Table 1. Data in the application-period temporal table (policy_info) after the DELETE statement
policy_id	coverage	bus_start	bus_end
A123	12000	2008-01-01	2008-06-01
A123	14000	2008-06-01	2008-06-15
A123	16000	2008-08-15	2009-01-01
B345	18000	2008-03-01	2009-01-01
C567	25000	2008-01-01	2009-01-01

The request for data as of 2008-01-01 queries the policy_info table. The query is implicitly rewritten to:

SELECT * FROM policy_info FOR BUSINESS_TIME AS OF '2008-01-01';

The query returns:

A123, 12000, 2008-01-01, 2008-06-01
C567, 25000, 2008-01-01, 2009-01-01

The query on view1 is implicitly rewritten to:

SELECT * FROM view1 FOR BUSINESS_TIME AS OF CURRENT TEMPORAL BUSINESS_TIME;

and then to:

SELECT policy_id, coverage FROM policy_info 
   FOR BUSINESS_TIME AS OF '2008-01-01';

The query returns:

A123, 12000
C567, 25000

The query on view2 involves a view on a regular table that references an application-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 BUSINESS_TIME AS OF CURRENT TEMPORAL BUSINESS_TIME;

and then to:

SELECT * FROM regular_table WHERE col1 in (SELECT coverage FROM policy_info 
   FOR BUSINESS_TIME AS OF '2008-01-01');

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.