SESSION TRANSACTION Command | Basic Teradata Query - 17.00 - SESSION TRANSACTION - Basic Teradata Query

Basic Teradata® Query Reference

prodname
Basic Teradata Query
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-2414-220K

Purpose

This control enables users to specify whether transaction boundaries are determined by Teradata SQL semantics or ANSI standards.

Syntax


SESSION TRANSACTION syntax

where the following is true:

ANSI
ANSI standards, with implicit transaction start and explicit transaction end by COMMIT or ROLLBACK.
BTET
Teradata SQL BEGIN TRANSACTION and END TRANSACTION semantics.

BTET mode as described here is also referred to as Teradata mode elsewhere.

Usage Notes

Use the SHOW CONTROLS command to see the current setting of the TRANSACTION option. See SHOW CONTROLS.

Before changing the SESSION TRANSACTION setting, all database user sessions must be logged off. Otherwise, the current setting remains in effect and BTEQ displays the following warning message:

*** Warning: You must not be logged on to change the SQLFLAG or TRANSACTION settings.

After a successful logon, BTEQ displays:

*** Transaction semantics are "%s"

where the “%s” string is BTET or ANSI, depending on the database default value and any specification used with the SESSION TRANSACTION command.

In addition to defining transaction boundaries, setting the TRANSACTION option also affects the following functions:
  • Truncation rules for character string assignments
  • Default comparison rules for character strings
  • Default of SET or MULTISET for CREATE TABLE statement. The default is SET in Teradata mode, and MULTISET in ANSI mode.

For more information on running in ANSI versus BTET (also referred to as Teradata mode) mode, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

The SESSION TRANSACTION command cannot be used in an SQL macro.

Example 1 – SESSION TRANSACTION

To specify transaction boundaries according to Teradata SQL semantics, type:

.set session trans BTET;

Example 2 – SESSION TRANSACTION

To specify transaction boundaries according to ANSI standards, type:

.set session trans ANSI:
Both the SET and SESSION keywords can be omitted when entering this command.

BTEQ Response

Unless BTEQ encounters an error condition, there is no display response to the SESSION TRANSACTION command.