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:
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.