About Differing Transaction Semantics in Different Session Modes
- ANSI
- Teradata
- 2PC
About Default Session Modes
- 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 |
|---|---|---|
| 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, B035-2414 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.
|
||
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
- 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 … |
|---|---|
|
ANSI |
|
|
| 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
- 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 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.