Logging MERGE Errors In an Error Table
The assumption is that in the normal case, a MERGE request with error logging completes without any USI or RI errors. Exceptions to normal completion of a MERGE request are handled as follows:
These are errors that occur during row merge step processing, such as CHECK constraint, duplicate row, and UPI violation errors.
a The transaction or request runs to completion
b The system writes all erring rows into the error table
c The system aborts the transaction or request
d 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.
The following rules and guidelines apply to logging errors in an error table for MERGE loads:
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:
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.
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:
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 |
IF this session mode is in effect … |
THEN the erring … |
ANSI |
request aborts and rolls back. |
Teradata |
transaction aborts and rolls back. |
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:
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.
If no marker row is recorded, the request or transaction was aborted and rolled back because of one or more of the following reasons:
The system preserves the error rows that belong to the aborted request or transaction.
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. |
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.
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. |
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. |
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. |
Teradata Database preserves error table rows logged by the aborted request or transaction and does not roll them back.
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.
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. |
Teradata Database preserves error table rows logged by the aborted request or transaction and does not roll them back.
Note: 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. |
A dynamic MERGE request returns the following activity types:
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 or Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems for details about these activity types.
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. |