All Forums Database
taruntrehan 43 posts Joined 10/12
02 Oct 2013
3932: Only an ET or null statement is legal after a DDL Statement.

Hi All,
I am writing a code to change PI of an existing table.

  1. I am creating a backup table with new PI.
  2. Copying data from existing table
  3. Drop Old Table
  4. Rename backup table to original table.

 
Following is the code snippet : 

create table person_bck (person_id bigint not null , person_name varchar(100)) primary index (person_id);

insert into person_bck (person_id , person_name ) select person_id , person_name from person;

drop table person;

rename table person_bck to person;

 
This code executes fine but fails with the following error when i surround it with bt - et.
Statement 2: INSERT Failed. 3932:  Only an ET or null statement is legal after a DDL Statement. 
I want to write this code as part of a single transaction; if any of the statement fails, it rolls back everything.
Some posts suggested to commit after the create table command and end transaction. then do rest work in another transaction.
However, in that case the rollback of create table will not happend because its a separate unit altogether.
 
How can i achieve this in a single atomic transaction unit?

Regards, Tarun Trehan http://allzhere.in
M.Saeed Khurram 544 posts Joined 09/12
03 Oct 2013

In BTET mode each individual statement is treated as a transaction, to combine multiple statements in transaction you need to use BTET. In case of DDL it is a requirement that each DDL statement must be followed by a COMMIT in case of ANSI mode. but in Teradata Mode I didn't find any such restriction. Might be changing the transaction mode will work for you. Else you will have to use the ET after each DDL to accomplish this. Or you can use some procedure to accomlish the task in entirety. 
 

Khurram

Sun_shine_jgd 39 posts Joined 07/13
03 Oct 2013

Put
 
BT;
<UR LOGIC>
ET;
 
IF any thing fails in between.it rolls back
 

taruntrehan 43 posts Joined 10/12
03 Oct 2013

Saeed, i got the error in TD mode.
But, i got the point.
Thanks.

Regards,
Tarun Trehan
http://allzhere.in

You must sign in to leave a comment.