17.05 - Logging Errors for INSERT … SELECT Requests - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
Normally, an INSERT … SELECT request with error logging completes without any USI or RI errors. Exceptions to normal completion of a INSERT … SELECT request are processed as follows:
  • Not all types of errors are logged when you specify the LOGGING ERRORS option for an INSERT … SELECT request.
    • All local, or data errors, are logged.
    • Errors that occur before the row merge step, such as data conversion errors detected in the RET AMP step before the MRG or MRM AMP steps, are not logged. See Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

      These are errors that occur during row merge step processing, such as CHECK constraint, duplicate row, and UPI violation errors.

  • When the database encounters USI or RI errors (or both) in the INSERT … SELECT operation, the following events occur in sequence:
    1. The transaction or request runs to completion.
    2. The system writes all error-causing rows into the error table.
    3. The system aborts the transaction or request.
    4. The system rolls back the transaction or request.

      Note that the system does not invalidate indexes, nor does it roll error table rows back, enabling you to determine which rows in the INSERT set are problematic and to determine how to correct them.

    If the number of errors in the request is large, running it to completion plus rolling back all the INSERT operations can exert an impact on performance. To minimize the potential significance of this problem, you should always consider either using the default limit or specifying a WITH LIMIT OF error_limit clause with a relatively small value for error_limit. In other words, unless you have a good reason for doing so, you should avoid specifying WITH NO LIMIT.

  • When an INSERT … SELECT operation encounters data errors only, their occurrence does not abort the transaction or request, the non-erring INSERT operations complete successfully, and the erring rows are logged in the error table so you can correct them.
The following rules and guidelines apply to logging errors in an error table for INSERT … SELECT loads:
  • Before you can log errors for INSERT … SELECT loads, you must first create an error table for the base data table into which you intend to do an INSERT … SELECT load. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • If error logging is not enabled and you submit an INSERT … SELECT bulk loading operation with the LOGGING ERRORS option specified, the system aborts the request and returns an error message to the requestor.
  • The LOGGING ERRORS option is not valid in a multistatement request.
  • Two basic types of errors can be logged when you specify the LOGGING ERRORS option:
    • Local errors
      Local errors are defined as errors that occur on the same AMP that inserts the data row. The following types of errors are classified as local errors:
      • Duplicate row errors, which occur only in ANSI session mode.

        The system silently ignores duplicate row errors that occur from a INSERT … SELECT into a SET table in Teradata session mode.

        Duplicate rows can also arise from these INSERT … SELECT insert situations:
        • The source table has duplicate rows.
        • An insert is made on a different AMP than the failed update.
      • Duplicate primary key errors
      • CHECK constraint violations
    • Nonlocal errors
      Nonlocal errors are defined as errors that occur on an AMP that does not own the data row. The following types of errors are classified as nonlocal errors:
      • Referential integrity violations
      • USI violations

        An exception to this is the case where a USI violation is local because the USI is on the same set of columns as a the primary index of a row-partitioned table. The system treats such an error as a nonlocal error, even though it is local in the strict definition of a local error.

    The system records the error in the error table, rejects the error-causing rows from the target table, and performs the actions described in the table below, depending on the type of error.

    Type of Error Response
    Local Completes the request or transaction successfully.
    Nonlocal Allows the request or transaction to complete in order to record all of the error causing rows in the INSERT … SELECT load, then aborts the request or transaction and rolls back its inserts and updates.
    Local and nonlocal
  • The system does not handle batch referential integrity violations for INSERT … SELECT error logging. Because batch referential integrity checks are all-or-nothing operations, a batch referential integrity violation causes the system to respond in the following session mode-specific ways:
    Session Mode Result
    ANSI Request aborts and rolls back.
    Teradata Transaction aborts and rolls back.
  • The system does not handle error conditions that do not allow useful recovery information to be logged in the error table. Such errors typically occur during intermediate processing of input data before it are built into a row format that corresponds to the target table.
    The system detects this type of error before the start of data row inserts and updates. The following are examples of these types of error:
    • UDT, UDF, and table function errors
    • Version change errors
    • Nonexistent table errors
    • Down AMP request against nonfallback table errors
    • Data conversion errors

      Conversion errors that occur during row inserts are treated as local data errors.

    The way the system handles these errors depends on the current session mode:

