Adding an application period to a table

You can alter a table to add an application period so that you maintain the beginning and ending values for a row.

Procedure

To add an application period to a table:

Issue the ALTER TABLE statement with the ADD PERIOD BUSINESS_TIME clause.

The table becomes an application-period temporal table.

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(4) NOT NULL,
coverage INT NOT NULL,
bus_start DATE NOT NULL,
bus_end DATE NOT NULL);

You can add an application period to this table by issuing the following ALTER TABLE statement:

ALTER TABLE policy_info ADD PERIOD BUSINESS_TIME(bus_start, bus_end);

You also can add a unique index to the table by issuing the following CREATE INDEX statement:

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

Restriction: You cannot issue the ALTER INDEX statement with ADD BUSINESS_TIME WITHOUT OVERLAPS. Db2 issues SQL error code -104 with SQLSTATE 20522.