17.10 - System-Defined Attributes for Error Table-Specific Columns - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

The following table describes the characteristics of the system-defined and generated error table-specific columns. The ETC prefix indicates an Error Table Column.

Column Name Column Definition Data Type and Attributes
ETC_DBQL_QID Query ID for the Database Query Log.

The system sets up a time-based query ID to uniquely identify all error rows for a given request regardless of whether DBQL is enabled.

The query ID is incremented for each new request.

DECIMAL(18,0) NOT NULL
ETC_DMLType A code for the type of DML request that erred, as follows.
  • D represents a Delete operation.
  • I represents an Insert operation.

    Indicates either an INSERT … SELECT error or a MERGE insert error.

  • U represents an Update operation.

    Indicates either a MERGE update error or a join UPDATE error.

CHARACTER(1)
ETC_ErrorCode Either a DBC error code or a value of 0.

A row with ETC_ErrorCode = 0 is a special marker row that confirms the request completed successfully, but logged one or more errors in a LOGGING ERRORS operation.

A row with a value for ETC_ErrorCode other than 0 indicates that the request aborted because of the recorded DBC error code and logged one or more errors in a LOGGING ERRORS operation.

The system does not write a marker row for a request that completes successfully without logging any errors.

INTEGER NOT NULL
ETC_ErrSeq Error sequence number.

The value provides a numeric sequence that is easier to refer to for error analysis and recovery purposes than the timestamp values in ETC_TimeStamp.

The value stored for ETC_ErrSeq begins the sequence with 1 for the first error of a given request, and increments by 1 for each subsequent error recorded.

The ETC_ErrSeq value does not reflect the true processing sequence of index rows that cause USI or referential integrity errors because, for example, the system assigns the sequence numbers for referential integrity errors when it informs the AMPs that own the data rows of the index violations, not when it first detects those violations.

INTEGER NOT NULL
ETC_IndexNumber Contains either the ID for the index that caused a USI or referential integrity violation or is set null. SMALLINT
ETC_IdxErrType Indicates if the index ID stored in ETC_IndexNumber refers to an USI violation or a referential integrity violation.
  • Null indicates that ETC_IndexNumber is null.
  • r indicates a parent-delete referential integrity violation.
  • R indicates a child-insert referential integrity violation.
  • U indicates a unique secondary index violation.

Because ETC_IdxErrType is defined with the NOT CASESPECIFIC attribute, both child and parent referential integrity violations are selected when you specify a predicate condition of WHERE ETC_IdxErrType = 'R'.

To work around this problem, make your predicate conditions case specific, as follows.

  • To select only child-insert errors, specify the WHERE condition etc_idxerrtype (CS) = ‘R’
  • To select only patient-delete errors, specify the WHERE condition etc_idxerrtype (CS) = ‘r’.

This provides a simple and reliable check for child versus parent referential integrity errors, and for parent versus child tables identified by ETC_RITableId.

CHARACTER(1) NOT CASESPECIFIC
ETC_RowId Records the rowIDs or row key values specified in the following table for the listed error conditions.
  • If the insert row is a duplicate of an existing row in the target table, the value is the rowID of the target table row.
  • If the insert row fails a CHECK constraint, the value is the row key of the insert row with 0s in its uniqueness portion.
  • If an insert row build fails because an internal error such as a divide by zero error occurred, the value is the row key of the insert row with 0s in its uniqueness portion.
  • If the update row of a MERGE request is a duplicate of an existing row in the target table, the value is the rowID of the original image of the target table row being updated.
  • If the update row fails a CHECK constraint, the value is the rowID of the original image of the target table row being updated.
  • If an update row build for a MERGE request fails because of an internal error such as a divide by zero error, the value is the rowID of the target table row being updated.
  • If multiple source rows of a MERGE request are attempting to update the same target table row (this is not permitted by the ANSI SQL:2011 standard), the value is the rowID of the target table row.
  • For any other operational error, the system stores a null.

This information is intended for Teradata Services personnel.

BYTE(10)
ETC_TableId Identifies the table associated with the value stored in ETC_FieldId. BYTE(6)
ETC_FieldId Stores the id of the column that caused an error condition, which is the field ID stored in DBC.TVFields.
  • For a composite key or index violation error, the value is the column ID of the first column in the composite key or index.
  • For a column or constraint violation, the value is the column ID of the column returned by the system for a constraint violation.
  • For a duplicate row error, the system stores a null.

If a table constraint contains two columns, the system stores the ID of the column on the right-hand side of the constraint expression.

For example, a CHECK (y>x) error captures the field ID of column x.

SMALLINT
ETC_RITableId Identifies the other table involved in an referential integrity violation.
  • For a child-insert error, the value is the referencing parent.
  • For a parent-delete error, the value is the referencing child.
BYTE(6)
ETC_RIFieldId Identifies the column in the table associated with an RI violation.
  • For a parent table, the value identifies the missing UPI or USI column referenced by an inserted child row.
  • For a child table, the value identifies the foreign key column that referenced the UPI or USI key in a deleted parent row.

For composite keys, ETC_RIFieldId identifies only the first column defined for the key, which is often enough information to identify the key uniquely.

SMALLINT
ETC_TimeStamp Indicates the time the system detects an error, not the time the system writes the error row to the error table. TIMESTAMP(2) NOT NULL
ETC_Blob Not currently used.

This column consumes space in the retrieval of rows from an error table.

If an error table has a large number of data table columns, the inclusion of this column in the select list might cause the query to reach an internal database limitation and return a 'Row size or Sort Key size overflow' error.

If this happens, drop the ETC_Blob column from the select list.

The size limit for ETC_Blob is 2 MB, which is large enough to store a maximum-sized memory segment associated with the step that produces a data conversion error.

BLOB