Convert Transaction-Time to System-Versioned Table with ALTER TABLE | Vantage - Method 1: Alter Existing Transaction-Time Table - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™
This method:
  • requires that the executor have the NONTEMPORAL privilege in the database, and that the database be enabled to recognize that privilege. Read more about the NONTEMPORAL privilege in Teradata Vantage™ - Temporal Table Support, B035-1182.
  • cannot be used if the transaction-time table is row partitioned on the beginning or ending bound of the transaction-time.
  • is not recommended for large, column-partitioned tables because for these tables the update operation in Step 4 can be very resource-intensive and time-consuming.
  1. Note all the constraints on the transaction-time table.
  2. Drop all the constraints from the transaction-time table.
  3. Use NONTEMPORAL ALTER TABLE to add two new columns of type TIMESTAMP(6) WITH TIME ZONE. For the purposes of this procedure, assume the columns are named sys_start and sys_end. These will hold the beginning and ending bound values of the new SYSTEM_TIME derived period column.
  4. Use NONTEMPORAL UPDATE to populate the new columns with the start and end values of the existing transaction-time columns or derived period column.
  5. Use NONTEMPORAL ALTER TABLE to drop the existing transaction-time column. Use the WITHOUT DELETE option to preserve the historical closed rows, which would otherwise be deleted automatically when you drop the transaction-time column:
    ALTER TABLE  transaction_time_table_name  
      DROP  transaction_time_column  WITHOUT DELETE
  6. Use ALTER TABLE to create the SYSTEM_TIME derived period column and to add attributes to the set the sys_start and sys_end columns in the same ALTER TABLE statement:
    ALTER TABLE  transaction_time_table_name
     ADD PERIOD FOR SYSTEM_TIME(sys_start,sys_end) 
     ADD sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL 
       GENERATED ALWAYS AS ROW START 
     add sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL 
       GENERATED ALWAYS AS ROW END;
  7. Add system versioning to make the new table an ANSI system-time temporal table:
    ALTER TABLE  transaction_time_table_name  
     ADD SYSTEM VERSIONING;
  8. Recreate all the constraints that were dropped in step 2. Note that ANSI constraints behave as NONSEQUENCED constraints.