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.
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 add a system period to a table and define system-period
data versioning:
-
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 (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
- 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
- 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
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;
