SESSION TRANSACTION

Basic Teradata Query Reference

brand
Teradata Tools and Utilities
prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

Purpose

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

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 Teradata Database 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 Teradata 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 details on running in ANSI versus BTET (also referred to as Teradata mode) mode, see SQL Fundamentals (B035-1141).

The SESSION TRANSACTION command cannot be used in a Teradata 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.