Creating Transaction-Time Tables
There are three ways to create a transaction-time table:
Creating a New Transaction-Time Table
To create a transaction-time table, use a normal CREATE TABLE statement, and define one column of the table as a transaction-time column.The data type of the column must be PERIOD(TIMESTAMP(6) WITH TIME ZONE), and must use the TRANSACTIONTIME or AS TRANSACTIONTIME column attribute. Transaction-time columns must also specify the NOT NULL column attribute.
CREATE MULTISET TABLE Policy_Types (
Policy_Type CHAR(2) NOT NULL PRIMARY KEY,
Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
PRIMARY INDEX (Policy_Name);
Adding a Transaction-Time Column to a Table
To add a transaction-time column to a nontemporal or valid-time table, use the ALTER TABLE statement.
Consider the following nontemporal table definition:
CREATE MULTISET TABLE Customer (
PRIMARY INDEX (Customer_ID);
The following statement adds a transaction-time column to the Customer table:
ALTER TABLE Customer
ADD Customer_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
Temporal tables cannot have unique primary indexes. If the original nontemporal table has a unique primary index, use ALTER TABLE to modify the primary index to nonunique prior to adding the temporal column. Uniqueness can be applied to a temporal table using other constraints. For more information, see “Using Constraints with Temporal Tables” on page 82.
Creating a Transaction-Time Column Based on Two Existing Timestamp Columns
If a nontemporal table has two pre-existing DateTime columns that represent the beginning and end bounds of a period of time, you can add a derived period column to the table based on the DateTime columns. The derived period column can serve as a transaction-time column, converting the table to a temporal table.
Note: The begin and end columns of a derived period column cannot be included in a primary index if the derived period column serves as a valid-time or transaction-time column.
Given a table that was originally created with the following DDL statement:
CREATE MULTISET TABLE Policy(
Policy_Type CHAR(2) NOT NULL,
Policy_Tx_Begin TIMESTAMP(6) WITH TIME ZONE,
Policy_Tx_End TIMESTAMP(6) WITH TIME ZONE
Prior to converting the table to a temporal table, existing constraints should be noted, then dropped. The table could be converted to a transaction-time table using the following statement:
ALTER TABLE Policy
ADD PERIOD FOR Policy_Tx_ Duration (Policy_Tx_Begin,Policy_Tx_End) AS TRANSACTION TIME;
After creating the temporal table, the constraints that were dropped can be reapplied.
Converting a Period Column to a Transaction-Time Column
If you have a table that includes a column of type PERIOD(TIMESTAMP(6) WITH ZONE), take the following steps to convert the existing Period column to a transaction-time column:
1 Note all the constraint information on the original table.
2 Drop all the constraints on the original table.
3 Grant NONTEMPORAL privilege to the user on the table.
4 ALTER TABLE to add a transaction-time column.
5 Submit NONTEMPORAL UPDATE to set the new transaction-time column with the existing column value being converted.
6 ALTER TABLE to drop the existing Period column.
7 ALTER TABLE to rename the transaction-time column with the name of the dropped column.
8 Create all the previously dropped constraints with the desired transaction-time qualifier.
For more information on...
CREATE TABLE (temporal form)
ALTER TABLE (temporal form)
UPDATE TABLE (temporal form)