ANSI vs TERA auto commit in Studio v14

Teradata Studio
Fan

ANSI vs TERA auto commit in Studio v14

Hi All

I'm experience database/data warehouse developer but new to Teradata.

I'm trying to work out how the behaviour of TERA vs ANSI autocommit in studio.

When using ANSI "BEGIN TRANSACTION;" statement generates an error. Why is this? I always thought ANSI standard allows nested transactions e.g:

BEGIN TRANSACTION --start explicit user transaction

<DML statement> --implicit transaction committed but still inside parent tran

<DML statement> --implicit transaction committed but still inside parent tran

COMMIT TRANSACTION --commit explicit user transaction and make all DML statement permanent.

If a ROLLBACK was issued instead of the COMMIT it would undo all work bu the nested implicit transaction.

If I switch the TMODE to TERA the "BEGIN TRANSACTION" is allowed but if I try the following:

--1st batch:

BEGIN TRASNAQCTION;

DROP TABLE t1;

--2nd batch

SELECT * FROM t1;

The SELECT statement  throws an error "Only an ET or NULL statement is legal after a DDL statement" (I'm assuming "ET" means End Transaction). Why is this? THis prevents me from creating a script/batch that CREATE/DROP/ALTER objects within the trasnaction.

As I said new to TD so happy to be enlightened if I'm missing the point.

Thanks in advance.

Steve

3 REPLIES
Senior Apprentice

Re: ANSI vs TERA auto commit in Studio v14

Hi Steve,

in fact there's no BEGIN TRANSACTION in Standard SQL, a transaction is automatically opened by the first statement within a session until it's COMMITed (there's a SAVEPOINT statemant which is similar to nested transactions).

But neither ANSI nor TERA mode support nested transactions in Teradata.

In ANSI mode a transaction is automatically opened until you COMMIT, in TERA mode by default each request is a transaction and multiple request can be bundled using BEGIN/END TRANSACTION. In both modes ROLLBACK/ABORT cancles the transaction.

A DDL statement must be the last statement within a transaction and commited, i.e. no two DDLs in the same transaction. When you DROP or ALTER a (potentially huge) table Teradata wants to know it's for shure :-)

What are you exactly trying to achieve?

Dieter

Fan

Re: ANSI vs TERA auto commit in Studio v14

Hi Dieter

Thanks for the info - very useful & sorry for the delay - been on hols then side tracked.

We quite often have to modify existing data in Teradata as corrections are made in the business.

I always get nevous doing this as some of the tables we touch are pretty hefty. If I was working on SQL Server I'd do something this:

BEGIN TRAN;

<UPDATE statement>;

<Various SELECT statements to to check correct records touched in the correct way>;

COMMIT --*IF* happy

ROLLBACK --If any issues suspected

I might hold the transaction open for some time (I realise this will block the table) whilst I write & run various SELECT statements to test the validity of the write operation. Hwn I'm happy I run the seperate "COMMIT" batch making the changes permanent. Any doubts and I can run a seperate "ROLLBACK" undoing all changes.

Is this possible TD Studio? From your explaination sounds like I should switch the connection properties to ANSI then do something like (with no explicit "BEGIN TRAN" as not applicable):

<UPDATE statement>;

<Various SELECT statements to to check correct records touched in the correct way>;

COMMIT --*IF* happy

ROLLBACK --If any issues suspected

Teradata Employee

Re: ANSI vs TERA auto commit in Studio v14

@Mover, To help you with editing table data, I wanted to point out our new Table Data Editor in our 14.10 version of Studio and Studio Express. It allows you to extract the data before you perform the update so you can archive it for future reference. You can filter the table data for large tables that you want to just look at certain rows or columns. Check out the Table Data Editor section in the Teradata Studio Developer Exchange article. (http://developer.teradata.com/tools/articles/teradata-studio#DataEditor)