Transaction Semantics Differences in ANSI and Teradata Session Modes | Vantage - Transaction Semantics Differences in ANSI and Teradata Session Modes - Analytics Database - Teradata Vantage

SQL Request and Transaction Processing
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-10-04
dita:mapPath
zfm1628111633230.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
evd1472255317510
lifecycle
latest
Product Category
Teradata Vantage™
You can perform transaction processing in any of the following session modes:
  • ANSI
  • Teradata
  • 2PC

Default Session Modes

When you upgrade an existing database, set the system default to Teradata session mode to provide compatibility for existing users and applications.

New customers can set the system default to ANSI session mode.

The default session mode for a session follows the system default set for that installation. The default mode can be overridden through use of the session options parcel which is submitted to the system with the connect or the logon/run parcel sequence.

Changing Session Modes

To change the mode for a session using client software based on the CLIv2 API, do any of the following:

Client Software USE These Commands or Options Session mode That You Switch To
BTEQ 
.[SET] SESSION TRANSACTION ANSI
 ANSI 
.[SET] SESSION TRANSACTION BTET
 Teradata
Log off the current Teradata session before you enter this BTEQ command. You must submit this command before you log on to a Teradata session.

See Basic Teradata® Query Reference, B035-2414 for more detail on using BTEQ commands.
Preprocessor2 
TRANSACT(ANSI)
 ANSI 
TRANSACT(BTET)
TRANSACT(2PC)
TRANSACT(COMMIT)
 Teradata
COMMIT, the default option, follows Teradata transaction semantics with the exception of allowing the ANSI session mode-only COMMIT request to be used to terminate transactions.
CLIv2 
set tx_semantics = 'A'
 ANSI 
set tx_semantics = 'T'
 Teradata 
set tx_semantics = 'D'
 Platform default
See the following manuals for more detail on setting the tx_semantics field.
  • Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417
  • Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418

See the relevant product documentation for client software based on the ODBC API to determine how to switch session modes using that software. Also see Teradata Vantage™ - SQL Fundamentals, B035-1141 for information about changing session modes.

Terminating Transactions

An application-initiated asynchronous abnormal termination causes full transaction rollback in both ANSI and Teradata session modes. Such a request is generated by any of the following commands.
  • CLIv2 ABORT request
  • TDP when the application terminates without proper session cleanup
  • BTEQ with .ABORT

In both Teradata and ANSI session modes, implementation includes the rule that if a session is terminated with an open transaction, then any effects of that transaction are rolled back, the Transient Journal is dropped, and any open cursors are closed.

In ANSI session mode, request errors do not cause a rollback of the transaction, only of the request that causes them. The system does not arbitrarily close a transaction unless its termination is required to preserve the integrity of the database.

In ANSI session mode, errors such as constraint violations on an INSERT or UPDATE request roll back only the current request, not the offending transaction.

Mode-Specific SQL Statement Restrictions

Except for the following statements, all SQL statements are valid in both session modes:

SQL Statements Invalid Session Mode
  • BEGIN TRANSACTION
  • BT
 ANSI
  • END TRANSACTION
  • ET
COMMIT [WORK] Teradata

Exception: You can submit COMMIT requests to terminate transactions from Preprocessor2 applications in Teradata session mode if you specified the Preprocessor2 TRANSACT(COMMIT) option to log on to the database.

Other Session Mode-Specific Features and Restrictions

The following additional session mode-specific features and restrictions apply to transaction processing:
  • Session Pool Manager

    The Session Pool Manager permits setting the Teradata or ANSI session mode for pooled sessions.

  • Two-Phase Commit (2PC) Protocol

    Vantage supports the 2PC protocol (see Teradata Vantage™ - Database Introduction, B035-1091 for a brief description of 2PC) for Teradata session mode, but not for ANSI session mode.

    If you attempt to use the 2PC protocol while in ANSI session mode, the logon process aborts and a failure response is returned.