15.00 - ABORT - Teradata Database

Teradata Database SQL Data Manipulation Language

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1146-015K

ABORT

Purpose  

Terminates the current transaction and rolls back its updates.

For information about the temporal form of ABORT, see Temporal Table Support.

Syntax  

where:

 

Syntax Element …

Specifies …

abort_message

the text of the message to be returned when the transaction is terminated.

If abort_message is not specified, the message defaults to “user-generated transaction ABORT”

FROM option

a clause that is required only if the WHERE clause includes subqueries or if the request is preceded with a temporal qualifier.

You can code scalar subqueries as an expression within a derived table in FROM clause conditions in the same way you can specify them for a SELECT request.

See “Scalar Subqueries” on page 142 and “Rules for Using Scalar Subqueries in ABORT Statements” on page 304). Note, however, that you cannot code a derived table as a scalar subquery.

The contents of the FROM option are described in “FROM Clause” on page 96.

WHERE abort_condition

a clause that introduces a conditional expression whose result must evaluate to TRUE if the transaction is to be rolled back. The expression can specify aggregate operations and scalar subqueries.

See “Scalar Subqueries” on page 142 and “Rules for Using Scalar Subqueries in ABORT Statements” on page 304 for the rules and restrictions.

If the WHERE clause is omitted, termination is unconditional. See “WHERE Clause” on page 119.

If you specify the value for a row‑level security constraint, it must be expressed in its encoded form.

The WHERE condition specifies an expression whose result must evaluate to TRUE if termination is to occur. If the result is FALSE, transaction processing continues.

ANSI Compliance

ABORT is a Teradata extension to the ANSI SQL:2011 standard. It is a synonym for the ANSI SQL:2011‑compliant statement ROLLBACK (see “ROLLBACK” on page 484).

Required Privileges

None.

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.

ABORT Is Explicit

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.

In the case of a SELECT statement, it makes no difference whether you issue 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

ABORT performs the following actions:

1 Backs out changes made to the database as a result of the transaction.

2 Deletes spooled output for the request.

3 Releases locks associated with the transaction.

4 If the transaction is in the form of a macro or a multistatement request, bypasses execution of the remaining statements.

5 Returns a failure response to the user.

Actions Performed by ABORT With Embedded SQL

ABORT performs the following additional actions when used within an embedded SQL application:

1 See “Actions Performed by ROLLBACK” on page 486.

2 Discards dynamic SQL statements prepared within the current transaction.

3 Closes open cursors.

4 Cancels database changes made by the current transaction.

5 Sets SQLCODE to zero when ROLLBACK is successful.

6 Sets the first SQLERRD element of the SQLCA to 3513 or 3514, depending on whether abort_message is specified.

7 Returns an abort message in the SQLERRM field of the SQLCA if a WHERE_clause is specified.

8 Terminates the application program connection (whether explicit or implicit) to Teradata Database if the RELEASE keyword is specified.

 

IN this environment …

IF no LOGON/CONNECT request precedes the next SQL request, THEN the preprocessor …

Mainframe-attached

attempts to establish an implicit connection.

Workstation-attached

issues a No Session Established error.

ABORT With a 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.

For example, assuming that the following items are true, the ABORT statement that follows incorrectly terminates the transaction.

  • The table test contains several rows,
  • The sum of test.colA is 188, and
  • Only one row contains the value 125 in test.colB
  •      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 it 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 SQL Data Definition Language.

    Rules For Using Correlated Subqueries In An ABORT Statement

    The following rules apply to correlated subqueries used in an ABORT statement:

  • A FROM clause is required for an ABORT statement if the ABORT references a table. All tables referenced in an ABORT statement must be defined in a FROM clause.
  • If an inner query column specification references an outer FROM clause table, then the column reference must be fully qualified.
  • Teradata Database supports the ABORT statement as an extension to ANSI syntax. The equivalent ANSI SQL statement is ROLLBACK (see “ROLLBACK” on page 484).
  • Correlated subqueries, scalar subqueries, and the EXISTS predicate are supported in the WHERE clause of an ABORT statement.

    Also see “Rules for Using Scalar Subqueries in ABORT Statements” on page 304 and “Correlated Subqueries” on page 134.

    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, Teradata 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.

    See “Example 5: Using an SQL UDF in the WHERE Clause of an ABORT Statement” on page 307.

    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. See “Two Types of ABORT Statements” on page 305. 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;

    Example  

    In the following example, the ABORT statement terminates macro execution if the row for the employee being deleted is not present in the employee table.

    Statements in the body of the macro are entered as one multistatement request. Therefore, if the WHERE condition of the ABORT statement is met, the entire request is aborted and the accuracy of the count in the department table is preserved.

         CREATE MACRO del_emp (num SMALLINT FORMAT '9(5)', 
            dname VARCHAR(12), 
            dept  SMALLINT FORMAT '999') AS (
         ABORT 'Name does not exist' WHERE :num NOT IN (
         SELECT emp_no 
         FROM employee 
         WHERE UPPER(name) = UPPER(:dname))
        ;DELETE FROM employee 
         WHERE UPPER(name) = UPPER(:dname) 
        ;UPDATE department 
          SET emp_count = emp_count - 1
          WHERE dept_no = :dept; ) ;

    Example : Valid ABORT Statements

    The following ABORT statements are all syntactically correct.

         ABORT 
         WHERE user= 'DBC';
     
         ABORT 
         FROM table_1 
         WHERE table_1.x1 = 1;
     
         ABORT 
         FROM table_11 
         WHERE table_1.x1 > 1;
     
         ABORT 
         FROM table_1,table_2 
         WHERE table_1.x1 = table_2.x2;

    Example  

    The following example uses an ABORT statement in the macro newemp. newemp inserts a row into the employee table for each new employee and then executes the SELECT statement to verify that the new information was entered correctly. The ABORT statement ensures that no new employee is inadvertently added to the executive office department, department 300.

         CREATE MACRO newemp (
            number   INTEGER,
            name     VARCHAR(12), 
            dept     INTEGER 100 TO 900, 
            position VARCHAR(12), 
            sex      CHARACTER, 
            ed_lev   BYTEINT ) AS (
         ABORT 'Department number 300 not valid'
         WHERE :dept = 300 ;
         INSERT INTO employee (emp_no, name, dept_no, job_title, sex,
                               ed_lev) 
         VALUES (:number, :name, :dept, :position, :sex, :edlev) ;
         SELECT * 
         FROM employee 
         WHERE emp_no = :number ; ) ; 

    Example : ABORT With a UDT In The WHERE Clause

    The following examples show correct use of UDT expressions in the WHERE clause of an ABORT statement:

         ABORT WHERE (tab1.euro_col < CAST(0.0 AS euro));
     
         ABORT WHERE (tab1.cir_col.area() < 10.0);

    Example : Using an SQL UDF in the WHERE Clause of an ABORT Statement

    The following ABORT statement specifies an SQL UDF in its WHERE search condition.

         ABORT FROM t1 
         WHERE a1 = test.value_expression(2, 3);

    Related Topics

  • “ROLLBACK” on page 484
  • “COMMIT” on page 343
  • SQL Request and Transaction Processing
  • Temporal Table Support