The SYSTEM_TIME period columns for a system-period temporal
table indicate when the version of a row is current.
The SYSTEM_TIME period contains a pair of TIMESTAMP(12) columns
whose values are generated by the database manager. The columns must
be defined as NOT NULL with an applicable GENERATED ALWAYS AS option.
The begin column of the period must be a row-begin column and the
end column of the period must be a row-end column.
CREATE TABLE policy_info
(
policy_id CHAR(4) NOT NULL,
coverage INT NOT NULL,
sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
PERIOD SYSTEM_TIME (sys_start, sys_end)
) IN policy_space;
- Row-begin column
- This column represents the time when the row data became current.
The database manager generates a value for this column by using a
reading of the system clock at the moment it executes the first data
change statement in the transaction that generates the row. If multiple
rows are inserted or updated within a single SQL transaction, the
values for the row-begin column are the same for all the impacted
rows. The values for these row-begin columns are unique from the values
generated for the row-begin columns for other transactions. A row-begin
column is required as the begin column of a SYSTEM_TIME period, which
must be defined for each system-period temporal table.
- When an existing regular table is altered to make it a system-period
temporal table, a row-begin column is added to the table. The row-begin
column is populated with a default of 0001-01-01-00.00.00.000000000000 for
the TIMESTAMP(12) data type value for any existing rows.
- Row-end column
- This column represents the time when the row data was no longer
current. For rows in a history table, the value in the row-end column
represents when the row was added to the history table. The rows in
the system-period temporal table are by definition current, so the
row-end column is populated with a default value for the TIMESTAMP(12)
data type (for example: 9999-12-30-00.00.00.000000000000).
A row-end column is required as the end column of a SYSTEM_TIME period,
which must be defined for each system-period temporal table.
- When an existing regular table is altered to make it a system-period
temporal table, a row-end column is added to the table. The row-end
column is populated with the a value for the TIMESTAMP(12) data type
(default value: 9999-12-30-00.00.00.000000000000)
for any existing rows. The value is intentionally one day earlier
than the maximum possible time stamp to avoid a possible overflow
to a 5-digit year when converting the value to a different time zone.
Since row-begin and row-end are generated columns, there is no
implicit check constraint generated for SYSTEM_TIME that ensures that
the value for an end column is greater than the value for its begin
column in a system-period temporal table. This lack of a check constraint
differs from an application-period temporal table where there is a
check constraint associated with its BUSINESS_TIME. A row where the
value for the end column is less than the value for the begin column
cannot be returned when a period-specification is used to query the
table. You can define a constraint to guarantee that the value for
end column is greater than the value for begin column. This guarantee
is useful when supporting operations that explicitly input data into
these generated columns, such as a load operation.
The systime_period_adj database configuration
parameter is used to specify what action to take when a history row
for a system-period temporal table is generated with an end column
value that is less than the value for begin column.
DB2 10.5 for Linux, UNIX, and Windows