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

Teradata Vantageā„¢ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K
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.