15.00 - Failure Response - Teradata Database

Teradata Database SQL Fundamentals

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

Failure Response

A failure response is a severe error. The response includes a statement number, an error code, and an associated text string describing the cause of the failure.

Teradata Session Mode

In Teradata session mode, a failure causes the system to roll back the entire transaction.

If one statement in a macro fails, a single failure response is returned to the client, and the results of any previous statements in the transaction are backed out.

ANSI Session Mode

In ANSI session mode, a failure causes the system to roll back the entire transaction, for example, when the current request:

  • Results in a deadlock
  • Performs a DDL statement that aborts
  • Executes an explicit ROLLBACK or ABORT statement
  • Example  

    The following SELECT statement

       SELECT * FROM Inventory:;

    in BTEQ, returns the failure response message:

        *** Failure 3706 Syntax error: expected something between the word
        'Inventory' and ':'.
                       Statement# 1, Info =20 
        *** Total elapsed time was 1 second.

    Example  

    Assume that the session is running in ANSI session mode, and the following table is defined:

       CREATE MULTISET TABLE inv, FALLBACK,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
          item INTEGER CHECK ((item >=10) AND (item <= 20) ))
       PRIMARY INDEX (item);

    You insert a value of 12 into the item column of the inv table.

    This is valid because the defined integer check specifies that any integer between 10 and 20 (inclusive) is valid.

       INSERT INTO inv (12);

    The following results message returns.

        *** Insert completed. One row added....

    You commit the current transaction:

       COMMIT;

    The following results message returns:

        *** COMMIT done. ...

    You insert a valid value of 15 into the item column of the inv table:

       INSERT INTO inv (15);

    The following results message returns.

        *** Insert completed. One row added....

    You can use the ABORT statement to cause the system to roll back the transaction:

       ABORT;

    The following failure message returns:

        *** Failure 3514 User-generated transaction ABORT.
                       Statement# 1, Info =0 

    You select all rows from the inv table:

       SELECT * FROM inv;

    The following results message returns:

        *** Query completed. One row found. One column returned.
     
          item
       -------
            12