SET CURRENT TEMPORAL BUSINESS_TIME statement

The SET CURRENT TEMPORAL BUSINESS_TIME statement changes the value of the CURRENT TEMPORAL BUSINESS_TIME special register.

Invocation for SET CURRENT TEMPORAL BUSINESS_TIME

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization for SET CURRENT TEMPORAL BUSINESS_TIME

None required.

Syntax for SET CURRENT TEMPORAL BUSINESS_TIME

Read syntax diagramSkip visual syntax diagramSETCURRENT TEMPORAL BUSINESS_TIME=NULLexpression

Description for SET CURRENT TEMPORAL BUSINESS_TIME

NULL
Specifies the null value.
expression
Specifies an expression that returns the null value or the value of one of the following built-in data types:
  • Timestamp
  • Character string
  • Graphic string

If the expression is a character or graphic string, it must meet the following requirements:

  • It must not be a CLOB or DBCLOB.
  • The value of the expression must be a valid character-string or graphic-string representation of a timestamp.
  • The result of the expression must be castable to TIMESTAMP(12).

expression can contain any of the following supported operands:

  • Constant
  • Special register
  • Variable (host variable, SQL parameter, SQL variable, or global variable)
  • Scalar function whose arguments are supported operands
  • CAST specification where the cast operand is a supported operand
  • Expression that uses arithmetic operators and operands

For more information, see:

Notes for SET CURRENT TEMPORAL BUSINESS_TIME

Transactions
The SET CURRENT TEMPORAL BUSINESS_TIME statement is not a committable operation. The ROLLBACK statement has no effect on CURRENT TEMPORAL BUSINESS_TIME.
Effects on other special registers
The setting of the CURRENT TEMPORAL BUSINESS_TIME special register does not affect other special registers, such as the CURRENT DATE and CURRENT TIMESTAMP special registers.

Examples for SET CURRENT TEMPORAL BUSINESS_TIME

Example of setting the special register to a valid value
Both of the following statements set the CURRENT TEMPORAL BUSINESS_TIME special register to '2008-01-06-00.00.00.000000000000'.
SET CURRENT TEMPORAL BUSINESS_TIME = TIMESTAMP('2008-01-01') + 5 DAYS ;
SET CURRENT TEMPORAL BUSINESS_TIME = '2008-01-06-00.00.00.000000000000';
Example of how setting the special register affects subsequent SQL statements
In the following example, the first statement sets the CURRENT TEMPORAL BUSINESS_TIME special register to last month. Assume that table att1 is an application-period temporal table. The setting of the CURRENT TEMPORAL BUSINESS_TIME special register affects the update of att1.
SET CURRENT TEMPORAL BUSINESS_TIME = CURRENT TIMESTAMP - 1 MONTH
UPDATE att1 SET c1 = 5 WHERE pk = 100
Assume that the att1 table has columns bt_begin and bt_end to indicate the beginning and end of the BUSINESS_TIME period. In this example, Db2 interprets the UPDATE statement as follows:
UPDATE att1 SET c1 = 5 WHERE pk = 100
AND bt_begin <= CURRENT TEMPORAL BUSINESS_TIME
AND bt_end > CURRENT TEMPORAL BUSINESS_TIME
Example of setting the special register so that it does not affect subsequent SQL statements
The following statement sets the CURRENT TEMPORAL BUSINESS_TIME special register to the null value. Subsequent SQL statements that reference application-period temporal tables are not affected by the CURRENT TEMPORAL BUSINESS_TIME special register.
SET CURRENT TEMPORAL BUSINESS_TIME = NULL