CURRENT TEMPORAL BUSINESS_TIME special register

The CURRENT TEMPORAL BUSINESS_TIME special register specifies a TIMESTAMP(12) value that is used in the default BUSINESS_TIME period specification for references to application-period temporal tables.

When a query references an application-period temporal table and the value of the CURRENT TEMPORAL BUSINESS_TIME special register is not the null value, the query is affected as follows:

If the columns of a BUSINESS_TIME period are defined as TIMESTAMP, the following period specification is implicit:
```
FOR BUSINESS_TIME AS OF CURRENT TEMPORAL BUSINESS_TIME 
```
If the columns of a BUSINESS_TIME period are defined as DATE, the following period specification is implicit: :
```
FOR BUSINESS_TIME AS OF CAST(CURRENT TEMPORAL BUSINESS_TIME AS DATE)
```

The initial value of the special register depends on the context as follows:

If the special register is in a trigger, the initial value is inherited from the invoking application.
If the special register is in a user-defined function or procedure that is defined with the INHERIT SPECIAL REGISTERS option, the initial value is inherited from the invoking application.
If the special register is in a user-defined function or procedure that is defined with the DEFAULT SPECIAL REGISTERS option, the initial value is the null value.
In other contexts, the initial value of the special register is the null value.

You can change the value of the special register by using the SET CURRENT TEMPORAL BUSINESS_TIME statement. If you change the value within a routine, that new value is not passed back to the invoking application.

Examples

Example of a query that references an application-period temporal table

Assume the following conditions:

ATT is an application-period temporal table and POLICY_ID is a column in ATT.
The value of the BUSTIMESENSITIVE bind option is YES.
The value of CURRENT TEMPORAL BUSINESS_TIME is not null.

Then, suppose that you issue the following query:

SELECT * FROM ATT
WHERE POLICY_ID = 123;

Db2 generates an implicit BUSINESS_TIME period specification for the query as follows:

SELECT * FROM ATT
FOR BUSINESS_TIME AS OF CURRENT TEMPORAL BUSINESS_TIME
WHERE POLICY_ID = 123;

Example of a procedure that uses CURRENT TEMPORAL BUSINESS_TIME

Suppose that procedure MYPROC is defined as follows:

CREATE PROCEDURE MYPROC(OUT VAR1 VARCHAR(40), OUT VAR2 VARCHAR(40))
BEGIN
 SELECT CURRENT TEMPORAL BUSINESS_TIME INTO VAR1
 FROM SYSIBM.SYSDUMMY1;

 SET CURRENT TEMPORAL BUSINESS_TIME = TIMESTAMP('2011-01-01') + 5 DAYS ;

 SELECT CURRENT TEMPORAL BUSINESS_TIME INTO VAR2
 FROM SYSIBM.SYSDUMMY1;
END!

Suppose that the application defines string variables VAR1, VAR2, and VAR3 and contains the following SQL statements:

SET CURRENT TEMPORAL BUSINESS_TIME = TIMESTAMP('2008-01-01') + 5 DAYS ;

CALL MYPROC(VAR1, VAR2);

SELECT CURRENT TEMPORAL BUSINESS_TIME INTO VAR3
FROM SYSIBM.SYSDUMMY1;

After the execution of the SQL statements, the variables have the following values:

VAR1 has value '2008-01-06-00.00.00.000000000000', which is the CURRENT TEMPORAL BUSINESS_TIME value that is set before the CALL statement invoked the procedure.
VAR2 has value '2011-01-06-00.00.00.000000000000', which is the CURRENT TEMPORAL BUSINESS_TIME value that is set during the CALL statement.
VAR3 has value '2008-01-06-00.00.00.000000000000', which is the CURRENT TEMPORAL BUSINESS_TIME value that is set before the CALL statement. The changes of the register value inside the procedure have no affect on the invoking application.

Example of a query that references CURRENT TEMPORAL BUSINESS_TIME

Assume that IN_TRAY is an application-period temporal table that contains users and subject lines for notes in the inbox. The following query returns the user ID and subject line for notes in the IN_TRAY table that were sent on the date that the CURRENT TEMPORAL BUSINESS_TIME special register is set to.

SELECT SOURCE, SUBJECT
FROM IN_TRAY
WHERE DATE (CURRENT TEMPORAL BUSINESS_TIME) = DATE (RECEIVED)