Usage Notes - Advanced SQL Engine - Teradata Database

Definition and Termination of ANSI Transactions

In ANSI session mode, the first SQL request in a session initiates a transaction. The transaction is terminated by issuing either a COMMIT or a ABORT/ROLLBACK statement. Request failures do not cause a rollback of the transaction, only of the request that causes them.

Explicit ABORT

There are no implicit transactions in ANSI session mode. More accurately, each ANSI transaction is initiated implicitly, but always completed explicitly. A COMMIT or ABORT/ROLLBACK must always be explicitly stated and be the last statement of a transaction in order for the transaction to terminate successfully.

In ANSI session mode, you must issue an ABORT/ROLLBACK (or COMMIT) even when the only statement in a transaction is a SELECT or SELECT AND CONSUME.

For a SELECT statement, there is no difference between issuing a COMMIT or an ABORT/ROLLBACK.

In the case of a SELECT AND CONSUME, there is a difference in the outcomes between issuing a COMMIT or ABORT/ROLLBACK because an ABORT or ROLLBACK statement reinstates the subject queue table of the statement to its former status, containing the rows that were pseudo-consumed by the aborted SELECT AND CONSUME statement.

ABORT and ROLLBACK Are Synonyms

With the exception of the absence of the WORK keyword, ABORT is a Teradata synonym for the ANSI-compliant ROLLBACK statement.

Actions Performed by ABORT

Actions Performed by ABORT with Embedded SQL

ABORT with WHERE Clause

ABORT tests each value separately. Therefore, the WHERE clause should not introduce both an aggregate and a nonaggregate value. The aggregate value becomes, in effect, a GROUP BY value, and the mathematical computation is performed on the grouped aggregate results.

     ABORT WHERE (SUM(test.colA) <> 188)
           AND   (test.colb = 125);

The preceding statement is processed first by performing an all-rows scan with the condition (colb=125), which selects a single row and then computes intermediate aggregate results with the condition (SUM(cola) <> 188).

The condition tests true because the value of cola in the selected row is less than 188.

If ABORT WHERE is used and the statement requires READ access to an object for execution, the user executing this DML statement must have SELECT right to the data being accessed.

The WHERE clause search condition of an ABORT statement can include scalar subqueries. If so, the subqueries require a FROM clause, and the ABORT should have a FROM clause if it is desired that the scope of a reference in a subquery is the ABORT condition.

ABORT with a UDT in Its WHERE Clause

ABORT supports comparisons of UDT expressions in a WHERE clause. A UDT expression is any expression that returns a UDT value.

If you specify a UDT comparison, then the UDT must have a defined ordering. See CREATE ORDERING in Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.

Rules for Using Correlated Subqueries in an ABORT Statement

Correlated subqueries, scalar subqueries, and the EXISTS predicate are supported in the WHERE clause of an ABORT statement.

See Correlated Subqueries.

Rules for Using Scalar Subqueries in ABORT Statements

You can specify a scalar subquery in the WHERE clause of an ABORT statement in the same way you can specify one for a SELECT statement.

You can also specify an ABORT statement with a scalar subquery in the body of a trigger. However, the database processes any noncorrelated scalar subquery you specify in the WHERE clause of an ABORT statement in a row trigger as a single-column single-row spool instead of as a parameterized value.

Rules for Using a Scalar UDF in an ABORT Statement

You can invoke a scalar UDF in the WHERE clause of a UDF if the UDF returns a value expression. However, you can only specify a scalar UDF as a search condition if it is invoked within an expression and returns a value expression.

Rules For Using ABORT In Embedded SQL

The following rules apply to using ABORT within an embedded SQL program.

• ABORT cannot be performed as a dynamic SQL request.
• ABORT is not valid when you specify the TRANSACT(2PC) option to the embedded SQL preprocessor.

Multiple ABORT Statements

If a macro or multistatement request contains multiple ABORT statements, those statements are initiated in the order they are specified, even if the expressions could be evaluated immediately by the parser because the request does not access any tables. However, the system can process ABORT statements in parallel.

If the system executes the ABORT statements in parallel, and one of the statements actually does abort, then the system reports that abort regardless of its specified sequence in the macro or multistatement request.

You can examine the EXPLAIN report for the macro or multistatement request to determine whether the system is executing the ABORT statements in parallel or not.

Two Types of ABORT Statements

There are two categories of ABORT statements, those that can be evaluated by the Parser and do not require access to a table and those that require table access.

If ABORT expressions are processed that do not reference tables, and if their order of execution is not important relative to the other requests in a multistatement request or macro, then they should be placed ahead of any statements that reference tables so that the abort statement can be done at minimum cost.

In the following example, the first two ABORT statements can be evaluated by the parser and do not require access to tables. The third ABORT statement requires access to a table.

     CREATE MACRO macro_name (
       P1 INTEGER,
       P2 INTEGER)
     AS. . .
     ABORT 'error' WHERE :p1 < 0;
     ABORT 'error' WHERE :p2 < 0;
     SELECT. . .
     ABORT 'error' WHERE tab.c1 = :p1;