- You can define only one error table per data table.
A single error table-to-data table relationship simplifies error management and allows the error table to be transparent to users because they do not need to know the name of the error table for a particular data table. You can create or drop an error table and display its structure indirectly by referencing the data table name in place of the error table name.
- You can define an error table for a column-partitioned table.
The system creates the error table as it would for a non-column-partitioned table except that the error table is a NoPI table without partitioning.
- You cannot use an ALTER TABLE request to change any property of an error table.
- The database maintains the compatibility between data tables and their associated error tables by disallowing any of the following ALTER TABLE operations when an error table is defined for a data table:
- Adding or dropping columns from the data table.
- Changing the primary index column set of the data table.
You can alter the partitioning of a data table that has an error table.
- Changing the fallback protection of the data table.
- Any ALTER TABLE request on the error table.
- You must create your error tables on base tables, not on views.creating an error table on a view is not permitted.
A security issue arises if you create an error table over all the base table columns in a view. Base table columns that were previously hidden by the view then become visible to the creator of the error table.
Although you could create the error table in a database the LOGGING ERRORS user does not have access to, that prevents the LOGGING ERRORS user from being able to perform error recovery.
If an error table is created with view columns only, error recovery using error table rows could become difficult, especially when errors come from a non-view column.
- You must create an error table before you can log errors against its associated data table.
- The base data table for which you are creating an error table cannot have more than 2,048 columns.
The newly created error table for this data table adds another 13 error columns to this total, bringing the maximum number of columns in an error table to 2,061. See System-Defined Attributes for Error Table-Specific Columns.
- If the size of an error table row (data table row plus the 13 ETC_ columns) exceeds the system row size limit, the CREATE ERROR TABLE request returns an error message to the requestor.
- An error table can be contained in either the same database or user as its associated data table or in a different database or user.
- Error tables are MULTISET tables by default to avoid the expense of duplicate row checks. You cannot change an error table to be a SET table.
- Error tables have the same fallback properties as their associated data table.
- The database copies the data types of data table columns to the error table.
The system does not copy any other column attributes from the data table to the error table.
- Error tables have the same primary index as their associated data table if the table has a primary index and is not column partitioned.
Because all error tables are MULTISET tables by definition, if the primary index for its associated data table is defined as a UPI, the system converts its definition to a NUPI for the error table.
- If the data table associated with an error table has a partitioned primary index, the system does not copy its partitioning to the error table.
Error tables are always nonpartitioned tables by default and cannot be changed to partitioned tables.
For information about partitioned primary indexes, see Partitioned and Nonpartitioned Primary Indexes and Teradata Vantage™ - Database Design, B035-1094.
- You can define an error table for a nonpartitioned NoPI table.
- You cannot define secondary indexes for an error table using the CREATE ERROR TABLE statement, nor does the system copy secondary indexes defined on the data table to its error table.
However, you can use the CREATE INDEX statement to add secondary indexes to an existing error table to facilitate scanning its captured data for analysis (which you cannot do for a Teradata MultiLoad error table). See CREATE INDEX.
You must drop any of the secondary indexes you have defined on the error table before you perform an INSERT … SELECT or MERGE operation that specifies error logging on the data table associated with the error table.
If you do not drop all secondary indexes on an error table, the system returns an error when you attempt to perform a bulk loading operation into the data table.
- You cannot define triggers, join indexes, or hash indexes on an error table.
- Error tables do not handle batch referential integrity violations.
Because batch referential integrity checks are all-or-nothing operations, a batch RI violation results in the following type of session mode-specific abort and returns an error message to the requestor:
IF this session mode is in effect … THEN the erring … ANSI request aborts. Teradata transaction aborts. - The system rejects attempts to insert data rows that cause local target table errors. Such rows are instead inserted into the error table. Once such errors have been corrected, you can reload the rows either directly from the error table or from the original source table.
- As a general rule, the system does not handle error conditions that do not allow useful recovery information to be logged in the error table.
These errors typically occur during intermediate processing of input data before they are built into a row format that corresponds to that of the target table.
The database detects such errors before the start of data row inserts and updates. The errors include the following:- UDT, UDF, and table function errors
- Version change sanity check errors
- Non-existent table sanity check errors
- Down AMP request against non-fallback table errors
- Data conversion errors.
However, conversion errors that occur during data row inserts and merge updates are handled as local data errors.
These kinds of errors result in the following type of session mode-specific abort and returns an error message to the requestor:
IF this session mode is in effect … THEN the erring … ANSI request aborts. Teradata transaction aborts. However, the system does preserve all error table rows logged by the aborted request or transaction and does not roll them back.
- In addition to the previously listed errors, the system does not handle the following types of errors, which result in the following type of session mode-specific abort and returns an error message to the requestor:
- Out of PERM or spool space errors
- Duplicate row errors in Teradata session mode (because the system rejects duplicate rows silently in Teradata mode)
- Trigger errors
- Join Index maintenance errors
- Identity column errors
- Implicit-USI violations.
When a table is created with a primary key that is not also the primary index, the system implicitly defines a USI on that primary key.
A violated implicit USI on a primary key cannot be invalidated because the system does not allow such a USI to be dropped and then recreated later
These kinds of errors result in the following type of session mode-specific abort and returns an error message to the requestor:
IF this session mode is in effect … THEN the erring … ANSI request aborts. Teradata transaction aborts. - When an error type that is excluded from logging occurs, such as a USI or referential integrity error, the system causes the following type of session mode-specific abort and returns an error message to the requestor:
IF this session mode is in effect … THEN the erring … ANSI request aborts. Teradata transaction aborts. In this case, the database rolls back only the target table rows, and only after the batch load operation has completed to enable the collect of all rows that cause errors. The database preserves existing error table rows and does not roll them back.
- The system sets other general table properties, such as journaling, checksums, datablock size, and so on to the defaults applied to CREATE TABLE statements that do not explicitly specify these settings.
- You can define an error table for a queue table.
- You cannot specify the QUEUE attribute when you create that error table.
In other words, while an error table itself cannot be a queue table, a queue table can have an error table associated with it.
- You can submit DML requests against an error table, just as you can for a Teradata MultiLoad error table, and you can make error corrections directly on the data row images stored in the error table.
After you correct errors in the error table rows, you can then reload the modified rows into the target table directly from the error table instead of reloading them from the staging table.
- CREATE ERROR TABLE requests add the definitions of row-level security constraint columns to the error table if row-level security constraints are defined for the base table. This is true unless you specify the NO RLS option.
The NO RLS option suppresses the generation of row-level security constraint columns from the base table to the error table.
- When rows are inserted into an error table, the database does not enforce row-level security constraints because such constraints would have been enforced during the MERGE or INSERT … SELECT operation that generated the error table row.
Error tables assume the following properties when they are created.