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

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.

• 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.

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.

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.

• 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.

• 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 support personnel.

• 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.

• For a child-insert error, the value is the referencing parent.
• For a parent-delete error, the value is the referencing child.

• 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.