Creating bitemporal tables

You can create a bitemporal table that maintains both system-based historical information and application period information.

About this task

You maintain system-based historical information by adding a system period to a table, and you maintain application period information by adding an application period to the table.

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 bitemporal table and define system-period data versioning on the table:

Issue a CREATE TABLE statement with both the SYSTEM_TIME clause and the BUSINESS_TIME clause. For more information about the requirements for the history table, see Creating a system-period temporal table and Creating an application-period temporal table.
Issue a CREATE TABLE statement to create a history table that receives the old rows from the bitemporal table.
Issue the ALTER TABLE ADD VERSIONING statement with the USE HISTORY TABLE clause to define system-period data versioning and establish a link between the bitemporal table and the history table.

Example

The following examples show how you can create a bitemporal table, create a history table, and then define system-period data versioning.

Begin general-use programming interface information.

This example shows a CREATE TABLE statement with the SYSTEM_TIME and BUSINESS_TIME clauses for creating a bitemporal table:

CREATE TABLE policy_info
(policy_id CHAR(4) NOT NULL,
coverage INT NOT NULL,
bus_start DATE NOT NULL,
bus_end DATE 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 BUSINESS_TIME(bus_start, bus_end),
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(4) NOT NULL,
coverage INT NOT NULL,
bus_start DATE NOT NULL,
bus_end DATE NOT NULL,
sys_start TIMESTAMP(12) NOT NULL,
sys_end TIMESTAMP(12) NOT NULL,
create_id TIMESTAMP(12));

This example shows the ALTER TABLE ADD VERSIONING statement with the USE HISTORY TABLE clause that establishes a link between the bitemporal table and the history table to enable system-period data versioning. Also, a unique index is added to the bitemporal table.

ALTER TABLE policy_info
ADD VERSIONING USE HISTORY TABLE hist_policy_info;

CREATE UNIQUE INDEX ix_policy
ON policy_info (policy_id, BUSINESS_TIME WITHOUT OVERLAPS);

End general-use programming interface information.