Creating an application-period temporal table

An application-period temporal table is a type of temporal table where you maintain the values that indicate when a row is valid. The other type of temporal table is a system-period temporal table where DB2® maintains the values that indicate when a row is valid.

About this task

When you create an application-period temporal table, you define begin and end columns to indicate the application period, or period of time when the row is valid. The begin column contains the time from which a row is valid. The end column contains the time when a row stops being valid.

Procedure

To create an application-period temporal table:

Issue a CREATE TABLE statement with the following items:

Two columns to define the application period. These columns are the begin and end columns. They must be type TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL or DATE NOT NULL, and they must be the same type. The data type cannot be a user-defined type.
The BUSINESS_TIME clause.

Example

Begin general-use programming interface information.

Example of creating an application-period temporal table

The following example SQL statements create a table with an application period and a unique index:

CREATE TABLE policy_info
(policy_id CHAR(4) NOT NULL,
coverage INT NOT NULL,
bus_start DATE NOT NULL,
bus_end DATE NOT NULL,
PERIOD BUSINESS_TIME(bus_start, bus_end));

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

End general-use programming interface information.