ErrorTable - Parallel Transporter

ErrorTable

Update operations create two error tables for each target table. These error tables are similar to those used for the Load operator, but the Update error tables are typically named with the following suffixes to distinguish them.

Consider the following:

Names for error tables can be defaulted or they can be explicitly named using the VARCHAR ErrorTable attribute.

If a job generates no errors, the error tables will be empty. They are automatically dropped at the end of the job.

If errors are generated, the tables are retained at the end of the job so error conditions can be analyzed.

To rerun a job from the beginning, either delete the error tables, or rename them, otherwise an error message results, stating that error tables already exist.

Conversely, if you restart a job (not from the beginning), an error tables must already exist. In other words, do not delete error tables to restart an update job.

Errors are separated into two tables, as follows:

Error Table (ET) contains most of the errors relating to data and the data environment.

The following types of errors are captured:

Constraint violations records that violate a range constraint defined for the table.

Unavailable AMP records that are written to a non-fallback table on an offline AMP.

Data conversion errors records that fail to convert to a specified data type.

By default, this error table is assigned a name using the convention:

   Target_Tablename_ET

Note: The names in the Error Table (ET) ErrorField column have a maximum supported size of 120 characters. The names can be up to 128 characters, but if a row is inserted into the Error Table (ET), the Teradata Database truncates any name that exceeds 120 characters.

Uniqueness Violations (UV) contains all of the rows that have violations of a unique primary index.

By default, this error table is assigned a name using the following convention:

   Target_Tablename_UV

Error Limits

The Update operator provides the same capability as the Load operator for setting an approximate number of errors captured before a job is terminated. The number is approximate because the Update operator sends multiple rows of data at a time to the Teradata Database. By the time the Update operator processes the message indicating that the error limit has been exceeded, the operator may have loaded more records into the error table than the actual number specified by the error limit.

When updating large amounts of data, it is not uncommon to encounter a data error that occurs repeatedly on each input record. Because an error can often be corrected long before errors are generated for all the records in a job run, consider using the ErrorLimit attribute to specify an approximate number of errors that can be tolerated before a job is terminated.

Note that the ET table contains errors in rows detected during the acquisition phase (the loading of data). These are commonly data conversion errors. The second table is the UV table and contains rows that are detected to be in error during the application phase of the job. These errors are commonly “uniqueness violation” errors (hence the name UV).

The ErrorLimit specification applies to each instance of the Update operator, not to all instances combined. For example, if the limit is set to 1,000, a single instance must detect that 1,000 rows were inserted into error tables to terminate the job.

This limit is specified with the ErrorLimit attribute. Errors are counted only during the Acquisition Phase, so the number of error rows being placed in the ET table are counted towards the number set in the ErrorLimit attribute. This applies to each instance of the Update operator, not to all instances combined. Therefore, if an error limit is set to 1,000, a single load instance must detect that 1,000 rows are inserted into the error tables before the job is terminated.

The error limit can also be reached at checkpoint time. See the examples below.

Error Limit Examples

To illustrate how Teradata PT determines if the number of errors has reached the Error Limit, consider these examples if there are two instances running and the Error Limit has been set to 1000.

If either instance by itself reaches 1000, it will terminate the job by returning a fatal error.

If instance #1 processes 500 error rows and instance #2 processes 500 error rows but does not reach a checkpoint. The job will continue processing.

If instance #1 processes 500 error rows and instance #2 processes 500 error rows but does reach a checkpoint. The total number of error rows for all instances combined is determined at checkpoint time and at the end of the Acquisition Phase. If the total of all instances exceeds the error limit at that time, the job will terminate with an error.

Error Capture

When running Insert, Update, Delete, or Upsert requests, errors can occur due to missing or duplicate rows. When errors occur, the request is rolled back and the error is normally reported. Use the APPLY statement to specify how to handle this type of error:

MARK means the error is to be captured and recorded.

IGNORE means the error is not to be recorded.

Specify whether errors are marked or ignored with the following in mind:

DUPLICATE INSERT ROWS means an attempt to insert a duplicate row.

DUPLICATE UPDATE ROWS means an update will result in a duplicate row.

MISSING DELETE ROWS means an attempt to delete a row that is missing.

MISSING UPDATE ROWS means an attempt to update a row that is missing.