Examples of various operations that involve temporal tables:
CREATE MULTISET TABLE Policy(
Policy_ID INTEGER,
Customer_ID INTEGER,
Policy_Type CHAR(2) NOT NULL,
Policy_Details CHAR(40),
Validity PERIOD(DATE) NOT NULL AS VALIDTIME
)
PRIMARY INDEX(Policy_ID);
CREATE MULTISET TABLE Policy_Types (
Policy_Name VARCHAR(20),
Policy_Type CHAR(2) NOT NULL PRIMARY KEY,
Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
AS TRANSACTIONTIME
)
PRIMARY INDEX (Policy_Name);
CREATE MULTISET TABLE Policy_History(
Policy_ID INTEGER,
Customer_ID INTEGER,
Policy_Type CHAR(2) NOT NULL,
Policy_Details CHAR(40),
Validity PERIOD(DATE) NOT NULL AS VALIDTIME,
Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
AS TRANSACTIONTIME)
)
PRIMARY INDEX(Policy_ID);
Policy is a
valid-time table,
Policy_Types is a transaction-time table,
and Policy_History is a bitemporal table.
Querying Temporal Tables::
Example 1: Current Query on a Valid-Time Table
To query a valid-time table for the rows that are valid at the current time (rows that overlap with current time), use the CURRENT VALIDTIME temporal qualifier in the SELECTstatement.
For example:
CURRENT VALIDTIME
SELECT *
FROM Policy
WHERE Policy_Type = 'AU';
The result is a nontemporal result set. (The result set does not include the valid-time column.)
Policy_ID Customer_ID Policy_Type Policy_Details
--------- ----------- ----------- -----------------
541077 766492008 AU STD-CH-344-YXY-00
541145 616035020 AU STD-CH-348-YXN-01
541008 246824626 AU STD-CH-345-NXY-00
Example 2: Current Query on a Transaction-Time Table
To query a transaction-time table for the current rows, use the CURRENT
TRANSACTIONTIME temporal qualifier in the SELECT statement.
For example:
CURRENT TRANSACTIONTIME
SELECT *
FROM Policy_Types;
The result is a nontemporal result set. (The result set does not include the transaction-time column.)
Policy_Name Policy_Type
-------------------- -----------
Premium Automobile AP
Basic Homeowner HM
Basic Automobile AU
Example 3: Sequenced Query on a Valid-Time Table
To query a valid-time table for the rows that are valid at a specific time period, use the SEQUENCED VALIDTIME temporal qualifier in the SELECT statement.
For example:
SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)'
SELECT Policy_ID, Customer_ID
FROM Policy
WHERE Policy_Type = 'AU';
The result set is a temporal table that includes rows that are valid for a period of applicability specified by PERIOD '(2009-01-01, 2009-12-31)':
Policy_ID Customer_ID VALIDTIME
--------- ----------- ------------------------
541077 766492008 ('09/12/21', '09/12/31')
541145 616035020 ('09/12/03', '09/12/31')
541008 246824626 ('09/10/01', '09/12/31')
Note: The valid-time column for the result set, VALIDTIME, which is automatically appended to the results of a sequenced valid-time query, is different from the valid-time column of the temporal table that was queried.
The valid time for the results of the query is the intersection of the PA of the query and the original valid-time periods of the rows.
Example 4: Nonsequenced Query on a Valid-Time Table
To query a valid-time table such that no special semantics are placed on the valid-time column, use the NONSEQUENCED VALIDTIME temporal qualifier in the SELECT statement.
For example:
NONSEQUENCED VALIDTIME SELECT * FROM Policy;
Example 5: Nonsequenced Query on a Transaction-Time Table
To query a transaction-time table such that no special semantics are placed on the transaction-time column, use the NONSEQUENCED TRANSACTIONTIME temporal qualifier in the SELECT statement.
For example:
NONSEQUENCED TRANSACTIONTIME SELECT * FROM Policy_Types;
Example 6: As Of Query on a Valid-Time Table
To get a snapshot of a valid-time table where the valid-time period in the result rows overlap a specific time, use the VALIDTIME AS OF temporal qualifier in the SELECT statement.
For example:
VALIDTIME AS OF DATE '2009-01-01' SELECT * FROM Policy;
Example 7: As Of Query on a Transaction-Time Table
To get a snapshot of a transaction-time table where the transaction-time period in the result rows overlap a specific time, use the TRANSACTIONTIME AS OF temporal qualifier in the SELECT statement.
For example:
TRANSACTIONTIME AS OF DATE '2009-01-01' SELECT * FROM Policy_Types;
Modifying Temporal Tables
The following examples demonstrate basic modifications to temporal tables.
Example 1: Current Valid-Time Insert into a Valid-Time Table
To perform a current valid-time insert into a valid-time table, use the CURRENT VALIDTIME qualifier.
Consider the following valid-time table:
CREATE MULTISET TABLE Policy(
Policy_ID INTEGER,
Customer_ID INTEGER,
Policy_Type CHAR(2) NOT NULL,
Policy_Details CHAR(40),
Validity PERIOD(DATE) NOT NULL AS VALIDTIME
)
PRIMARY INDEX(Policy_ID);
The following statement performs a current valid-time insert into the Policy table. Because the INSERT uses a positional assignment list (where no column names are provided), no value for the valid-time column can be specified. The system timestamps the value of the valid-timecolumn.
CURRENT VALIDTIME INSERT INTO Policy
VALUES (541077, 766492008, 'AU', 'STD-CH-344-YXY-00');
The following statement also performs a current valid-time insert into the Policy table.Because the INSERT uses a named list, a value for the valid-time column can be specified.
CURRENT VALIDTIME INSERT INTO Policy(Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity)VALUES (541145, 616035020, 'AU', 'STD-CH-348-YXN-01',PERIOD '(2009-12-03, 2010-12-01)');
Example 2: Sequenced Valid-Time Insert into a Valid-Time Table
Use a sequenced valid-time insert to insert history, current, or future rows into a valid-time table. A sequenced valid-time insert is similar to a conventional insert where the valid-time column is treated as any other column in the table.
Consider the same valid-time table as in the previous example.
The following statements perform sequenced valid-time inserts into the Policy table.
SEQUENCED VALIDTIME INSERT INTO Policy
VALUES (232540, 909234455, 'BM', 'STD-CH-344-YYY-00',
PERIOD (DATE '1999-01-01', DATE '1999-12-31'));
SEQUENCED VALIDTIME INSERT INTO Policy
(Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity)
VALUES (944540, 344567123, 'BM', 'STD-PL-332-YXY-01',
PERIOD (DATE '2007-02-03', DATE '2008-02-02'));
Example 3: Nonsequenced Valid-Time Insert into a Valid-Time Table
A nonsequenced valid-time insert is similar to a conventional insert where the valid-time column is treated as any other column in the table.
Consider the same valid-time table as in the previous examples. The following statements.
The following statements perform nonsequenced valid-time inserts into the Policy table.
NONSEQUENCED VALIDTIME INSERT INTO Policy VALUES (540232, 455909234, 'AU', 'STD-CH-344-YYY-00',PERIOD (DATE '2009-01-01', DATE '2009-12-31'));
NONSEQUENCED VALIDTIME INSERT INTO Policy
(Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity)
VALUES (540944, 123344567, 'AU', 'STD-PL-332-YXY-01',
PERIOD (DATE '2007-02-03', DATE '2008-02-02'));
Example 4: Current Valid-Time Insert into a Bitemporal Table
To insert data into a bitemporal table that is open in the transaction-time dimension and current in the valid-time dimension, use the CURRENT VALIDTIME qualifier.
Consider the following bitemporal table:
CREATE MULTISET TABLE Policy_History(
Policy_ID INTEGER,
Customer_ID INTEGER,
Policy_Type CHAR(2) NOT NULL,
Policy_Details CHAR(40),
Validity PERIOD(DATE) NOT NULL AS VALIDTIME,
Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
AS TRANSACTIONTIME)
PRIMARY INDEX(Policy_ID);
The following statement performs a current valid-time insert into the Policy_History table.
Because the INSERT uses a positional assignment list (where no column names are provided), no value for the valid-time column can be specified. Because the system inserts the value for the transaction-time column, no value for the transaction-time column can be specified.
CURRENT VALIDTIME INSERT INTO Policy_History
VALUES (541077, 766492008, 'AU', 'STD-CH-344-YXY-00');
The following statement also performs a current valid-time insert into the Policy_History table. Because the INSERT uses a named list, a value for the valid-time column can be specified. Because the system inserts the value for the transaction-time column, no value for the transaction-time column can be specified.
CURRENT VALIDTIME INSERT INTO Policy_History
(Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity)
VALUES (541145, 616035020, 'AU', 'STD-CH-348-YXN-01',
PERIOD '(2009-12-03, 2010-12-01)');
Example 5: Sequenced Valid-Time Insert into a Bitemporal Table
A sequenced valid-time insert is similar to a conventional insert, where the valid-time column is treated as any other column in the table. Use a sequenced valid-time insert to insert rows that are history, current, or future in the valid-time dimension.All such insertions are open in the transaction-time dimension. Because the system automatically inserts the value for the transaction-time column, the INSERT statement cannot specify a value for the transaction-time column.
Consider the following bitemporal table:
CREATE MULTISET TABLE Policy_History(
Policy_ID INTEGER,
Customer_ID INTEGER,
Policy_Type CHAR(2) NOT NULL,
Policy_Details CHAR(40),
Validity PERIOD(DATE) NOT NULL AS VALIDTIME,
Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
AS TRANSACTIONTIME)
PRIMARY INDEX(Policy_ID);
The following statements perform sequenced valid-time inserts that are open in the transaction-time dimension into the Policy_History table.
SEQUENCED VALIDTIME INSERT INTO Policy_History
VALUES (232540, 909234455, 'BM', 'STD-CH-344-YYY-00',
PERIOD (DATE '1999-01-01', DATE '1999-12-31'));
SEQUENCED VALIDTIME INSERT INTO Policy_History
(Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity)
VALUES (944540, 344567123, 'BM', 'STD-PL-332-YXY-01',
PERIOD (DATE '2007-02-03', DATE '2008-02-02'));
Example 6: Nonsequenced Valid-Time Insert into a Bitemporal Table
A nonsequenced valid-time insert is similar to a conventional insert where the valid-time column is treated as any other column in the table. Because the system automatically inserts the value for the transaction-time column, the INSERT statement cannot specify a value for the transaction-time column.
Consider the following bitemporal table:
CREATE MULTISET TABLE Policy_History(
Policy_ID INTEGER,
Customer_ID INTEGER,
Policy_Type CHAR(2) NOT NULL,
Policy_Details CHAR(40),
Validity PERIOD(DATE) NOT NULL AS VALIDTIME,
Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
AS TRANSACTIONTIME)
PRIMARY INDEX(Policy_ID);
The following statements perform nonsequenced valid-time inserts that are open in the transaction-time dimension into the Policy_History table.
NONSEQUENCED VALIDTIME INSERT INTO Policy_History
VALUES (540232, 450909234, 'AU', 'STD-CH-344-YYY-00',
PERIOD (DATE '2009-11-01', UNTIL_CHANGED));
NONSEQUENCED VALIDTIME INSERT INTO Policy_History
(Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity)
VALUES (540944, 120344567, 'AU', 'STD-PL-332-YXY-01',
PERIOD (DATE '2010-02-03', DATE '2011-02-02'));
Example 7: Nontemporal Insert into a Bitemporal Table
A nontemporal insert in to a bitemporal table is similar to a conventional insert, where the valid-time and transaction-time columns are treated as any other column in the table. You can use a nontemporal insert to insert closed or open rows.
To perform a nontemporal insert, you must have the NONTEMPORAL privilege on the target table.
Consider the following bitemporal table:
CREATE MULTISET TABLE Policy_History(
Policy_ID INTEGER,
Customer_ID INTEGER,
Policy_Type CHAR(2) NOT NULL,
Policy_Details CHAR(40),
Validity PERIOD(DATE) NOT NULL AS VALIDTIME,
Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
AS TRANSACTIONTIME)
PRIMARY INDEX(Policy_ID);
The following nontemporal INSERT statements insert rows into Policy_History, explicitly specifying values for the valid-time and transaction-time columns:
NONTEMPORAL INSERT INTO Policy_History
VALUES (411458, 160350204, 'AU', 'STD-CH-340-YXN-01',
PERIOD '(2009-12-03, 2010-12-01)',
PERIOD (TIMESTAMP '2004-01-01 00:00:00.000000', UNTIL_CLOSED));
NONTEMPORAL INSERT INTO Policy_History
VALUES (114583, 603502048, 'AU', 'STD-CH-920-YXD-01',
PERIOD '(2009-12-08, 2010-12-07)',
PERIOD (TIMESTAMP '2004-01-01 00:00:00.000000', UNTIL_CLOSED));
Example 8: Current Insert into a Transaction-Time Table
The following INSERT statement inserts an open row into the Policy_Types table. Because the system automatically inserts the value for the transaction-time column, no value for the transaction-time column can be specified.
INSERT INTO Policy_Types
VALUES ('Basic Motorcycle', 'BM');
Example 9: Nontemporal Insert into a Transaction-Time Table
A nontemporal insert is similar to a conventional insert, where the transaction-time column is treated as any other column in the table. You can use a nontemporal insert to insert closed or open rows.
Note: To perform a nontemporal insert, you must have the NONTEMPORAL privilege on the target table.
Consider the following transaction-time table:
CREATE MULTISET TABLE Policy_Types (
Policy_Name VARCHAR(20),
Policy_Type CHAR(2) NOT NULL PRIMARY KEY,
Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
AS TRANSACTIONTIME
)
PRIMARY INDEX (Policy_Name);
The following nontemporal INSERT statements insert rows into Policy_Types, explicitly specifying values for the transaction-time column:
NONTEMPORAL INSERT INTO Policy_Types VALUES ('Premium Automobile', 'AP',PERIOD (TIMESTAMP '2004-01-01 00:00:00.000000', UNTIL_CLOSED));
NONTEMPORAL INSERT INTO Policy_Types (Policy_Name, Policy_Type, Policy_Duration)VALUES ('Basic Homeowner', 'HM',PERIOD (TIMESTAMP '2004-01-01 00:00:00.000000', UNTIL_CLOSED));
Example 10: Current Delete from a Valid-Time Table
To perform a current delete, use the CURRENT VALIDTIME qualifier in the DELETE statement.
For a table with valid time, current rows qualify for deletion. Depending on the period of validity of a qualified row and whether the table also supports transaction time, the delete operation may physically delete a row, logically delete a row, modify the period of validity for a row, or logically delete a row and create a new row. Consider the following data in the Policy table:
NONSEQUENCED VALIDTIME
SELECT Policy_ID, Customer_ID, Validity
FROM Policy
WHERE Policy_Type = 'AU';
Policy_ID Customer_ID Validity
--------- ----------- ------------------------
497201 304779902 ('05/02/14', '06/02/13')
540944 123344567 ('07/02/03', '08/02/02')
541077 766492008 ('09/12/21', '99/12/31')
541145 616035020 ('09/12/03', '10/12/01')
541008 246824626 ('09/10/01', '99/12/31')
Suppose the value of TEMPORAL_DATE is the following:
SELECT TEMPORAL_DATE;
Temporal Date
-------------
09/12/21
The following current DELETE statement physically deletes the qualified row from the table because the beginning bound of the period of validity is equal to TEMPORAL_DATE:
CURRENT VALIDTIME DELETE
FROM Policy
WHERE Policy_ID = 541077;
Example 11: Current Delete from a Valid-Time Table
The following current DELETE statement modifies the period of validity for the qualified row from the table because the beginning bound of the period of validity is less than TEMPORAL_DATE. The row becomes a history row.
CURRENT VALIDTIME DELETE
FROM Policy
WHERE Policy_ID = 541145;
NONSEQUENCED VALIDTIME
SELECT Policy_ID, Customer_ID, Validity
FROM Policy
WHERE Policy_Type = 'AU';
Policy_ID Customer_ID Validity
--------- ----------- ------------------------
497201 304779902 ('05/02/14', '06/02/13')
540944 123344567 ('07/02/03', '08/02/02')
541145 616035020 ('09/12/03', '09/12/21')
541008 246824626 ('09/10/01', '99/12/31')
Example 12: Current Modifications Do Not Apply to Future Rows
This example demonstrates that current data modifications do not apply to future rows.
Assume the following tables describe a company’s employees and departments:
CREATE MULTISET TABLE employee ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
eid INTEGER NOT NULL,
ename VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
bdate DATE FORMAT 'yyyy/mm/dd',
job_duration PERIOD(DATE) NOT NULL AS VALIDTIME,
deptid INTEGER,
mid INTEGER)
PRIMARY INDEX ( eid );
CREATE MULTISET TABLE dept ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
deptid INTEGER NOT NULL,
deptname VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( deptid );
Assume the company management must cut down the staff of the SUPPORT department.
Employees who have been with the company for less than three months must be discharged.
The following query removes employees with job durations of less than three months:
CURRENT VALIDTIME
DELETE employee
FROM dept
WHERE dept.deptname = 'SUPPORT' AND
Dept.deptid = employee.deptid AND
BEGIN(job_duration) > CURRENT_DATE - interval '3' month;
Now assume that the system includes employee entries for new employees who have not yet started working at the company. The DELETE statement above will not remove these future employees. To remove them together with the current employees, the following statement could be used:
BT;
/* delete currently employees in department with less than 3 months
work*/
CURRENT VALIDTIME
DELETE employee
FROM dept
WHERE dept.deptname = 'SUPPORT' AND
Dept.deptid = employee.deptid AND
BEGIN(job_duration) > current_date - interval '3' month;
/* delete all future employees */
SEQUENCED VALIDTIME
DELETE employee
FROM dept
WHERE dept.deptname = 'SUPPORT' AND
dept.deptid = employee.deptid AND
BEGIN(job_duration) > TEMPORAL_DATE;
ET;
Alternatively, the following SQL would accomplish the same:
REPLACE VIEW v1 AS
NONSEQUENCED VALIDTIME
SELECT employee.eid, dept.deptid
FROM employee, dept
WHERE dept.deptname = 'SUPPORT' AND
dept.deptid = employee.deptid AND
BEGIN(job_duration) > CURRENT_DATE - interval '3' month
AND job_duration OVERLAPS PERIOD(TEMPORAL_DATE, UNTIL_CHANGED);
SEQUENCED VALIDTIME PERIOD(TEMPORAL_DATE, UNTIL_CHANGED)
DELETE employee FROM v1 WHERE v1.eid = employee.eid AND
v1.deptid = employee.deptid;
Example 13: Sequenced Delete from a Valid-Time Table
To perform a sequenced delete, use the VALIDTIME or SEQUENCED VALIDTIME qualifier in the DELETE statement.
For a table with valid time, any row with a period of validity that overlaps with the period of applicability qualifies for deletion. The delete operation may physically delete a row, logically delete a row, modify the period of validity for a row, or delete a row and create a new row.
Consider the following data in the Policy table:
NONSEQUENCED VALIDTIME
SELECT Policy_ID, Customer_ID, Validity
FROM Policy
WHERE Policy_Type = 'AU';
Policy_ID Customer_ID Validity
--------- ----------- ------------------------
497201 304779902 ('05/02/14', '06/02/13')
540944 123344567 ('07/02/03', '08/02/02')
541077 766492008 ('09/12/21', '99/12/31')
541145 616035020 ('09/12/03', '10/12/01')
541008 246824626 ('09/10/01', '99/12/31')
The following sequenced DELETE statement physically deletes one row from the table. The period of validity for policy 540944 (PERIOD '(2007-02-03, 2008-02-02)') is fully contained within the period of applicability of the sequenced delete statement (PERIOD '(2007-01-01,2008-03-01)'):
SEQUENCED VALIDTIME PERIOD '(2007-01-01, 2008-03-01)' DELETE
FROM Policy;
Example 14: Sequenced Delete from a Valid-Time Table
The following sequenced DELETE statement modifies the period of validity for one row from the table. The period of validity for policy 497201(PERIOD '(2005-02-14, 2006-02-13)') begins before and overlaps the period of applicability of the sequenced delete statement (PERIOD '(2005-11-01, 2006-08-01)'). Because only that portion of the policy that overlaps
the period of applicability of the sequenced delete statement is deleted, the period of validity for the row is modified to end when the deletion begins (2005-11-01):
SEQUENCED VALIDTIME PERIOD '(2005-11-01, 2006-08-01)' DELETE
FROM Policy;
NONSEQUENCED VALIDTIME
SELECT Policy_ID, Customer_ID, Validity
FROM Policy
WHERE Policy_Type = 'AU';
Policy_ID Customer_ID Validity
--------- ----------- ------------------------
497201 304779902 ('05/02/14', '05/11/01')
541145 616035020 ('09/12/03', '10/12/01')
541077 766492008 ('09/12/21', '99/12/31')
541008 246824626 ('09/10/01', '99/12/31')
Example 15: Sequenced Delete from a Valid-Time Table
If the period of applicability of the sequenced delete in the last example had been (PERIOD '(2005-05-01, 2005-06-01)'), such that it was smaller than, and fully contained within, the period of validity of policy 497201, the policy row would be split into two rows, preserving the validity for periods that were not deleted:
SEQUENCED VALIDTIME PERIOD '(2005-05-01, 2005-06-01)' DELETE
FROM Policy;
NONSEQUENCED VALIDTIME
SELECT Policy_ID, Customer_ID, Validity
FROM Policy
WHERE Policy_Type = 'AU';
Policy_ID Customer_ID Validity
--------- ----------- ------------------------
497201 304779902 ('05/02/14', '05/05/01')
497201 304779902 ('05/06/01', '06/02/13')
541145 616035020 ('09/12/03', '10/12/01')
541077 766492008 ('09/12/21', '99/12/31')
541008 246824626 ('09/10/01', '99/12/31')
Example 16: Nonsequenced Delete from a Valid-Time Table
A nonsequenced delete applies no special temporal logic to the delete operation or row selection, and operates on a valid-time table as a conventional delete would operate on a nontemporal table:
Start from the same valid-time table that was used for the sequenced delete examples:
Policy_ID Customer_ID Validity
--------- ----------- ------------------------
497201 304779902 ('05/02/14', '06/02/13')
540944 123344567 ('07/02/03', '08/02/02')
541077 766492008 ('09/12/21', '99/12/31')
541145 616035020 ('09/12/03', '10/12/01')
541008 246824626 ('09/10/01', '99/12/31')
Each of the following nonsequenced DELETE statements physically deletes one row from the table:
NONSEQUENCED VALIDTIME DELETE
FROM Policy
WHERE Customer_ID = 304779902;
NONSEQUENCED VALIDTIME DELETE
FROM Policy
WHERE BEGIN(Validity) = DATE '2007-02-03';
Example 17: Current or Sequenced Delete from a Bitemporal Table
The syntax of these operations is identical to the same kinds of deletions performed on validtime tables:
• To perform a current delete, use the CURRENT VALIDTIME qualifier in the DELETE statement.
• To perform a sequenced delete, use the SEQUENCED VALIDTIME qualifier in the DELETE statement. (Using VALIDTIME alone as the qualifier is equivalent.)
There are two important ways that these kinds of deletions on bitemporal tables differ from those on valid-time tables:
• Current and sequenced deletions on bitemporal tables affect only rows that are open in the transaction-time dimension.
• Because rows are physically removed from bitemporal tables only when the
NONTEMPORAL qualifier is used, rows deleted in SEQUENCED VALIDTIME are only deleted logically.
The ending bound of their transaction-time period is changed from the value of UNTIL_CLOSED to the date or timestamp of the deletion, and the row becomes closed in the transaction-time dimension. The logically deleted row becomes a history row.
The valid-time period remains unchanged for the logically deleted row.
The deleted state of the row is reflected in the ending bound of the transaction time.
However, similar to a SEQUENCED VALIDTIME DELETE on a valid-time table, if the period of validity of the original row extended beyond the period of applicability of the sequenced delete new rows are created that reflect the time periods for which the information was not deleted.
The new rows have appropriately modified valid-time periods. These new rows are open in the transaction-time dimension, because their time periods were not included in the period of applicability of the deletion.
Example 18: Nontemporal Delete from a Bitemporal Table
Performing a nontemporal delete on a bitemporal table physically deletes the specified rows. Because a nontemporal delete can be used to remove history rows from the table, the NONTEMPORAL privilege is required to perform nontemporal operations on temporal tables that have transaction time. Nontemporal deletes should be used only if absolutely necessary, and only by appropriately authorized personnel.
Example 19: Merging Nontemporal Table Data into a Bitemporal PPI Table
You can use the temporal form of the MERGE statement to merge data from a nontemporal table into a temporal table. Suppose you have the following nontemporal table called
Policy_Changes:
CREATE TABLE Policy_Changes(
Policy_ID INTEGER,
Customer_ID INTEGER,
Policy_Type CHAR(2) NOT NULL,
Policy_Details CHAR(40)
);
Suppose you also have the following bitemporal table called Policy that is partitioned according to the partitioning guidelines for a bitemporal table:
CREATE MULTISET TABLE Policy(
Policy_ID INTEGER,
Customer_ID INTEGER,
Policy_Type CHAR(2) NOT NULL,
Policy_Details CHAR(40),
Validity PERIOD(DATE) AS VALIDTIME,
Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
AS TRANSACTIONTIME
)
PRIMARY INDEX(Policy_ID)
PARTITION BY
CASE_N((END(Validity) IS NULL OR
END(Validity) >= CURRENT_DATE AT '-12:59') AND
END(Policy_Duration) >= CURRENT_TIMESTAMP,
END(Validity) < CURRENT_DATE AT '-12:59' AND
END(Policy_Duration) >= CURRENT_TIMESTAMP,
END(Policy_Duration) < CURRENT_TIMESTAMP);
The following statement performs a sequenced merge in the valid-time dimension into the Policy table from the Policy_Changes table where the period of applicability is December 1,2009 to December 7, 2009.
The matching condition is applied on open rows of the Policy table where the period of validity overlaps the period of applicability. If the matching condition is satisfied, a sequenced update is performed;
if the matching condition is not satisfied, a sequenced insert is performed.
SEQUENCED VALIDTIME
MERGE INTO Policy USING (
NONSEQUENCED VALIDTIME PERIOD (DATE'2009-12-01', DATE'2009-12-07')
SELECT
source.Policy_ID,
source.Customer_ID,
source.Policy_Type,
source.Policy_Details,
target.Validity AS vt,
END(target.Policy_Duration) AS ett
FROM Policy_Changes source LEFT OUTER JOIN Policy target
ON source.Policy_ID = target.Policy_ID
WHERE (vt IS NULL OR
((BEGIN(vt) < DATE '2009-12-07') AND
(END(vt) > DATE '2009-12-01') AND
(ett = TIMESTAMP '9999-12-31 23:59:59.999999'))
)
) AS merge_source (
PID,
CID,
PType,
PDetails,
j,
k
)
ON (Policy_ID = merge_source.PID) AND
END(Validity) = END(j) AND END(Policy_Duration) = k
WHEN MATCHED THEN
UPDATE SET Policy_Details = merge_source.PDetails
WHEN NOT MATCHED THEN
INSERT (
merge_source.PID,
merge_source.CID,
merge_source.PType,
merge_source.PDetails,
PERIOD(TEMPORAL_DATE, UNTIL_CHANGED)
Example 20: Dropping a Valid-Time Column
To drop a valid-time column from a valid-time table, use the ALTER TABLE statement.
Consider the following valid-time table:
CREATE MULTISET TABLE Customer (
Customer_Name VARCHAR(40),
Customer_ID INTEGER,
Customer_Address VARCHAR(80),
Customer_Phone VARCHAR(12),
Customer_Validity PERIOD(DATE) NOT NULL AS VALIDTIME
)
PRIMARY INDEX ( Customer_ID );
The following statement drops the Customer_Validity column:
ALTER TABLE Customer DROP Customer_Validity;
To drop a valid-time column from a bitemporal table, use the ALTER TABLE statement and specify the NONTEMPORAL qualifier.
Dropping any type of column from a bitemporal table requires the NONTEMPORAL privilege on the table, and the NONTEMPORAL qualifier to ALTER TABLE must be used.
Consider the following bitemporal table:
CREATE MULTISET TABLE Customer (
Customer_Name VARCHAR(40),
Customer_ID INTEGER,
Customer_Address VARCHAR(80),
Customer_Phone VARCHAR(12),
Customer_Validity PERIOD(DATE) NOT NULL AS VALIDTIME,
Customer_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
AS TRANSACTIONTIME
)
PRIMARY INDEX ( Customer_ID );
The following statement drops the Customer_Validity column:
NONTEMPORAL ALTER TABLE Customer DROP Customer_Validity;
When a valid-time column is dropped from a bitemporal table, all rows that are no longer valid (all history rows in the valid-time dimension) are physically deleted from the table.
Example 21: Dropping a Transaction-Time Column
Dropping any type of column from a transaction-time or bitemporal table requires the NONTEMPORAL privilege on the table, and the NONTEMPORAL qualifier to ALTER TABLE must be used.
Consider the following transaction-time table:
CREATE MULTISET TABLE Customer (
Customer_Name VARCHAR(40),
Customer_ID INTEGER,
Customer_Address VARCHAR(80),
Customer_Phone VARCHAR(12),
Customer_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
AS TRANSACTIONTIME
)
PRIMARY INDEX ( Customer_ID );
Assuming that you have the NONTEMPORAL privilege on the Customer table, the following
ALTER TABLE statement drops the Customer_Duration column:
NONTEMPORAL ALTER TABLE Customer DROP Customer_Duration;
When a transaction-time column is dropped from a transaction-time or
bitemporal table, all closed rows (all history rows in the transaction-time
dimension) are physically deleted from the table.
Views on Temporal Tables
The following examples create views on temporal tables.
Example 1
The following statement creates a sequenced view on the Policy table.
The result of the sequenced query is a valid-time table or, in this case, view.
The valid-time period for each row in the view is stored in a new column that is automatically appended by the system.
The validtime for each row in the view is the overlap of the valid-time period of the row in the original temporal table with the valid time of the sequenced query.
In this case, because a time period is not specified in the sequenced query,
the period for the query defaults to (0001-01-01,UNTIL_CHANGED), and the valid-time periods in the view will match those for the original rows.
Because names for the view columns are not specified in the CREATE VIEW statement, the system assigns the new valid-time column the name VALIDTIME.
CREATE VIEW Basic_Auto_Policy_V AS
SEQUENCED VALIDTIME
SELECT Policy_ID, Customer_ID
FROM Policy
WHERE Policy_Type = 'AU';
Example 2
The following statement creates a similar sequenced view on the Policy table but provides a list of column names that includes the extra column name “Basic_View_Validity”, which the system assigns to the new valid-time column that is appended to the view.
CREATE VIEW Basic_Auto_Policy_V (
Policy_ID,
Customer_ID,
Basic_View_Validity
) AS
SEQUENCED VALIDTIME
SELECT Policy_ID, Customer_ID
FROM Policy
WHERE Policy_Type = 'AU';
Session Temporal Qualifier
Example 1
The following statement sets the session temporal qualifier to current in the valid-time dimension:
SET SESSION CURRENT VALIDTIME;
Example 2
The following statement sets the session temporal qualifier to current in the transaction-time dimension:
SET SESSION CURRENT TRANSACTIONTIME;
Restoring a Prior Table State
Tables with transaction time automatically store in the table a snapshot copy of any row that is modified or deleted.
This characteristic of these tables can be used to recover a prior state ofthe table using only SQL This can be useful to quickly recover from a localized problem, such as if a table or set of tables have become corrupted by a user error, and need to be restored to a consistent state.
Assume a table that has transaction time needs to be restored to the state it was in at a point (time X) prior to the current time.
Rows in the table can be classified based on whether they are open or closed, and on their transaction-time column period relation to time X:
These rows are represented graphically below.
Example
To return the table back to the state it was in at time x, use the following plan:
Row Row State
BEGIN(TT)
Beginning Transaction Time
END(TT)
End Transaction Time
1 Open Prior to time X UNTIL_CLOSED
2 Open Equal to time X UNTIL_CLOSED
3 Open After time X UNTIL_CLOSED
4 Closed Prior to time X Prior to time X
5 Closed Prior to time X Equal to time X
6 Closed Prior to time X After time X
7 Closed Equal to time X After time X
8 Closed After time X After time X
1182A050
Time
X
[
4) [
5) [
)
[
)
7) )
1) UNTIL_CLOSED
| |
|
|
[
|
|
|
|
)
|
|
|
[
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Now
X Now
2) UNTIL_CLOSED
3) UNTIL_CLOSED
6) [
8) [ )
The following SQL will realize the plan for each of the different row states.
NONTEMPORAL DELETE tt_table where BEGIN(tt_col) > time X;
NONTEMPORAL UPDATE tt_table
SET tt_col = PERIOD(BEGIN(tt_col), UNTIL_CLOSED)
WHERE END(tt_col) IS NOT UNTIL_CLOSED
AND BEGIN(tt_col) <= time X
AND END(tt_col) > time X;
Note: Because this solution requires NONTEMPORAL SQL, nontemporal
operations on temporal tables must be enabled using the DBS Control utility.
Additionally, the user executing this SQL must be granted the NONTEMPORAL privilege. For more information on DBS Control see Utilities.
For more information on NONTEMPORAL operations, see Ensuring Recovered Table States Reflect Transaction Boundaries
A TRANSACTIONTIME SELECT AS OF query on a transaction-time or
bitemporal table will return the state of the table at the exact moment specified
in the AS OF qualifier.
Such queries are useful for various types of reports, such as those used in regulatory compliance.
However, transactions are not instantaneous, so the state of the table captured
at an instant in time might reflect an intermediate state, during which one or
more transactions are in progress and have only partially completed.
This is not sufficient for situations that require historical tables to reflect
transaction boundaries, after transactions have fully completed.
To guarantee that these tables do not include partial transactions, the time specified by the AS OF query must be an instant in time that reflects transaction boundaries.
The strategy for
Row
Row at Time X
Compared to Row Now Plan
1 Row existed at time X and row exists now. Leave the row as it is.
2 It existed at time X and row exists now. Leave the row as it is.
3 It did not exist at time X. Delete the row.
4 It was closed at time X, and is still closed now. Leave the row as it is.
5 It was closed at time X, and is still closed now. Leave the row as it is.
6 It was open at time X, but is closed now. Leave BEGIN(TT) as it is.
Update END(TT) to UNTIL_CLOSED.
7 It was open at time X, but row is closed now. Leave BEGIN(TT) as it is.
Update END(TT) to UNTIL_CLOSED.
8 It did not yet exist at time X. Delete the row.
capturing these times involves using transactions that obtain read locks on
tables, and noting the time immediately after these locks are achieved, before
any further table modifications have begun.
Such locks can be obtained either explicitly or implicitly, and capturing these times are demonstrated by the following techniques:
• Begin an ANSI mode transaction or a Teradata mode transaction (using the BT and ET statements) with the LOCKING request modifier to lock all tables that will be accessed from within the transaction. (For details on the LOCKING modifier, see SQL Data Manipulation Language.) This assures that all other transactions on the table have completed before this transaction commences, and so provides a convenient checkpoint at which the table is guaranteed to be at a transaction boundary.
Follow the LOCKING modifier with a SELECT CURRENT_TIMESTAMP request, and note the time value that is returned. This time value can be used with
TRANSACTIONTIME AS OF SELECT at any time in the future to return the state of the table at this time, as the table existed between transactions. Such a statement is guaranteed to always return exactly the same state of the table, as no transactions would have been in the process of modifying the table when the lock was acquired.
• Because all DML SELECT requests obtain read locks by default on any tables or rows referenced in the request, using an explicit LOCKING modifier is not necessary.
However, with implicit locking it is difficult to know exactly when all locks have been acquired.
Therefore, if using implicit locking, place the SELECT CURRENT_TIMESTAMP
statement immediately before the COMMIT or ET statement. This guarantees that the timestamp returned is a time when all locks have been acquired, ensuring that the returned timestamp reflects transaction boundaries.
Again note the time value that is returned, to be used later with an TRANSACTIONTIME
AS OF SELECT to return the state of the table at this time.
• Use a multistatement request that includes a CURRENT TRANSACTIONTIME SELECT statement and that ends with a SELECT CURRENT_TIMESTAMP statement.
The first statement implicitly acquires the appropriate read locks, and the timestamp returned by the second statement can be used in the future to reproduce a historical table state reflecting no partial transactions.
In a working database environment, such checkpoint transactions (that issue a SELECT CURRENT_TIMESTAMP statement after locks have been acquired) can be scheduled to occur automatically and periodically at times when reports will be required, such as daily or weekly.
The times returned by the CURRENT_TIMESTAMP statements can be automatically logged.
To generate the prior states of the table as required for regulatory compliance, while
ensuring that the historical tables returned reflect transaction boundaries, use
TRANSACTIONTIME AS OF queries that specify the logged timestamp values.
==========================================================
