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.

Related topics

Conflict-resolution

MaxConflicts

InsertConflict-parameters

DeleteConflict-parameters

DDLConflict-parameters

Creating-conflict-resolution-rules


Value

Description

Default action

nnnn

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, the High-speed Apply Engine assumes that nnnn is a positive value.

-nnnn: Abort

+nnnn: Warn

MultipleRows

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, the High-speed Apply Engine ignores it.

Warn

MultiRowConflict

This value indicates any conflict that occurs as the 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

This value indicates any negative SQL code except those that are subject to the following: 1

  • RIConflict
  • TimeOut
  • individual -nnnn rule

Abort

NoRows

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:

  • This value is equivalent to SQL code 100.

Warn

Positive

This value indicates any positive SQL code except those that are subject to a NoRows rule or an individual nnnn rule. 1

Warn

RIConflict

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:

  • This value is equivalent to SQL codes -530, -531, and -532.

None

(High-speed Apply Engine takes action indicated by the value of Negative code parameter)

SQLWarnx

This value indicates any of the SQL warnings returned to the SQL Communication Area (SQLCA). 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

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:

  • This value is equivalent to SQL codes -913, -911, and -904.

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).

Important

When you indicate specific codes, remember that some codes might be different, depending on whether the statement is being processed as dynamic or static SQL. You might want to include all applicable codes (for example, SQL codes -302 and -433 represent a column value that is too long for the specified column).

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 the rules appear in the configuration file is not significant.

The High-speed Apply Engine processes the conflict resolution rules in the following order:

  1. 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.

  2. Code=SQLWarnx
  3. Code=NoRows | MultipleRows

  4. 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.

  • For distribution by an object (Distribution by object), High-speed Apply Engine rolls back all statements processed since the previous commit action, including the statement that generated the conflict.
  • For distribution by UR (Distribution by unit of recovery), High-speed Apply Engine rolls back the entire transaction.

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.

  • For distribution by an object, High-speed Apply Engine writes all statements processed since the previous commit action to the conflict file, including the statement that generated the conflict. High-speed Apply Engine continues processing with the next unprocessed statement.
  • For distribution by UR, High-speed Apply Engine writes the entire transaction to the conflict file, including any statements that have not been processed. High-speed Apply Engine continues processing with the next transaction.

Display

High-speed Apply Engine displays any information available from the DBMS about 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:

  • Timeout
  • MultiRowConflict
  • RIConflict
  • DB2 SQL codes -913, -911, -904, -530, -531, -532, and -254.
  • Oracle messages that reflect a timeout or RI conflict condition for the target tables

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.

  • For distribution by an object, High-speed Apply Engine rolls back all statements since the previous commit action, including the statement that generated the conflict. High-speed Apply Engine continues processing with the next unprocessed statement.
  • For distribution by UR, High-speed Apply Engine rolls back the entire transaction. High-speed Apply Engine continues processing with the next transaction.

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.

  • For distribution by an object, High-speed Apply Engine skips all statements processed since the previous commit action, including the statement that generated the conflict. High-speed Apply Engine continues processing with the next unprocessed statement.
  • For distribution by UR, High-speed Apply Engine skips the entire transaction, including any statements that have not been processed. High-speed Apply Engine continues processing with the next transaction.

Terminate

High-speed Apply Engine commits the current transaction and terminates processing when it encounters the specified conflict type.

  • For distribution by an object, High-speed Apply Engine commits the statements processed since the previous commit action, including the statement that generated the conflict.
  • For distribution by UR, High-speed Apply Engine commits the statements processed since the previous commit action, including the statement that generated the conflict, but not including any statements that have not been processed.

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

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*