DB2 10.5 for Linux, UNIX, and Windows

systime_period_adj - Adjust temporal SYSTEM_TIME period database configuration parameter

This database configuration parameter specifies what action to take when a history row for a system-period temporal table is generated with an end timestamp that is less then the begin timestamp.

This can happen when two different transactions conflict in their attempt to update the same row in a system-period temporal table. It can also happen as the result of an adjustment to the system clock; for example, if the system clock is adjusted back an hour for the end of daylight savings time.

Configuration type

Database

Applies to

Database server with local and remote clients
Client
Database server with local clients
Partitioned database server with local and remote clients

Parameter type

Configurable online

Propagation class

Immediate

Default [range]

NO [NO, YES]

When a row is updated in a system-period temporal table, a history row is generated with a SYSTEM_TIME period indicating the range of time when the data in the history row was current. The value in the row-begin column indicates when the data in the history row became current. The value in the row-end column indicates when the history row became history data.

Following is an example of how two conflicting transactions could potentially generate a history row that has an row-end timestamp that is less than the row-begin timestamp.

Transaction TRA has a row-begin value generated for a row in a system-period temporal table it is updating at timestamp T1.
Transaction TRB has a row-begin value generated for the same row that it is updating at timestamp T2 (where T1 < T2).
Transaction TRB generates a history row and commits.
Transaction TRA generates its history row and commits.

After this sequence of events, the history row generated for transaction TRA would have an end timestamp that is less than its begin timestamp.

The database manager can ensure that generated history rows always have an end timestamp greater than the start timestamp by allowing timestamp adjustments when there are conflicts or by rolling back one of the transactions involved.

NO: No timestamp value adjustments are made when the end timestamp is less than the start timestamp for a history row that is being inserted. Instead, the transaction that is attempting to insert the history row fails and an error is returned (SQLSTATE 57062, SQLCODE SQL20528N). Not allowing adjustments ensures that all history rows generated during the transaction have the same end timestamp and can easily be identified using that end timestamp.
YES: An adjustment is made to the timestamp value of the row-begin column value for the system-period temporal table and the end timestamp value for the generated history row when there are timestamp conflicts. The adjustment consists of modifying the end timestamp to be greater than the start timestamp by 1 microsecond. This ensures that the end timestamp is greater than the start timestamp for the history row. A message is returned indicating that an adjustment was made (SQLSTATE 01695, SQLCODE SQL5191W).

When no timestamp adjustments are necessary, SQLCODE DB20000I is returned.

Application programmers might consider using SQLCODE or SQLSTATE values to handle these timestamp value adjustment-related return codes from SQL statements.