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.

Examples

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);

Start of change The specified application period means that a row is valid from bus-start, including the bus-start value, to bus-end, but not including the bus-end value. This type of period is called an inclusive-exclusive period and is the default behavior for application periods. End of change

Example of creating an application-period temporal table with an inclusive-inclusive period of data type DATE

The following example CREATE TABLE statement contains the INCLUSIVE keyword in the definition of the application period to indicate an inclusive-inclusive period:

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 INCLUSIVE));

The inclusive-inclusive period means that a row is valid from bus-start, including the bus-start value, to bus-end, including the bus-end value. In this case, the data type of these columns is DATE.

Suppose that you issue the following INSERT statement:

INSERT INTO policy_info VALUES('A123', 12000, '2008-01-01', '2008-06-30')

The policy_info table then contains the following data:

policy_id	coverage	bus-start	bus_end
A123	12000	2008-01-01	2008-06-30

Suppose that you then issue the following update statement to change the coverage amount for policy A123 between May 1, 2008 and May 31, 2008.

UPDATE policy_info FOR PORTION OF BUSINESS_TIME
BETWEEN
'2008-05-01'
AND
'2008-05-31'
SET coverage = 14000
WHERE policy_id = 'A123';

The policy_info table then contains the following rows:

policy_id	coverage	bus-start	bus_end
A123	12000	2008-01-01	2008-04-30
A123	14000	2008-05-01	2008-05-31
A123	12000	2008-06-01	2008-06-30

The middle row shows the updated values for the specified period of time. In addition, two rows were inserted to represent the part of the row that was not affected by the UPDATE statement.

Example of creating an application-period temporal table with an inclusive-inclusive period of data type TIMESTAMP

The following example CREATE TABLE statement creates a table with an inclusive-inclusive application period with type TIMESTAMP(6).

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

Suppose that you issue the following INSERT statement:

INSERT INTO policy_info VALUES('A123', 12000, '2008-01-01 08:30:00.000000', 
          '2008-06-30 17:30:00.000000');

The policy_info table then contains the following data:

policy_id	coverage	bus-start	bus_end
A123	12000	2008-01-01-08.30.00.000000	2008-06-30-17.30.00.000000

Suppose that you then issue the following update statement to change the coverage amount for policy A123 between the indicated TIMESTAMP values:

UPDATE policy_info FOR PORTION OF BUSINESS_TIME      
BETWEEN
'2008-05-01 09:30:00.000001' 
AND
'2008-05-31 18:30:00.999999'
SET coverage = 14000
WHERE policy_id = 'A123';

The policy_info table then contains the following rows:

policy_id	coverage	bus-start	bus_end
A123	12000	2008-01-01-08.30.00.000000	2008-05-01-09.30.00.000000
A123	14000	2008-05-01-09.30.00.000001	2008-05-31-18.30.00.999999
A123	12000	2008-05-31-18.30.01.000000	2008-06-30-17.30.00.000000

End general-use programming interface information.