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.
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.
There is more transactional information in the next chapter of this book.