ANSI Mode Transactions Teradata

ANSI mode transactions work the opposite of Teradata mode in the way they are controlled. It assumes that all SQL statements are part of a single transaction. The user must request the end of a transaction and commit the work to disk in order to save the work, release all held locks and delete the before images from the Transient Journal.

The following command must be used to successfully end an ANSI transaction (single or multi-step command):

COMMIT WORK;

This command requests that, if the outcome of the statement is successful, the work is committed to the database. This is particularly important when data is being changed. Otherwise, if the user never commits the work and logs off, the completed work is automatically be rolled back from the Transient Journal, like it never happened.

To perform the same transaction above using BTEQ in ANSI mode, the following commands can be used:

Again, in batch mode it is still important to use the .if to check the outcome. This is especially true in ANSI mode due to its perception of a transaction. ANSI tends to commit all modifications that work and rollback only the individual statements that failed. This ANSI definition is definitely different than my perception of a transaction. But now you know how it works too.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics