15.10 - Transaction Semantics Differences in ANSI and Teradata Session Modes - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

You can perform transaction processing in any of the following session modes.

  • ANSI
  • Teradata
  • 2PC
  • Teradata session mode ensures compatibility with legacy applications, although it introduces a few caveats.

  • When you upgrade an existing Teradata 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.

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

     

    FOR this client software …

    USE these commands or options …

    TO switch to this session mode …

    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 onto a Teradata session.

    See Basic Teradata Query Reference for more detail on using BTEQ commands.

    Preprocessor2

    TRANSACT(ANSI)

    ANSI

    TRANSACT(BTET)
    TRANSACT(2PC)
    TRANSACT(COMMIT)

    Teradata

    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.

    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
  • Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems
  • 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 SQL Fundamentals for information about changing session modes.

    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.

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

     

    These SQL statements …

    Are not valid in this session mode …

  • BEGIN TRANSACTION
  • BT
  • ANSI

  • END TRANSACTION
  • 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 Teradata Database.

    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
  • Teradata Database supports the 2PC protocol (see Introduction to Teradata 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.