Session Mode Result
ANSI Request aborts and rolls back.
Teradata Transaction aborts and rolls back.

The system preserves error table rows logged by the aborted request or transaction and does not roll them back.

The system inserts a marker row into the error table at the end of a successfully completed INSERT … SELECT request with logged errors.

Marker rows have a value of 0 in the ETC_ErrorCode column of the error table, and their ETC_ErrSeq column stores the total number of errors logged. All other columns in a marker row except for ETC_DBQL_QID and ETC_TimeStamp are set to null.

If no marker row is recorded, the request or transaction was aborted and rolled back because of one or more of the following reasons:
  • The specified error limit was reached.
  • The system detected an error that it cannot handle.
  • The system detected a nonlocal (RI or USI) violation.

    The database preserves the error rows that belong to the aborted request or transaction.

  • In addition to the previously listed errors, the system does not handle the following types of errors. though it preserves logged error table rows if any one of the listed errors is detected:
    • Out of permanent space or our of spool space errors
    • Duplicate row errors in Teradata session mode (because the system ignores such errors in Teradata session mode)
    • Trigger errors
    • Join index maintenance errors
    • Identity column errors
  • The LOGGING ERRORS option is applicable to INSERT … SELECT load requests whose target tables are permanent data tables only.

    Other kinds of target tables, such as volatile and global temporary tables, are not supported.

    The database returns a warning message to the requestor if it logs an error.

  • You can either specify logging of all errors or logging of no errors. This means that you cannot specify the types of errors to log. The WITH LIMIT OF error_limit option, of course, enables you to terminate error logging when the number of errors logged matches the number you specify in the optional WITH LIMIT OF error_limit clause.

    If you do not specify a LOGGING ERRORS option, and an error table is defined for the target data table of the INSERT … SELECT request, the system does no error handling for INSERT … SELECT operations against that data table.

    In this case, the request or transaction containing the erring INSERT … SELECT request behaves as follows when an error occurs:

    Session Mode Result
    ANSI Request aborts and rolls back.
    Teradata Transaction aborts and rolls back.
  • If you specify neither the WITH NO LIMIT option, nor the WITH LIMIT OF error_limit option, the system defaults to an error limit of 10.

    The database logs errors up to the limit of 10, and then the request of transaction containing the INSERT … SELECT request behaves as follows when the tenth error occurs:

    Session Mode Result
    ANSI Request aborts and rolls back.
    Teradata Transaction aborts and rolls back.
The database preserves error table rows logged by the aborted request or transaction and does not roll them back.
  • WITH NO LIMIT

    The database places no limit on the number of error rows that can accumulate in the error table associated with the target data table for the INSERT … SELECT operation.

  • WITH LIMIT OF error_limit

    The database logs errors up to the limit of error_limit, and then the request or transaction containing the INSERT … SELECT request behaves as follows when the error_limit is reached:

Session Mode Result
ANSI Request aborts and rolls back.
Teradata Transaction aborts and rolls back.
The database preserves error table rows logged by the aborted request or transaction and does not roll them back.
  • The activity count returned for an INSERT … SELECT … LOGGING ERRORS request is the same as that returned for an INSERT … SELECT operation without a LOGGING ERRORS option: a count of the total number of rows inserted into the target data table.
  • LOGGING ERRORS does not support LOB data. LOBs in the source table are not copied to the error table. They are represented in the error table by nulls.
  • An index violation error does not cause the associated index to be invalidated.
  • For referential integrity validation errors, you can use the IndexId value with the RI_Child_TablesVX view to identify the violated Reference index. For information about Reference indexes, see Database Design .

    You can distinguish whether an index error is a USI or referential integrity error by the code stored in the ETC_IdxErrType error table column.

ETC_IdxErrType Value Error
R Foreign key insert violation.
r Parent key delete violation.
U USI validation error.