When the Update operator encounters a data row that cannot be processed properly, it creates a row in one of the two error tables that are created for each target table in the Update operator job:
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.
- ErrorTable1uses the suffix ET
- ErrorTable2 uses the suffix UV
Consider the following facts about error tables:
- 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.
- Names for error tables can be defaulted or they can be explicitly named using the VARCHAR ErrorTable attribute.
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:
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:
Each error table generates eight columns of information that you can use to help determine the cause of the problem. You can specify that the error tables return any or all of these columns, in any order, using an SQL SELECT statement in a BTEQ job.
For details on accessing error tables, see Accessing and Using Error Tables.
In addition, the acquisition error table includes the faulty record, and the application error table includes a mirror image of the target table columns.