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
You can also alter existing tables to use system-period data versioning. For more information, see Adding a system period and system-period data versioning to an existing table.
About this task
A system period is a system-maintained period in which Db2 maintains the beginning and ending timestamp values for a row.
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.
For a list of restrictions that apply to tables that use system-period data versioning, see Restrictions for system-period data versioning.
Procedure
To create a temporal table with a system period and define system-period data versioning on the 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);
If you want to use temporal tables to track auditing information, see the example in Scenario for tracking auditing information.