- Not all types of errors are logged when you specify the LOGGING ERRORS option for a MERGE request.
- All local, or data errors, are logged.
These are errors that occur during row merge step processing, such as CHECK constraint, duplicate row, and UPI violation errors.
- Errors that occur before the row merge step, such as data conversion errors detected in the RET AMP step before the MRG or MRM AMP steps, are not.
- All local, or data errors, are logged.
- When the system encounters USI or RI errors (or both) in the MERGE operation, the following events occur in sequence:
- The transaction or request runs to completion
- The system writes all erring rows into the error table
- The system aborts the transaction or request
- The system rolls back the transaction or request
Note that the system does not invalidate indexes, nor does it roll error table rows back, enabling you to determine which rows in the MERGE set are problematic and to determine how to correct them.
If the number of errors in the request is large, running it to completion plus rolling back all the INSERT and UPDATE operations can exert an impact on performance. To minimize the potential significance of this problem, you should always consider specifying a WITH LIMIT OF error_limit clause.
- Before you can log errors for MERGE loads, you must create an error table (see “CREATE ERROR TABLE” in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144) for the base data table into which you intend to do a MERGE load.
- If error logging is not enabled and you submit a MERGE load operation with the LOGGING ERRORS option specified, the system aborts the request and returns an error message to the requestor.
- The LOGGING ERRORS option is valid in both ANSI and Teradata session modes.
- The LOGGING ERRORS option is not valid in a multistatement request.
- Two general categories of errors can be logged when you specify a LOGGING ERRORS option:
- Local errors
Local errors are defined as errors that occur on the same AMP that inserts the data row. The following types of errors are classified as local errors:
Duplicate row errors, which occur only in ANSI session mode.
The system silently ignores duplicate row errors that occur from a MERGE into a SET table in Teradata session mode.
Duplicate rows can also arise from the following MERGE insert situations:- The source table has duplicate rows.
- An insert is not well-behaved, meaning that the insert is made on a different AMP than the failed update.
Duplicate primary key errors
CHECK constraint violations
Attempts to update the same target table row with multiple source table rows
- Nonlocal errors Nonlocal errors are defined as errors that occur on an AMP that does not own the data row. The following types of errors are classified as nonlocal errors:
- Referential integrity violations
- USI violations
An exception to this is the case where a USI violation is local because the USI is on the same set of columns as the primary index. The system treats such an error as a nonlocal error, even though it is local in the strict definition of a local error.
The system response depends on the type of error:IF this type of error occurs … THEN the system records it in the error table, rejects the error-causing rows from the target table, and … local completes the request or transaction successfully. nonlocal lets the request or transaction run to completion in order to record all the error causing rows in the MERGE load, then aborts the request or transaction and rolls back its inserts and updates. both local and nonlocal
- Local errors
- Teradata Database rejects data rows that cause local errors from the target table. However, the system does not reject data rows that cause nonlocal errors from the target table, but instead inserts them into the target table.
- Teradata Database does not handle batch referential integrity violations for MERGE error logging. Because batch referential integrity checks are all-or-nothing operations, a batch referential integrity violation causes the system to respond in the following session mode-specific ways:
IF this session mode is in effect … THEN the erring … ANSI request aborts and rolls back. Teradata transaction aborts and rolls back. - Teradata Database does not handle error conditions that do not allow useful recovery information to be logged in the error table. Such errors typically occur during intermediate processing of input data before it are built into a row format that corresponds to the target table.
Teradata Database detects this type of error before the start of data row inserts and updates. The following are examples of these types of error:
- UDT, UDF, and table function errors
- Version change errors
- Nonexistent table errors
- Down AMP request against nonfallback table errors
- Data conversion errors
Note that the system handles conversion errors that occur during data row inserts as local data errors.
The way Teradata Database handles these errors depends on the current session mode, as explained by the following table:IF this session mode is in effect … THEN the erring … ANSI request aborts and rolls back. Teradata transaction aborts and rolls back.
Teradata Database preserves error table rows logged by the aborted request or transaction and does not roll them back.
Teradata Database inserts a marker row into the error table at the end of a successfully completed MERGE request with logged errors.
Marker rows have a value of 0 in the ETC_ErrorCode column of the error table, and their ETC_ErrSeq column stores the total number of errors logged. All other columns in a marker row except for ETC_DBQL_QID and ETC_TimeStamp are set to null.
- The specified error limit was reached.
- Teradata Database detected an error that it cannot handle.
- Teradata Database detected a nonlocal (RI or USI) violation.
The system preserves the error rows that belong to the aborted request or transaction.
- In addition to the previously listed errors, Teradata Database does not handle the following types of errors. However, it preserves the logged error rows for any of the errors listed.
- Out of permanent space or our of spool space errors
- Duplicate row errors in Teradata session mode, because the system ignores such errors in Teradata session mode
- Trigger errors
- Join index maintenance errors
- Identity column errors
- Implicit USI violations
When you create a table with a primary key that is not also the primary index, Teradata Database implicitly defines a USI on that primary key.
Teradata Database cannot invalidate a violated implicit USI on a primary key because it does not allow such a USI to be dropped and then recreated later.
Teradata Database handles the errors in the following session mode-specific ways:
IF this session mode is in effect … THEN the erring … ANSI request aborts and rolls back. Teradata transaction aborts and rolls back.
- The LOGGING ERRORS option is applicable to MERGE load requests whose target tables are permanent data tables only.
Other kinds of target tables, such as volatile and global temporary tables, are not supported.
The system returns a warning message to the requestor if it logs an error.
- You cannot log errors for MERGE requests that specify unreasonable update or insert operations.In this case, the request or transaction containing the erring MERGE request behaves as follows when the system detects the unreasonable INSERT specification:
IF this session mode is in effect … THEN the erring … ANSI request aborts and rolls back. Teradata transaction aborts and rolls back. - You can log all errors or not log errors. You cannot specify the types of errors to log. The WITH LIMIT OF error_limit option enables you to terminate error logging when the number of errors logged matches the number you specify in the optional WITH LIMIT OF error_limit clause.
If you do not specify a LOGGING ERRORS option, and an error table is defined for the target data table of the MERGE request, the system does no error handling for MERGE operations against that data table.
In this case, the request or transaction containing the erring MERGE request behaves as follows when an error occurs:IF this session mode is in effect … THEN the erring … ANSI request aborts and rolls back. Teradata transaction aborts and rolls back. - If you specify neither the WITH NO LIMIT option, nor the WITH LIMIT OF error_limit option, the system defaults to an error limit of 10.Teradata Database logs errors up to the limit of 10, and then the request of transaction containing the MERGE request behaves as follows when the eleventh error occurs:
IF this session mode is in effect … THEN the erring … ANSI request aborts and rolls back. Teradata transaction aborts and rolls back.
- WITH NO LIMIT
Teradata Database places no limit on the number of error rows that can accumulate in the error table associated with the target data table for the MERGE operation.
- WITH LIMIT OF error_limit
Teradata Database logs errors up to the limit of error_limit, and then the request or transaction containing the MERGE request behaves as follows when the error_limit + 1 error occurs:
IF this session mode is in effect … THEN the erring … ANSI request aborts and rolls back. Teradata transaction aborts and rolls back.
- The activity count returned for a MERGE … LOGGING ERRORS request is the same as that returned for a MERGE operation without a LOGGING ERRORS option, a count of the total number of rows in the target table that were:
- updated
- inserted
- deleted DELETE cannot be used with INSERT or UPDATE. INSERT and UPDATE can be used individually or together.The possible activity types returned to the application are listed in the following table:
Activity Type Name Activity Type Number Description PCLMRGMIXEDSTMT 127 A mix of updates and inserts. PCLMRGUPDSTMT 128 All updates, no inserts or deletes. PCLMRGINSSTMT 129 All inserts, no updates or deletes. PCLMRGDELSTMT 206 All deletes, no updates or inserts.
| This activity type … | Is returned when this clause is specified for the MERGE request … |
|---|---|
| PCLMRGMIXEDSTMT | WHEN MATCHED and WHEN NOT MATCHED.
|
| PCLMRGUPDSTMT | WHEN MATCHED THEN UPDATE only. |
| PCLMRGINSSTMT | WHEN NOT MATCHED THEN INSERT only. |
| PCLMRGDELSTMT | WHEN MATCHED THEN DELETE only. |
Following is an example of a MERGE statement and resulting output:
MERGE INTO t1 target USING (SEL a, b, c FROM src WHERE a <= 100) AS source (a, b, c) ON target.a1 = source.a WHEN MATCHED UPDATE SET b1=b1+100 WHEN NOT MATCHED THEN INSERT (a,b,c); *** Merge completed. 80 rows affected. 30 rows inserted, 50 rows updated, no rows deleted.
See Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 or Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418 for details about these activity types.
- LOGGING ERRORS does not support LOB data. LOBs in the source table are not copied to the error table. They are represented in the error table by nulls.
- An index violation error does not cause the associated index to be invalidated.
- For referential integrity validation errors, you can use the IndexId value with the RI_Child_TablesVX view (for information about the RI_Child_TablesVX view, see Teradata Vantage™ Data Dictionary, B035-1092) to identify the violated Reference index (for information about Reference indexes, see Teradata Vantage™ - Database Design, B035-1094). You can determine whether an index error is a USI or referential integrity error by the code stored in the ETC_IdxErrType error column.
| IF the value of ETC_IdxErrType is … | THEN the error is a … |
|---|---|
| R | foreign key insert violation. |
| r | parent key delete violation. |
| U | USI validation error. |