Codes for conflict resolution (SQL codes)
The Code parameter identifies the SQL code, database message, or conflict type that the High-speed Apply Engine receives from the target database when a conflict occurs. You can specify a particular value only once in a configuration section. High-speed Apply Engine ignores any duplicate values.
The following table describes the values that you can specify for the Code parameter. The default actions that are listed in this table show the action that High-speed Apply Engine takes when it encounters the conflict type, but you have not defined a conflict resolution rule for that conflict type.
For more information about the actions that you can specify for the code types, see Actions for conflict resolution.
Value | DBMS | Description | Default action |
|---|---|---|---|
nnnn | Db2, Oracle, Db2 LUW | For Db2 and Db2 LUW, this value indicates an SQL code (SQLCODE) of the type nnnn. You can qualify this value with a plus (+) or minus (-) sign to limit the value to a positive or negative SQL code. If you do not specify positive or negative, High-speed Apply Engine assumes that nnnn is a positive value. For Oracle, negative SQL codes correspond to error codes listed in the Error Messages documentation. For example, code -942 would correspond to the 'ORA-00942: table or view does not exist' error condition. | -nnnn: Abort +nnnn: Warn |
MultipleRows | Db2, Oracle, Db2 LUW | This value indicates conflicts that might occur when multiple rows are changed by an UPDATE or DELETE statement in the target table. If you specify this code in the [InsertConflict] section, High-speed Apply Engine ignores it. | Warn |
MultiRowConflict | Db2 | This value indicates any conflict that occurs as High-speed Apply Engine processes any insert within a multiple row insert action. For conflicts that occur during a multiple row insert, High-speed Apply Engine resets all code values other than 0 or -911 (TimeOut) to -254 and processes the conflict by using the rule that specifies this code value. Valid actions for this code value are Abort and Retry. If the action is Retry, High-speed Apply Engine retries the multiple row insert that encountered a conflict by performing single row insert actions for all rows. | Retry |
Negative | Db2, Oracle, Db2 LUW | This value indicates any negative SQL code except those that are subject to the following: 1
| Abort |
NoRows | Db2, Oracle, Db2 LUW | This value indicates conflicts that might occur if no rows are changed by a statement in the apply request. Specifying NoRows is equivalent to specifying the specific SQL codes that result from no affected rows. For example:
| Warn |
Positive | Db2, Oracle, Db2 LUW | This value indicates any positive SQL code except those that are subject to a NoRows rule or an individual nnnn rule. 1 | Warn |
RIConflict | Db2, Oracle, Db2 LUW | This value indicates conflicts that arise from database-defined referential integrity (RI) constraints. Specifying RIConflict is equivalent to specifying the specific SQL codes that result from RI constraints in the target database. For example:
| None (High-speed Apply Engine takes action indicated by value of Negative code parameter) |
SQLWarnx | Db2, Db2 LUW | This value indicates any of the SQL warnings returned to the SQL Communication Area (SQLCA). For Db2 and Db2 LUW, the value x can be from 0 to 9 or the letter A. You can specify this code one time for each value of x. | Warn |
TimeOut | Db2, Oracle, Db2 LUW | This value indicates conflicts that can occur when an attempt to update the target database times out. Specifying TimeOut is equivalent to specifying the specific SQL codes that result from a database’s failure to respond. For example:
| Retry |
1 For more information, see Interaction between conflict resolution rulesand Processing order for conflict resolution rules.
Interaction between conflict resolution rules
Typically, you enter more than one conflict resolution rule in your configuration file. You can specify more than one rule for a given SQL code. For example, you can create a specific rule for the SQL code 100 (Code=100), and a general rule that applies to operations which do not change rows in the target database (Code=NoRows).
When an SQL code triggers more than one conflict resolution rule, the High-speed Apply Engine attempts to execute all actions that are specified in the applicable rules. The rules are processed in order (by their code types), as described in Processing order for conflict resolution rules.
High-speed Apply Engine does not execute all of the actions when any of the following conditions exist:
If you specify a rule for a particular SQL code (Code=nnnn, Code=TimeOut, or Code=RIConflict) and a rule for a positive or negative code type (Code=Negative | Positive), High-speed Apply Engine executes the action of the particular SQL code rule and does not execute the action of the Code=Negative | Positive rule.
Consider the Negative and Positive code types as defaults that apply to all numeric SQL codes except those subject to Code=nnnn, Code=TimeOut, or Code=RIConflict rules. (Code=TimeOut and Code=RIConflict resolve to the SQL codes that represent specific conditions for the database. High-speed Apply Engine treats these rules like rules with particular SQL codes.)
- When the Action parameter of a conflict resolution rule is Abort or Terminate, High-speed Apply Engine does not execute actions that are specified for any rules which are processed after the Abort or Terminate action.
Processing order for conflict resolution rules
The order that rules appear in the configuration file is not significant.
The High-speed Apply Engine processes the conflict resolution rules in the following order:
rules with nonzero numeric values for the Code parameter
Depending on the rules that you specify, this category can be Code=Negative | Positive, Code=nnnn, Code=TimeOut, or Code=RIConflict (TimeOut includes positive or negative values). For more information about these rule types, see Interaction between conflict resolution rules.
- Code=SQLWarnx (Db2 and Db2 LUW)
- Code=NoRows | MultipleRows
Code=0
Rules for SQL code 0 are processed last.
Actions for conflict resolution
The value of the Action parameter determines how the High-speed Apply Engine handles the conflict specified by the Code parameter. You can specify one or more actions for each code value.
The following table describes the values that you can specify for the Action parameter. For more information about the code types that you can specify.
Value | Description |
|---|---|
Abort | High-speed Apply Engine rolls back the current unit of recovery (transaction) and terminates the apply request. This value is the default action for Negative conflicts.
|
Continue | High-speed Apply Engine continues processing when it encounters the specified conflict type. |
Defer DeferStatement | High-speed Apply Engine writes the SQL statement that generated the specified conflict type to the conflict file. Processing continues with the next unprocessed statement. (This action is not normally used with distribution by UR because, when you defer one statement in a transaction, and then continue processing other statements, you break the integrity of the transaction.) |
DeferUR | High-speed Apply Engine writes the transaction that generated the specified conflict type to the conflict file. This value is valid only after a Rollback action.
|
Display | High-speed Apply Engine displays any information available from the DBMS regarding the error. |
Retry | High-speed Apply Engine attempts to apply the statement that generated the conflict again. This value is the default action for TimeOut and MultiRowConflict code values. This action is only valid with the following Code parameters:
You can specify the number of (or time for) retry attempts by using the Conflict parameters (Conflict-parameters). You can also use the Conflict parameters MaxFailedRetries and MaxRetryFail to define the maximum number of retries allowed and the action that the High-speed Apply Engine takes when the maximum number of retries is exceeded. To provide clear output messages when the High-speed Apply Engine retries a statement, add a Warn or Display action before the Retry action in your conflict resolution rules. |
Rollback | High-speed Apply Engine rolls back the current transaction when it encounters the specified conflict type.
|
RollbackStatement | High-speed Apply Engine commits that part of the current transaction that has already been processed and then rolls back the statement that generated the specified conflict type. Processing continues with the next unprocessed statement. |
Skip | High-speed Apply Engine skips the statement that generated the specified conflict type. Processing continues with next unprocessed statement. Normally, the RollbackStatement action precedes the Skip action, so that the statement that caused the conflict is not applied to the target table. |
SkipUR | High-speed Apply Engine skips the unit of recovery that generated the specified conflict type. This value is valid only after a Rollback action.
|
Terminate | High-speed Apply Engine commits the current transaction and terminates processing when it encounters the specified conflict type.
|
Warn | High-speed Apply Engine issues a warning message and continues processing when it encounters the specified conflict type. The apply request ends with a return code of 4. This value is the default action for NoRows, Positive, SQLWARNx and MultipleRows conflicts. |
The following actions cause High-speed Apply Engine to perform an implied commit action:
- Defer
- DeferStatement
- DeferUR
- RollbackStatement
- Skip
- SkipUR
- Terminate
The following actions cause High-speed Apply Engine to perform an implied rollback action:
- Rollback
- Abort
Related topics