ANSI Vs Teradata Transactions Teradata

Remember that the Teradata RDBMS has the ability to execute all SQL in either Teradata mode or in ANSI mode. This makes a slight difference in what code is required to guarantee that all changes made to the rows are permanently kept.

In Teradata mode, all SQL commands are implicitly a complete transaction. Therefore, once a change is made, it is committed and becomes permanent. It contains an implied COMMIT or an explicit END TRANSACTION (ET).

In ANSI mode, just the opposite is true. All SQL commands are considered to be part of the same logical transaction. A transaction is not complete until an explicit COMMIT is executed.

Therefore, each of the DML commands in ANSI mode needs to perform the following command to permanently store the data, and more importantly, release the write locks that are currently held:

COMMIT WORK;

As an example, to remove all rows, both statements below can be needed in ANSI mode.
DELETE FROM My_table; COMMIT WORK;

Without a COMMIT WORK, it is likely that the DELETE will abort and all the rows will be put back. The major downside to this technique is that the Fast Path DELETE is no longer allowed because there is potential that the rows might need to be put back into the table if something fails. Therefore, they must be logged.

Since a macro is always a transaction, it could still be used to improve performance.


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

Teradata Topics