CURRENT TEMPORAL SYSTEM_TIME special register

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

When a query references a system-period temporal table and the value of the CURRENT TEMPORAL SYSTEM_TIME special register is not the null value, the following period specification is implicit: 

FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME

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 SYSTEM_TIME statement. If you change the value within a routine, that new value is not passed back to the invoking application.

When the value of the CURRENT TEMPORAL SYSTEM_TIME special register is not null and the SYSTIMESENSITIVE bind option is set to YES, you cannot explicitly specify FOR SYSTEM_TIME in a select-statement.

Examples

Example of a query that references a system-period temporal table
Assume the following conditions:
  • STT is a system-period temporal table, and POLICY_ID is a column of STT.
  • The value of the SYSTIMESENSITIVE bind option is YES.
  • The value of CURRENT TEMPORAL SYSTEM_TIME is not null.

Then, suppose that you issue the following query:

SELECT * FROM STT
WHERE POLICY_ID = 123;

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

SELECT * FROM STT
FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME
WHERE POLICY_ID = 123;
Example of a procedure that uses CURRENT TEMPORAL SYSTEM_TIME
Suppose that procedure MYPROC is defined as follows:
CREATE PROCEDURE MYPROC(OUT VAR1 VARCHAR(40), OUT VAR2 VARCHAR(40))
BEGIN
 SELECT CURRENT TEMPORAL SYSTEM_TIME INTO VAR1
 FROM SYSIBM.SYSDUMMY1;

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

 SELECT CURRENT TEMPORAL SYSTEM_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 SYSTEM_TIME = TIMESTAMP('2008-01-01') + 5 DAYS ;

CALL MYPROC(VAR1, VAR2);

SELECT CURRENT TEMPORAL SYSTEM_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 SYSTEM_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 SYSTEM_TIME value that is set during the CALL statement.
  • VAR3 has value '2008-01-06-00.00.00.000000000000', which is the CURRENT TEMPORAL SYSTEM_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 a system-period temporal table
Assume that IN_TRAY is a system-period temporal table that contains users and subject lines for notes in the inbox. The following query returns the user IDs and subject lines based on the state of the messages in IN_TRAY as of the date that is specified by the CURRENT TEMPORAL SYSTEM_TIME special register. 
SELECT SOURCE, SUBJECT
FROM IN_TRAY

If the special register is set to a non-null value, the previous statement is equivalent to the following statement: 

SELECT SOURCE, SUBJECT
FROM IN_TRAY
FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME