DDLs are auto commit. I remember vaguely in one project where I had a unix script , where we switch between btet and ANSI. It was challenging. We segregated the DMLs as per the business requirment. In ANSI, you can think of stuffing your works with commit, rollback and savepoint.
You can use:
.set session transaction ANSI
You can also think of BTET.
By default in BTEQ every statment is auto commit. untill we specify .set session transaction ANSI before loggin.
By seeing your script you didn't use ANSI mode so it should be defualt BTET and so no commit requreid. Still if it is not happening because of DBS control settings, you may try below -
.SET SESSION TRANSACTION BTET
<your remaining script with no commit statments>
SQL Assistant automatically adds a COMMIT to each request in an ANSI session (you can easily verify that when you look at DBQL) unless you use .BeginTx/.CommitTx
BTEQ never autocommits, if you don't specify SET SESSION TRANSACTION to set the session mode BTEQ will use the system default mode, which seems to be ANSI in your case.
- add COMMIT to each CREATE (you could do a Search & Replace to simplify it)
- use SQL Assistant to submit the script (there's a command line mode, too)
- switch to BTET mode, but as this results in different defaults for SET/MULTISET and [NOT] CASESPECIFIC this might be even more work than adding COMMIT
Hi, I have noticed that when using .Net in SQL Assistant and ANSI mode, that a replace view adds a COMMIT WORK to the end of the definition of the view. This then results in a failure if you call that view from another view. This does not occur if using ODBC in ANSI mode.
Are there any settings that can be changed to prevent this COMMIT WORK from being added to the view definition?
I have found this with Teradata SQL Assistant 13.10 and 15.0.
It sounds like a bug in the Teradata Database.
However as a workaround, you can use the SQLA Transaction statements: .BeginTx , .CommitTx and .RollbackTx .
In this case it will be something like
Replace View ...
Refer to the SQL-A on-line help for additional information.