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
- Applies to
- Database server with local and remote clients
- Database server with local clients
- Partitioned database server with local and remote clients
- Parameter type
- Configurable online
- Propagation class
- 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.
- 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.
- 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.
- 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.