17.05 - Proper Positioning For the Session Mode and Transaction Type - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

Because fastpath delete processing defers the delete until a transaction ends and because it allows rollback and delete processing to be almost instantaneous, make sure that the DELETE or DELETE ... ALL statements are positioned properly based on the active session mode and the transaction type.

Session mode  Transaction Type Positioning Requirements
ANSI ANSI The DELETE … ALL or unconditional DELETE must be in the same multistatement request that contains a COMMIT.

For example,

DELETE FROM table_name ALL
;COMMIT;
Teradata Implicit

(such as a multistatement macro or BTEQ request)

The DELETE … ALL or unconditional DELETE must be the last statement that references the target table in the request.
Explicit The DELETE … ALL or unconditional DELETE must be in the same multistatement request that contains the END TRANSACTION that terminates the currently open transaction.

For example,

BEGIN TRANSACTION;
DELETE FROM table_name ALL
;END TRANSACTION;

This is not valid for embedded SQL applications because the DELETE and END TRANSACTION statements must fall into the same request, and embedded SQL does not support multistatement requests.