Creating a system-period temporal table

Creating a system-period temporal table results in a table that tracks when data changes occur and preserves historical versions of that data.

About this task

When creating a system-period temporal table, include attributes that indicate when data in a row is current and when transactions affected the data:

Include row-begin and row-end columns that are used by the SYSTEM_TIME period to track when a row is current.
Include a transaction start-ID column that captures the start times for transactions that affect rows.
Create a history table to receive old rows from the system-period temporal table.
Add versioning to establish the link between the system-period temporal table and the history table.

The row-begin, row-end, and transaction start-ID columns can be defined as IMPLICITLY HIDDEN. Since these columns and their entries are generated by the database manager, hiding them can minimize any potential negative affects on your applications. These columns are then unavailable unless referenced, for example:

A SELECT * query run against a table does not return any implicitly hidden columns in the result table.
An INSERT statement does not expect a value for any implicitly hidden columns.
The LOAD, IMPORT, and EXPORT commands can use the includeimplicitlyhidden modifier to work with implicitly hidden columns.

A system-period temporal table can be defined as a parent or a child in a referential constraint. However, the referential constraints are applied only to the current data, that is the data in the system-period temporal table. The constraints are not applied to the associated history table. In order to minimize inconsistencies when a system-period temporal table is a child table in a referential constraint, the parent table should also be a system-period temporal table.

Note: While the row-begin, row-end, and transaction start-ID generated columns are required when creating a system-period temporal table, you can also create a regular table with these generated columns.

The example in the following section shows the creation of a table that stores policy information for the customers of an insurance company.

Procedure

To create a system-period temporal table.

Create a table with a SYSTEM_TIME attribute.

For example:

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;

Create a history table.

For example:

CREATE TABLE hist_policy_info
(
 policy_id    CHAR(4) NOT NULL,
 coverage     INT NOT NULL,
 sys_start    TIMESTAMP(12) NOT NULL,
 sys_end      TIMESTAMP(12) NOT NULL,
 ts_id        TIMESTAMP(12) NOT NULL
) IN hist_space;

You can also create a history table with the same names and descriptions as the columns of the system-period temporal table by using the LIKE clause of the CREATE TABLE statement. For example:

CREATE TABLE hist_policy_info LIKE policy_info IN hist_space;

Add versioning to the system-period temporal table to establish a link to the history table.
For example:
```
ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info;
```

Results

The policy_info table stores the insurance coverage level for a customer. The SYSTEM_TIME period related columns (sys_start and sys_end) show when a coverage level row is current. The ts_id column lists the time when execution started for a transaction that impacted the row.

Table 1. Created system-period temporal table (policy_info)
policy_id	coverage	sys_start	sys_end	ts_id

The hist_policy_info history table receives the old rows from the policy_info table.

Table 2. Created history table (hist_policy_info)
policy_id	coverage	sys_start	sys_end	ts_id

Example

This section contains more creating system-period temporal table examples.

Hiding columns

The following example creates the policy_info table with the TIMESTAMP(12) columns (sys_start, sys_end and ts_id) marked as implicitly hidden.

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 IMPLICITLY HIDDEN,
 sys_end      TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END IMPLICITLY HIDDEN,
 ts_id        TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN,
 PERIOD SYSTEM_TIME (sys_start, sys_end)
) in policy_space;

Creating the hist_policy_info history table using the LIKE clause of the CREATE TABLE statement results in the history table inheriting the implicitly hidden attribute from the policy_info table. If you do not use the LIKE clause when creating the history table, then any columns marked as hidden in the system-period temporal table must also be marked as hidden in the associated history table.

Changing an existing table into a system-period temporal table

The following example adds timestamp columns and a SYSTEM_TIME period to an existing table (employees) enabling system-period temporal table functions.

ALTER TABLE employees
   ADD COLUMN sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN;
ALTER TABLE employees 
   ADD COLUMN sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END;
ALTER TABLE employees
   ADD COLUMN ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID;
ALTER TABLE employees ADD PERIOD SYSTEM_TIME(sys_start, sys_end);

These new columns can be hidden by including the IMPLICITLY HIDDEN clause in the ALTER TABLE statement

A history table must be created and versioning added to finish this task.