16.10 - Failure Response - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Published
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)
Last Update
2018-04-25

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: Returning Error Messages When Using a SELECT Statement

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: Returning Error Messages in ANSI Mode

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