Teradata Session Mode Transaction Processing Case Studies - Analytics Database - Teradata Vantage

This topic presents case studies concerning Teradata session mode transaction semantics. Also see ANSI Session Mode Transaction Processing Case Studies).

Failed Teradata Session Mode Transaction Example

BTEQ -- Enter your DBC/SQL request or BTEQ command:
BEGIN TRANSACTION;

*** Begin transaction accepted.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO employee
SELECT *
FROM customer_service.employee;

*** Insert completed. 26 rows added.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
FRM employee
WHERE empnum = 401;

*** Failure 3706 Syntax error; SELECT * must have a FROM clause.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
FROM employee;

*** Query completed. No rows found.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
END TRANSACTION;

*** Failure 3510 Too many END TRANSACTION statements.

Teradata Session Mode Requests

DELETE FROM table_1
WHERE PI_col=2;
INSERT INTO table_1
VALUES (2,3,4);
UPDATE table_1
SET col_3=4;

BEGIN TRANSACTION;
DELETE FROM table_1
WHERE PI_col=2;
INSERT INTO table_1
VALUES (2,3,4);
UPDATE table_1
SET col_3=4;
END TRANSACTION;

DELETE FROM table_1
WHERE PI_col=2
;INSERT INTO table_1
VALUES (2,3,4)
;UPDATE table_1
SET col_3=4;

CREATE MACRO mac_1 AS (
DELETE FROM table_1
WHERE PI_col=2;
INSERT INTO table_1
VALUES (2,3,4);
UPDATE table_1
SET col_3=4;
);

EXEC mac_1;

EXPLAIN EXEC mac_1
EXPLAIN DELETE FROM table_1
WHERE PI_col=2
;INSERT INTO table_1
VALUES (2,3,4)
;UPDATE table_1
SET col_3=4;

Mixing DDL and DML Statements within a Multistatement Request

You cannot mix DDL and DML statements within the same macro or multistatement request in Teradata session mode. An attempt to perform such a request results in a failure response.

For example, the following multistatement request mixes DML (2 SELECT requests) with DDL (a CREATE TABLE request) and therefore, fails:

     SELECT *
     FROM table_1
     ;SELECT *
     FROM table_1
     ;CREATE TABLE table_33 (
       col_1 INTEGER);

Result:

     *** Failure 3576 Data definition not valid unless solitary.
               Statement#1, Info =0

The equivalent macro text results in the same failure at the time that you attempt to create the macro.

     CREATE MACRO mac_1 AS (
     SELECT *
     FROM table_1;
     SELECT *
     FROM table_1;
     CREATE TABLE table_33 (
       col_1 INTEGER);
     );

Result:

     *** Failure 3576 Data definition not valid unless solitary.
               Statement#1, Info =0

A DDL statement within a Teradata session mode transaction must be the last action statement in the transaction. Otherwise, the transaction fails and all its work is rolled back. For example:

BEGIN TRANSACTION;

Result:

*** Begin transaction accepted.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE TABLE table_19 (
  col_1 INTEGER);
*** Table has been created.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO table_3
VALUES (1);
*** Failure 3932 Only an ET or null statement is legal after a DDL statement.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
SHOW TABLE table_19;
*** Failure 3807 Table/view/trigger/procedure ‘table_19’ does not exist.

Teradata Session Mode DELETE Performance for Different Transaction Structures

Depending on its structure, a transaction that contains a DELETE request can create a Transient Journal entry for each row deleted from a table or create only one Transient Journal entry for the entire transaction.

The following DELETE request is a single implicit transaction, which does not write a Transient Journal entry for each deleted row, and therefore its performance is good.

DELETE FROM table_1;

The following explicit transaction contains only BEGIN TRANSACTION and END TRANSACTION requests in addition to the DELETE request. Because of its structure, the transaction writes a Transient Journal entry for each deleted row and performs poorly, especially for large tables.

BEGIN TRANSACTION;
DELETE FROM table_1;
END TRANSACTION;

The following multistatement request contains the same three requests as the previous transaction, but the requests are packaged as a multistatement request and are therefore treated as an implicit transaction. Vantage does not write a Transient Journal entry for each row deleted from the table, and its performance is identical to that of the single-statement implicit transaction version.

BEGIN TRANSACTION
;DELETE FROM table_1
;END TRANSACTION;

In the first case, the system knows what the next request is and prepares to roll back the transaction. In the second case, the system sees that the delete is to be committed.