Error Dropping a Column in a TRANSACTIONTIME Table

Database
Win
Teradata Employee

Error Dropping a Column in a TRANSACTIONTIME Table

Using Teradata Database 14.0.5 in a Virtual Machine and testing the following steps specified in a reference document, "Temporal Table Support" (Release 14.0) page 36 "Converting Period or DateTime Columns to a Transaction-Time Column", an error was encountered in an ALTER TABLE step to DROP the old PERIOD column: (Is there a missing step that will prevent the error?)

STEPS:

  1. Note all the constraint information on the original table.
  2. Drop all the constraints on the original table.
  3. Grant NONTEMPORAL privilege to the user on the table.
  4. ALTER TABLE to add a new transaction-time column.
  5. Submit NONTEMPORAL UPDATE to set the new transaction-time column with the

    existing column value being converted.
  6. ALTER TABLE to drop the existing Period column or two DateTime columns from the

    table. - An error was encountered in this step.

QUERY USED:

-- Create sample nontemporal table
CREATE MULTISET TABLE Policy(
Policy_ID INT,
Customer_ID INT,
Policy_Type CHAR(2) NOT NULL,
Policy_Details CHAR(40),
TransactionDateTime PERIOD(TIMESTAMP(6) WITH TIME ZONE)
) PRIMARY INDEX(Policy_ID);

-- Insert sample records
INSERT INTO Policy (Policy_ID, Customer_ID, Policy_Type, Policy_Details, TransactionDateTime)
VALUES (541008, 246824626, 'AU', 'STD-CH-345-NXY-00', PERIOD(TIMESTAMP '2009-10-01 00:00:00.000000+00:00', TIMESTAMP '2010-11-11 00:00:00.000000+00:00'));
INSERT INTO Policy (Policy_ID, Customer_ID, Policy_Type, Policy_Details, TransactionDateTime)
VALUES (1, 1, 'DU', 'DUMMY', PERIOD(TIMESTAMP '1900-01-01 00:00:00.000000+00:00', TIMESTAMP '1911-11-11 00:00:00.000000+00:00'));

-- Add new TRANSACTIONTIME column
ALTER TABLE Policy
ADD TransactionDateTime_NEW PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL TRANSACTIONTIME;

-- Set EnabNonTempoOp (Field Group: GENERAL; Field#: 55) in DBS Control to TRUE
GRANT NONTEMPORAL ON Policy TO TemporalUser; -- GRANTed by a USER owner of a database object

-- UPDATE the new TRANSACTIONTIME column with the existing PERIOD column value
NONTEMPORAL
UPDATE Policy
SET TransactionDateTime_NEW = TransactionDateTime;

-- DROP the existing PERIOD column (With Error)
ALTER TABLE Policy
DROP TransactionDateTime;

ERROR:

ALTER TABLE Failed. 9328: History rows in a table with TransactionTime cannot be modified.

Please see attached screenshot.

Thank you in advance for any responses.

1 REPLY
Win
Teradata Employee

Re: Error Dropping a Column in a TRANSACTIONTIME Table

Hi Self :),

This has been resolved. For STEP 6, also use  NONTEMPORAL keyword for the ALTER TABLE statement.

Best regards,

Win