17.10 - Function of Error Tables - 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)

Data warehousing environments require frequent updates of their underlying databases, and the overall data load strategy must include scheduled incremental loads, or small batches, to be performed throughout the day.

The intent of the LOGGING ERRORS option with INSERT ... SELECT and MERGE batches is to complement the FastLoad and MultiLoad batch load utilities that handle larger volumes of data and more complex loads with multiple target tables or DML operations, and the continuous load utility, Teradata Parallel Data Pump.

By capitalizing on incremental batch loads on a continuous basis using INSERT ... SELECT or MERGE requests or both, you can update your data warehouse as frequently as is necessary, using a mixture of data loading strategies. Furthermore, such batch requests are not restricted by the inability to load data into tables defined with LOB columns (note, however, that error logging does not support LOB data. LOBs in a source table are not copied to an error table; instead, they are represented in the error table as nulls.), unique secondary indexes, referential integrity constraints, join indexes, hash indexes, and triggers that can make bulk data loading into active data warehouse tables a complicated operation (see Teradata® FastLoad Reference, B035-2411 and Teradata® MultiLoad Reference, B035-2409 for a complete list and description of the database data table restrictions they must observe).

Although Teradata Parallel Data Pump performs batch loads, the INSERT ... SELECT and MERGE batch options and the Teradata Parallel Data Pump option can serve very distinct situations. For example, when logged on sessions are near their full capacity, the INSERT ... SELECT or MERGE batch options are generally preferable to the Teradata Parallel Data Pump option. Conversely, when the specific order of updates is important, Teradata Parallel Data Pump are generally preferable.

The purpose of error tables is to provide a substrate for complex error handling of the following kinds of batch loads:
  • Insert errors that occur during an INSERT ... SELECT operation
  • Insert and update errors that occur during a MERGE operation

When an insert or update error occurs on an index or data conversion, the transaction or request containing the erring statement does not roll back, but instead runs to completion, logging the rows that caused errors in an error table.

Data errors that cause secondary index or referential integrity errors do cause the transaction or request containing the erring statement to roll back. You must then fix the errors that caused the secondary index or referential integrity violations before you can again load those rows into the database.

Error tables are one component of a batch system that includes extensions to MERGE and INSERT ... SELECT batch load operations to support complex error handling (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146). These DML statement extensions permit you to specify error logging for INSERT ... SELECT and MERGE operations. If you specify error logging, the system logs error conditions and captures copies of the data rows that cause them in an error table.