I am writing a code to change PI of an existing 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?
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.
RazorSQL seems to submit all statements as a Multi Statement Request (i.e. all at once), check if there's a way/setting to submit them individually (like F5/F9 in SQL Assistant/Studio).
I was able to figure it out by using TMODE=TERA,CHARSET=ASCII in my JDBC URL. However, now I am getting the following error:
ERROR: [Teradata Database] [TeraJDBC 16.10.00.07] [Error 3807]
[SQLState 42S02] Object 'midlife_a' does not exist. Error Code:
I have tried deselecting the option that ignores error but it doesn't seem to be working...
It is interesting that another ODBC tool is giving out quite similar error messages to what is syntactically correct SQL statements and statement blocks. In another post, I mentioned that I was having the same issues. I see some similar posts from earlier in the past too.
You mentioned Single vs. Multiple statements. Another user mentioned TMODE=TERA option.
How can I control these options? What options are available from an ODBC tool to change these settings? Do we need to set something up in the database server once, or each time after establishing a session, or something completely different?
Thanks for your help.