Transaction Semantics Differences in ANSI and Teradata Session Modes | Vantage - 17.10 - Transaction Semantics Differences in ANSI and Teradata Session Modes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
English (United States)

About Differing Transaction Semantics in Different Session Modes

You can perform transaction processing in any of the following session modes:
  • ANSI
  • Teradata
  • 2PC

About Default Session Modes

Teradata session mode ensures compatibility with legacy applications, although it introduces a few caveats.
  • When you upgrade an existing database, you might want to set the system default to Teradata session mode to provide compatibility for existing users and applications.
  • New customers should consider setting 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
Log off the current Teradata session before you enter this BTEQ command. You must submit this command before you log onto a Teradata session.

See Basic Teradata® Query Reference, B035-2414 for more detail on using BTEQ commands.

COMMIT is the default option. It follows Teradata transaction semantics with the exception that it permits the ANSI session mode-only COMMIT request to be used to terminate transactions.
set tx_semantics = 'A'
set tx_semantics = 'T'
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 abort 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 do not roll back an offending transaction, they only roll back the current request.

Mode-Specific SQL Statement Restrictions

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

These SQL statements … Are not valid in this session mode …
  • BT
  • ET
COMMIT [WORK] Teradata

An exception is that 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 onto 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.