Proper Positioning for Session Mode and Transaction Type - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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.