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.

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 (SYSTEM_TIME) defined on two timestamp columns. The first column is the row-begin column and the second column is the row-end column.
   • A transaction-start-ID column that defined as TIMESTAMP(12) NOT NULL with the GENERATED ALWAYS AS TRANSACTION START ID attribute.
   • 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, 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.

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);