15.00 - System-Defined Attribute Characteristics and Definitions for Error Table-Specific Columns - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

System‑Defined Attribute Characteristics and Definitions for Error Table‑Specific Columns

The following table describes the characteristics of the system‑defined and generated error table‑specific columns. The ETC prefix for the column names represents 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 the use of Teradata support personnel and is probably not useful for end users.

    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 Teradata 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