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.
- Note all the constraints on the transaction-time table.
- Drop all the constraints from the transaction-time table.
- 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.
- Use NONTEMPORAL UPDATE to populate the new columns with the start and end values of the existing transaction-time columns or derived period column.
- 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
- 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; - Add system versioning to make the new table an ANSI system-time temporal table:
ALTER TABLE transaction_time_table_name ADD SYSTEM VERSIONING; - Recreate all the constraints that were dropped in step 2. Note that ANSI constraints behave as NONSEQUENCED constraints.