15.00 - Modifying Temporal Tables - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

Modifying Temporal Tables

The following examples demonstrate basic modifications to temporal tables.

Example : 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-time column.

   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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : Update a Row in a Valid-Time Table

Assume an insurance customer has a basic homeowner’s policy begin on February 3, 2007, which is valid and renewed automatically unless the policy holder requests a change. The full information, including the period of validity for the customer’s policy (in the Policy_Term column) is shown by the following query:

NONSEQUENCED VALIDTIME SELECT * FROM Policy WHERE Policy_ID=540944;
 Policy_ID  Customer_ID  Policy_Type  Policy_Details        Policy_Term
----------  -----------  -----------  --------------------  ------------------------
    540944    123344567  HM           STD-PL-332-YXY-01     ('07/02/03', '99/12/31')

Now assume that the customer requests after a year that the policy be upgraded to a premium homeowner’s policy. The update statement specifies a period of applicability for the change, which would be from the day of the change, February 3. 2008, indefinitely again, until the customer requests further changes or cancels the policy. The following statement would make the required update to the customer policy:

SEQUENCED VALIDTIME PERIOD '((2008-02-03, UNTIL_CHANGED)'
UPDATE POLICY SET Policy_Type='HP'
WHERE Policy_ID=540944;

The result will be two rows in the table for this Policy_ID:

  • a history row showing the initial policy with the Policy_Term column (the valid-time column) showing an ending date corresponding to when the change was made
  • a row reflecting the current policy, with valid time starting on the date the change was made

  • NONSEQUENCED VALIDTIME SELECT * FROM Policy where Policy_ID=540944;
      Policy_ID  Customer_ID  Policy_Type  Policy_Details      Policy_Term
    -----------  -----------  -----------  ------------------- ------------------------
         540944    123344567  HP           STD-PL-332-YXY-01   ('08/02/03', '99/12/31')
         540944    123344567  HM           STD-PL-332-YXY-01   ('07/02/03', '08/02/03')

    For a nontemporal table, using nontemporal semantics, the Policy_Type value in the row would have simply been replaced, with no history row left in the table to show how the policy had existed prior to the change.

    Example : 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 : 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 : 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 : 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 : 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 : 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 : 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 : Current or Sequenced Delete from a Bitemporal Table

    The syntax of these operations is identical to the same kinds of deletions performed on valid-time 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 : 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 : Merging Nontemporal Table Data into a Row-Partitioned Bitemporal Table

    You can use the temporal form of the MERGE statement to merge data from a nontemporal table into a primary-indexed 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 row 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 : 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 : 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.