16.10 - Error Logging Tables - 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

You can create an error logging table that you associate with a permanent base table when you want Teradata Database to log information about the following:

  • Insert errors that occur during an SQL INSERT … SELECT operation on the permanent base table
  • Update and insert errors that occur during an SQL MERGE operation on the permanent base table

To enable error logging for an INSERT … SELECT or MERGE statement, specify the LOGGING ERRORS option.

IF a MERGE operation or INSERT … SELECT operation generates errors that … AND the request … THEN the error logging table contains …
the error logging facilities can handle completes
  • an error row for each error that the operation generated.
  • a marker row that you can use to determine the number of error rows for the request.

The presence of the marker row means the request completed successfully.

aborts and rolls back when referential integrity (RI) or unique secondary index (USI) violations are detected during index maintenance
  • an error row for each USI or RI violation that was detected.
  • an error row for each error that was detected prior to index maintenance.

The absence of a marker row in the error logging table means the request was aborted.

reach the error limit specified by the LOGGING ERRORS option aborts and rolls back an error row for each error that the operation generated, including the error that caused the error limit to be reached.
the error logging facilities cannot handle aborts and rolls back an error row for each error that the operation generated until the error that the error logging facilities could not handle.

You can use the information in the error logging table to determine how to recover from the errors, such as which data rows to delete or correct and whether to rerun the request.