Troubleshooting exceptions
Exceptions are rows that a Q Apply program did not apply to targets or reworked because of conflicts or SQL errors. In most cases you need to determine the cause of exceptions and correct problems to avoid them.
Conflicts can occur when an application other than the Q Apply program is updating the target. For example, the Q Apply program might try to insert a row that already exists at the target. If the conflict action for the Q subscription is F (force), Q Apply turns the INSERT into an UPDATE, and the original INSERT statement is logged in the IBMQREP_EXCEPTIONS table.
SQL errors cover a broad range of potential problems. For example, if the recovery log at the target server filled up, SQL errors would be generated when the Q Apply program tried to apply rows.
You can use two different tools to view details about problem rows:
- Q Replication Dashboard
- Start by looking at the Exceptions column on the Summary tab. If the replication configuration has any exceptions, a red icon appears. Double-click the table cell to open a window with details about recent exceptions. Click Generate Report for more comprehensive information.
- Replication Center
- Use the Exceptions window. To open the window, right-click the Q Apply server that contains the Q Apply program that you want to view exceptions for and select . Use the window to create a report that shows which Q subscriptions the problem rows were part of, when the problems occurred, and the reasons that the rows could not be applied.
The following list shows the types of problems that can prevent the Q Apply program from applying rows:
- Unexpected SQL errors
- Rows that caused SQL errors that were not defined as acceptable for the Q subscription.
- Acceptable SQL errors
- Rows that caused SQL errors that were defined as acceptable. You define acceptable SQL errors when you create a Q subscription.
- Value-based conflicts
- Rows that caused conflicts such as an attempt to delete or update a row that did not exist at the target, or to insert a row that already existed. The Q Apply program records the type of conflict detection that was used (check only key values, check changed non-key columns as well as key columns, check all non-key columns as well as key columns), and whether the row was applied despite being saved. When you create a Q subscription, you can specify that the Q Apply program should apply some rows even when they cause conflicts.
- Version-based conflicts
- Rows that caused conflicts in peer-to-peer replication, such as an attempt to update a row with the values from older row, or an attempt to insert a row when a newer row with the same key already existed at the target.
When the Q Apply program saves rows in the IBMQREP_EXCEPTIONS table, it records the time when the error or conflict occurred, the reason for the error, and the type of SQL operation that resulted in the problem. The Q Apply program also saves the names of the receive queue and Q subscription, and source commit information for the transaction.
For unexpected SQL errors and acceptable SQL errors, the Q Apply program saves the SQL code and SQL state code returned by DB2® for the transaction, as well as the error message tokens from the SQLCA structure that is used for executing the transaction. For value-based conflicts and version-based conflicts, the Q Apply program records the reason that the row could not be applied. Rows that are not applied are saved in SQL format in the TEXT column of the IBMQREP_EXCEPTIONS table.
In peer-to-peer replication, the Q Apply program saves rows that it did not apply at the server where the conflict or SQL error occurred.