Creating a bitemporal table

Creating a bitemporal table results in a table that combines the historical tracking of a system-period temporal table with the time-specific data storage capabilities of an application-period temporal table.

About this task

When creating a bitemporal table, you combine the steps used to create a system-period temporal table with the steps used to create an application-period temporal table.

Include both a SYSTEM_TIME period and a BUSINESS_TIME period in the CREATE TABLE statement.
Create a history table to receive old rows from the bitemporal table.
Add versioning to establish the link between the bitemporal table and the history table.
Optionally, define that overlapping periods of BUSINESS_TIME are not allowed and that values are unique with respect to any period.

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

Procedure

To create a bitemporal table:

Create a table with both a SYSTEM_TIME attribute and a BUSINESS_TIME attribute.

For example:

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,
 ts_id        TIMESTAMP(12) NOT NULL
              GENERATED ALWAYS AS TRANSACTION START ID,
 PERIOD BUSINESS_TIME (bus_start, bus_end),
 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,
 bus_start    DATE NOT NULL,
 bus_end      DATE 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 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 bitemporal table.

For example:

ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info;

Optional: Create a unique index that includes the BUSINESS_TIME period.

For example:

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

Results

The policy_info table stores the insurance coverage level for a customer. The BUSINESS_TIME period-related columns (bus_start and bus_end) indicate when an insurance coverage level is valid. 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 bitemporal table (policy_info)
policy_id	coverage	bus_start	bus_end	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	bus_start	bus_end	sys_start	sys_end	ts_id

The ix_policy index, with BUSINESS_TIME WITHOUT OVERLAPS as the final column in the index key column list, ensures that there are no overlapping time periods for customer insurance coverage levels.

Example

This section contains more creating bitemporal 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,
 bus_start    DATE NOT NULL,
 bus_end      DATE 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)
              GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN,
 PERIOD BUSINESS_TIME (bus_start, bus_end),
 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.