When the action of REPERROR is EXCEPTION or TRANSEXCEPTION, you can map the values of operations that generate errors to an exceptions table and, optionally, map other
information about the error that can be used to resolve the error. See “About the exceptions table”.
To map the exceptions to the exceptions table, use the following options of the MAP parameter:
● MAP with EXCEPTIONSONLY
● MAP with MAPEXCEPTION Using EXCEPTIONSONLY
EXCEPTIONSONLY is valid for one pair of source and target tables that are explicitly named and mapped one-to-one in a MAP statement; that is, there cannot be wildcards. To use
EXCEPTIONSONLY, create two MAP statements for each source table that you want to use EXCEPTIONSONLY for on the target:
● The first, a standard MAP statement, maps the source table to the actual target table.
● The second, an exceptions MAP statement, maps the source table to the exceptions table (instead of to the target table). An exceptions MAP statement executes immediately
Handling Replicat errors during DML operations
regular MAP statement that contains the same source table. Use a COLMAP clause in the exceptions MAP statement if the source and exceptions-table columns are not identical, or if you want to map additional information to extra columns in the exceptions table, such as information that is captured by means of column-conversion functions or SQLEXEC.
Using MAPEXCEPTION
MAPEXCEPTION is valid when the names of the source and target tables in the MAP statement are wildcarded. Place the MAPEXCEPTION clause in the regular MAP statement, the same one where you map the source tables to the target tables. Replicat maps all operations that generate errors from all of the wildcarded tables to the same exceptions table; therefore, the exceptions table should contain a superset of all of the columns in all of the wildcarded tables. In addition, because you cannot individually map columns in a wildcard
configuration, use the COLMAP clause with the USEDEFAULTS option to handle the column mapping for the wildcarded tables (or use the COLMATCH parameter if appropriate), and use explicit column mappings to map any additional information, such as that captured with column-conversion functions or SQLEXEC.
About the exceptions table
Use an exceptions table to capture information about an error that can be used for such purposes as troubleshooting your applications or configuring them to handle the error. At minimum, an exceptions table should contain enough columns to receive the entire row image from the failed operation. You can define extra columns to contain other information that is captured by means of column-conversion functions, SQLEXEC, or other external means.
To ensure that the trail record contains values for all of the columns that you map to the exceptions table, use the following parameters in the Extract parameter file:
● Use the NOCOMPRESSDELETES parameter so that all columns of a row are written to the trail for DELETE operations.
● Use the GETUPDATEBEFORES parameter so that Extract captures the before image of a row and writes them to the trail.
Example 1 EXCEPTIONSONLY
This example shows how to use REPERROR with EXCEPTIONSONLY and an exceptions MAP statement. This example only shows the parameters that relate to REPERROR; other parameters not related to error handling are also required for Replicat.
REPERROR (DEFAULT, EXCEPTION)
Handling Replicat errors during DML operations
In this example, the REPERROR parameter is set for DEFAULT error handling, and the EXCEPTION option causes the Replicat process to treat failed operations as exceptions and continue processing.
There are two MAP statements:
● A regular MAP statement that maps the source table ggs.equip_account to its target table equip_account2.
● An exceptions MAP statement that maps the same source table to the exceptions table ggs.equip_account_exception.
In this case, four extra columns were created, in addition to the same columns that the table itself contains:
DML_DATE OPTYPE DBERRNUM DBERRMSG
To populate the DML_DATE column, the @DATENOW column-conversion function is used to get the date and time of the failed operation, and the result is mapped to the column. To populate the other extra columns, the @GETENV function is used to return the operation type, database error number, and database error message.
The EXCEPTIONSONLY option of the exceptions MAP statement causes the statement to execute only after a failed operation on the source table. It prevents every operation from being logged to the exceptions table.
The INSERTALLRECORDS parameter causes all failed operations for the specified source table, no matter what the operation type, to be logged to the exceptions table as inserts.
NOTE There can be no primary key or unique index restrictions on the exception table.
Uniqueness violations are possible in this scenario and would generate errors.
Example 2 MAPEXCEPTION
This is an example of how to use MAPEXCEPTION for exceptions mapping. The MAP and TARGET clauses contain wildcarded source and target table names. Exceptions that occur when processing any table with a name beginning with TRX will be captured to the fin.trxexceptions table using the designated mapping.
MAP src.trx*, TARGET trg.*,
MAPEXCEPTION (TARGET fin.trxexceptions, COLMAP (USEDEFAULTS,
ACCT_NO = ACCT_NO,
OPTYPE = @GETENV (“LASTERR”, “OPTYPE”), DBERR = @GETENV (“LASTERR”, “DBERRNUM”), DBERRMSG = @GETENV (“LASTERR”, “DBERRMSG”) )
);