Coding Multiple-Statement Requests - Teradata Vantage

Multiple-statement requests are application-independent, but their behavior depends on session mode and on whether they are specified as implicit transactions or as part of an explicit transaction.

Vantage treats multiple-statement requests as single implicit transactions in Teradata session mode when no open BEGIN TRANSACTION statement precedes them.

With the exception of multiple-statement INSERT requests, Vantage treats multiple-statement requests as single recovery units only if the requests run in ANSI/ISO session mode or as implicit transactions in Teradata session mode. If statements in the transaction generate errors, the entire transaction or only the erring request is rolled back. For example, in Teradata session mode within an explicit transaction, Vantage rolls the entire transaction back to the BEGIN TRANSACTION statement, so the complete transaction is the recovery unit.

If multiple UPDATE requests are specified within one multiple-statement request inside an explicit transaction in Teradata session mode, and one fails, all are rolled back. The fewer UPDATE statements in the multiple-statement request, the lower the impact of the rollback. However, the more statements included in the request, the higher the degree of parallelism among them.

The rollback issue for UPDATE requests is not true for multiple-statement INSERT requests, where the statement independence feature can frequently enable multiple-statement INSERT requests to roll back only the statements that fail within an explicit transaction or multiple-statement request and not the entire transaction or request.

Statement independence is not enabled for multiple-statement INSERT requests into tables defined with join indiexes.

See INSERT/INSERT ... SELECT for more information about statement independence. Client data loading utilities also support statement independence. See the appropriate Teradata Tools and Utilities documentation for information about which load utilities and APIs support statement independence and at what level.