TEMPORAL_LOGICAL_TRANSACTION_TIME built-in global variable
The TEMPORAL_LOGICAL_TRANSACTION_TIME global variable contains the value that is assigned to the row-begin column of a system-period temporal table, or to the end column in a history table during an insert, update, or delete operation.
- It is updatable, with values maintained by the user or the system.
- The type is TIMESTAMP(12) WITHOUT TIME ZONE.
- The schema is SYSIBM.
- The scope of this global variable is session.
FL 505 This global variable value is ignored for updates to temporal-enabled Db2 catalog tables and the associated history tables.
The global variable can be set to the following values:
- NULL
- Specifies that temporal logical transactions are not in use. NULL is the default value.
Db2 ensures the uniqueness of the generated values for row-begin columns in system-period temporal tables, and end columns in history tables across transactions, assuming that temporal logical transactions have not been used.
If multiple rows are inserted or updated in system-period temporal tables within a single SQL unit of work, the values for the row-begin columns are the same for all of the rows and are unique from the values that are generated for the columns by another transaction. If a conflicting unit of work is updating the same row in the system-period temporal table, and the row to be inserted into the associated history table would have an end timestamp value greater than the begin timestamp value, an error is returned.
If the begin timestamp value and end timestamp value for the row in the history table are the same, a row is not inserted into the history table.
- non-null value
-
Specifies a timestamp value for the start of a new temporal logical transaction.
For any specified value, the assigned value is composed of a TIMESTAMP(9) value followed by 000 or three digits that indicate a data sharing member ID.For best results, applications usually set TEMPORAL_LOGICAL_TRANSACTION_TIME to value that is a later time than the existing value.
While the SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME built-in global variable is set to a non-null value, the application controls the scope of temporal logical transactions, and Db2 does not ensure the uniqueness across transactions for generated values for row-begin columns in system-period temporal tables or end columns in history tables.
When the TEMPORAL_LOGICAL_TRANSACTIONS global variable is set to 0, only one assignment of a non-null value to TEMPORAL_LOGICAL_TRANSACTION_TIME is allowed within a unit of work.
The variable must not be assigned a value in the body of a before trigger.
The variable must not be assigned a non-null value if values have been assigned to row-begin columns or transaction-start-ID columns within the same unit of work.
During the execution of a data change operation, the value of the TEMPORAL_LOGICAL_TRANSACTION_TIME built-in global variable is the value that was established at the beginning of the data change statement.