Start of change

SET CURRENT TEMPORAL BUSINESS_TIME

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

Invocation

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

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagramSETCURRENT TEMPORAL BUSINESS_TIME=NULLexpression

Description

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

Notes

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

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
End of change