Adding a system period and system-period data versioning to an existing table

You can alter existing tables to use system-period data versioning.

About this task

A system period is a system-maintained period in which Db2 maintains the beginning and ending timestamp values for a row.

A system period temporal table has three required columns. The row-begin column of the system period contains the timestamp value for when a row is created. The row-end column contains the timestamp value for when a row is removed. A transaction-start-ID column contains a unique timestamp value that Db2 assigns per transaction, or the null value.

The null value is assigned to the transaction-start-ID column if the column is nullable. Otherwise, the value is generated using the time-of-day clock during execution of the first data change statement in the transaction that requires a value to be assigned to a row-begin column or transaction-start-ID column in the table, or when a row in a system-period temporal table is deleted. If multiple rows are inserted or updated within a single SQL transaction, the values for the transaction-start-ID column are the same for all the rows and are unique from the values that are generated for the column for another transaction.

Procedure

To add a system period to a table and define system-period data versioning:

  1. Issue the ALTER TABLE statement on the base table to alter or add row-begin, row-end, and transaction-start-ID columns, and to define the system period.
    After you alter the table, it must have the following attributes:
    • A row-begin column that is defined as TIMESTAMP(12) NOT NULL with the GENERATED ALWAYS AS ROW BEGIN attribute.
    • A row-end column that is defined as TIMESTAMP(12) NOT NULL with the GENERATED ALWAYS AS ROW END attribute.
    • A system period (specified by a SYSTEM_TIME clause) defined on two timestamp columns. The first column is the row-begin column and the second column is the row-end column.
    • Start of changeA transaction-start-ID column that is defined as TIMESTAMP(12) with the GENERATED ALWAYS AS TRANSACTION START ID attribute. Defining this column as NOT NULL is optional.End of change
    • The only table in the table space
    • The table definition is complete
  2. Issue a CREATE TABLE statement to create a history table that will correspond with the system-period temporal table.
    The history table must have the following attributes:
    • The same number of columns as the system-period temporal table that it corresponds to
    • Columns with the same names, data types, null attributes, CCSIDs, subtypes, hidden attributes, and field procedures as the corresponding system-period temporal table. However, the history table cannot have any GENERATED ALWAYS columns unless the system-period temporal table has a ROWID GENERATED ALWAYS or ROWID GENERATED BY DEFAULT column. In that case, the history table must have a corresponding ROWID GENERATED ALWAYS column. .
    • The only table in the table space
    • The table definition is complete

    A history table cannot be a materialized query table, an archive-enabled table, or an archive table, cannot have a clone table defined on it, and cannot have the following attributes:

    • Identity columns or row change timestamp columns
    • ROW BEGIN, ROW END, or TRANSACTION START ID columns
    • Column masks
    • Row permissions
    • Security label columns
    • System or application periods
  3. Issue the ALTER TABLE ADD VERSIONING statement with the USE HISTORY TABLE clause to define system-period data versioning on the table.
    This step establishes a link between the system-period temporal table and the history table.

Example

Begin general-use programming interface information.For example, consider that you created a table named policy_info by issuing the following CREATE TABLE statement:

CREATE TABLE policy_info
(policy_id CHAR(10) NOT NULL,
coverage INT NOT NULL);

Issue the following ALTER TABLE statements to add the begin and end columns and a system period to the table:

ALTER TABLE policy_info ADD COLUMN sys_start TIMESTAMP(12) NOT NULL
GENERATED ALWAYS AS ROW BEGIN;

ALTER TABLE policy_info ADD COLUMN sys_end TIMESTAMP(12) NOT NULL
GENERATED ALWAYS AS ROW END;

ALTER TABLE policy_info ADD COLUMN trans_id TIMESTAMP(12);
GENERATED ALWAYS AS TRANSACTION START ID;

ALTER TABLE policy_info
ADD PERIOD SYSTEM_TIME(sys_start, sys_end);

To create a history table for this system-period temporal table, issue the following CREATE TABLE statement:

CREATE TABLE hist_policy_info
(policy_id CHAR(10) NOT NULL,
coverage INT NOT NULL,
sys_start TIMESTAMP(12) NOT NULL,
sys_end TIMESTAMP(12) NOT NULL,
trans_id TIMESTAMP(12));

To define system-period data versioning between the system-period temporal table and the history table, issue the following ALTER TABLE statement:

ALTER TABLE policy_info
ADD VERSIONING USE HISTORY TABLE hist_policy_info;

End general-use programming interface information.