ANSI Mode in SQL Assistant

Tools & Utilities
Teradata Employee

ANSI Mode in SQL Assistant

Hi Fellow Teradata Developer,

I have a question on how to set Ansi mode on Teradata SQL assistant, I hope you can Shed some light on this.
When I'm working on BTEQ, on the same Database, I run the command .SET TRANSACTION MODE ANSI, and everything seems to work properly. So It's not an issue related with the Database.

I want to work with the same mode on TD SQL Assistant; googling around I found a suggestion on setting the mode on the option property of the ODBC, and so it's what I did. Logging on with the ODBC driver modified and using the command HELP SESSION I get for TRANSACTION SEMANTICS the value ANSI.

But, when I try to issue a Rollback, a get the following message:

ROLLBACK Failed 3514: User-generated transaction ABORT

instead of

**** Failed 3514: User-generated transaction ABORT

expected when Ansi mode is working. Checking with other session I can see my DML commited on the fly.
Below the version of the software I'm using:

Teradata Sql Assistant Version: 13.0.0.12
Database Version: 12.00.0318
ODBC Version: 13.00.00.16

Thanks beforehand, I looked everywhere for a Clue, but still no one :(

Pierluca
2 REPLIES
Teradata Employee

Re: ANSI Mode in SQL Assistant

SQL Assistant does not support explicit transaction in ANSI mode because ODBC specification requires Auto-Commit mode by default; and furthermore there is no SQL command to open an Explicit Transaction in ANSI mode. In summary SQL Assistant supports Explicit Transaction in Teradata Mode using "Begin Transaction", "End Transaction" and of course "Rollback" commands.

Currently SQL Assistant in ANSI mode will always auto-commit; hence no access to transaction spanning multiple requests. A multi-statement request (i.e. "Execute Parallel" in SQL Assistant) can be used in some scenarios in ANSI mode to workaround this issue.
Teradata Employee

Re: ANSI Mode in SQL Assistant

Thanks a million, It was helpful.