Creating a system-period temporal table

You can create a temporal table that has a system period and define system-period data versioning on the table, so that the data is versioned after insert, update, and delete operations.

Before you begin

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 create a temporal table with a system period and define system-period data versioning on the table:

  1. Issue a CREATE TABLE statement with a SYSTEM_TIME clause.
    The created table 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

    It cannot have clone table defined on it, and it cannot have the following attributes:

    • Column masks
    • Row permissions
    • Security label columns
  2. Issue a CREATE TABLE statement to create a history table that receives the old rows from 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.
    By defining system-period data versioning, you establish a link between the system-period temporal table and the history table.

Example

The following examples show how you can create a temporal table with a system period, create a history table, and then define system-period data versioning on the table. Also, a final example shows how to insert data.

Begin general-use programming interface information.The following example shows a CREATE TABLE statement for creating a temporal table with a SYSTEM_TIME period. In the example, the sys_start column is the row-begin column, sys_end is the row-end column, and create_id is the transaction-start-ID column. The SYSTEM_TIME period is defined on the ROW BEGIN and ROW END columns:

CREATE TABLE policy_info
(policy_id CHAR(10) 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,
create_id TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID,
PERIOD SYSTEM_TIME(sys_start,sys_end));

This example shows a CREATE TABLE statement for creating a history table:

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,
create_id TIMESTAMP(12))

To define versioning, issue the ALTER TABLE statement with the ADD VERSIONING clause and the USE HISTORY TABLE clause, which establishes a link between the system-period temporal table and the history table:

ALTER TABLE policy_info
ADD VERSIONING USE HISTORY TABLE hist_policy_info;

The following example shows how to insert data in the POLICY_ID and COVERAGE columns of the POLICY_INFO table:

INSERT INTO POLICY_INFO (POLICY_ID, COVERAGE)     
VALUES('A123', 12000);

If you want to use temporal tables to track auditing information, see the example in Scenario for tracking auditing information with system-period temporal tables.

End general-use programming interface information.