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 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.
- 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:
BTEQ Response
Unless BTEQ encounters an error condition, there is no display response to the SESSION TRANSACTION command.