15.10 - Teradata Session Mode Transaction Processing Case Studies - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

This topic presents several case studies concerning Teradata session mode transaction semantics. Also see “ANSI Session Mode Transaction Processing Case Studies” on page 659).

 
 

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 fails because it mixes DML (2 SELECT requests) with DDL (a CREATE TABLE request).

     SELECT *
     FROM table_1
     ;SELECT *
     FROM table_1
     ;CREATE TABLE table_33 (
       col_1 INTEGER);
 
     *** 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);
     );
 
     *** Failure 3576 Data definition not valid unless solitary.
               Statement#1, Info =0

If you include a DDL statement within a Teradata session mode transaction, it must be the last action statement in the transaction. If it is not, the transaction fails and all its work is rolled back. For example.

BEGIN TRANSACTION;
 
*** 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.

 

Depending on how you structure a transaction that contains a DELETE request, it can either 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. It does not write a Transient Journal entry for each deleted row, so its performance is quite 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 the way it is structured, 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 because they are packaged as a multistatement request, they are treated as an implicit transaction. Teradata Database 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 that it needs to be prepared to roll back the transaction. In the second case, the system can see that the delete is to be committed, and therefore the system knows that it does not need to roll back the transaction.