Logging MERGE Errors in an Error Table - Teradata VantageCloud Lake

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:

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.
When the system encounters USI or RI errors (or both) in the MERGE operation, the following events occur in sequence:
1. The transaction or request runs to completion
2. The system writes all erring rows into the error table
3. The system aborts the transaction or request
4. The system rolls back the transaction or request
The system does not invalidate indexes or 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 the request 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, 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:

Before you can log errors for MERGE loads, you must create an error table for the base data table into which you intend to do a MERGE load. See CREATE ERROR TABLE.
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 multiple-statement 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 when 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 the error is local in the strict definition of a local error.

The system response depends on the type of error:

Error	System Behavior
Local	System records error in error table, rejects error-causing rows from target table, and completes request or transaction successfully.
Nonlocal	System records error in error table, rejects error-causing rows from target table, and allows request or transaction run to completion to record error causing rows in MERGE load, then stops request or transaction and rolls back its inserts and updates.
Local and nonlocal

The database rejects data rows that cause local errors from the target table.
Instead of rejecting data rows that cause nonlocal errors from the target table, the system inserts those rows into the target table.

The 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:

Session Mode	Result
ANSI	Erring request stops and rolls back.
Teradata	Erring transaction stops and rolls back.

The 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 the data is built into a row format that corresponds to the target table.
The database detects this type of error before the start of data row inserts and updates. The following are examples of these types of errors:
- UDF and table function errors
- Version change errors
- Nonexistent table errors
- Down AMP request against nonfallback table errors
- Data conversion errors
  The system handles conversion errors that occur during data row inserts as local data errors.
The way the database handles these errors depends on the current session mode, as explained by the following table:
Session Mode Result

ANSI Erring request stops and rolls back.

Teradata Erring transaction stops and rolls back.

The database preserves error table rows logged by the stopped request or transaction and does not roll the rows back.

The 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 specified error limit was reached.
The database cannot handle the error.
The database detected a nonlocal (RI or USI) violation.
The system preserves the error rows that belong to the aborted request or transaction.
The database does not handle, but does preserve logged error rows for, the following error types:
- Out of permanent space errors
- Out 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, the database implicitly defines a USI on that primary key.
  
  The database cannot invalidate a violated implicit USI on a primary key because the database does not allow such a USI to be dropped and then recreated later.
  
  The database handles the errors in the following session mode-specific ways:
  
  Session Mode Result
  
  ANSI Erring request stops and rolls back.
  
  Teradata Erring transaction stops 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 after logging an error.
You cannot log errors for MERGE requests that specify unreasonable update or insert operations.
The request or transaction containing the erring MERGE request behaves as follows when the system detects the unreasonable INSERT specification:
Session Mode Result

ANSI Erring request stops and rolls back.

Teradata Erring transaction stops 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.

The request or transaction containing the erring MERGE request behaves as follows when an error occurs:
Session Mode Result

ANSI Erring request stops and rolls back.

Teradata Erring transaction stops 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.

The 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:

Session Mode	Result
ANSI	Erring request stops and rolls back.
Teradata	Erring transaction stops and rolls back.

The database preserves error table rows logged by the stopped request or transaction and does not roll the rows back.

WITH NO LIMIT
The 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
The 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:
Session Mode Result

ANSI Erring request stops and rolls back.

Teradata Erring transaction stops and rolls back.

The database preserves error table rows logged by the stopped request or transaction and does not roll the rows 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, or 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.

A dynamic MERGE request returns the following activities:

Activity	Returned When This Clause Is Specified for MERGE Request
PCLMRGMIXEDSTMT	WHEN MATCHED or WHEN NOT MATCHED If the MERGE request only updates rows of the target table, the database may internally convert the activity type to PCLMRGUPDSTMT. If the MERGE request only inserts rows into the target table, the database may internally convert the activity type to PCLMRGINSSTMT.
PCLMRGUPDSTMT	WHEN MATCHED THEN UPDATE
PCLMRGINSSTMT	WHEN NOT MATCHED THEN INSERT
PCLMRGDELSTMT	WHEN MATCHED THEN DELETE

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 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_TablesV[X] view to identify the violated Reference index (for information about Reference indexes, see Sizing a Reference Index Subtable ). You can determine whether an index error is a USI or referential integrity error by the code stored in the ETC_IdxErrType error column.

ETC_IdxErrType Value	Error
R	Foreign key insert violation
r	Parent key delete violation
U	USI validation error