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.