Using the dashboard to identify and diagnose data exceptions
The Q Replication Dashboard makes it easy to identify and diagnose data exceptions at target tables by displaying the exceptions prominently in the health summary view, helping you find out what caused them, and providing more detailed information about them.
Figure 23. Exception shown in summary view
The icon in the Exceptions column of the Replication Configurations table turns to a red square when an exception occurs on at least one send queue-receive queue pair in the configuration:
When you expand the table, you can see which queue pair had the exception and use detail windows and reports to diagnose the problem.
One of the ways that exceptions occur is when an application other than the Q Apply program is updating the target table. In this tutorial, the runwork application creates exceptions at the STAFF target table by taking the following actions:
- Deletes a row from the target table
- Updates the same row in the source table
When the update is replicated, the Q Apply program cannot find the row in the target table to update, and an exception occurs.
Follow these steps to identify and diagnose an exception:
- From the summary page, notice the red square that indicates at least one
exception in the LONDON.ASN > DALLAS.ASN configuration:
In the Replication Configurations table, click on the unidirectional icon to the left of the upper LONDON.ASN Q Capture entry to expand your view of the configuration:
Figure 24. Exceptions column expanded
You can see that the exceptions are occurring only on the SEND_RECVQ2_LON_DALLAS send queue-receive queue. The table shows the number of exceptions.
- Select the table cell with the red square. A window opens that provides
details about the most recent exception.
Figure 25. Exception detail window
The "Target table:" field shows that the exception occurred at the STAFF table, and that the Q Apply program was attempting an UPDATE operation at STAFF. The SQLCODE and SQLSTATE that help identify the cause of the exception are listed, along with links to reference information in the DB2 Information Center for more detailed explanations. You can also see that the row was applied despite the exception ("Applied: Yes").
In the Reports area of the window, you can specify a time interval for viewing detailed reports about exceptions that occur for a send queue-receive queue pair.
- To diagnose the exception more closely, select Generate Report. The
Exceptions Report opens in a new browser window. The report provides a
statistical summary of all exceptions reported during the time range that you
specified on the details window. It also has a row for each exception that
includes the following information:
- The time of the exception
- The SQL statement that the Q Apply program was trying to execute when the exception occurred
- The conflict rule that was specified for the Q subscription
Figure 26. Exceptions report
The exceptions that are occurring at the STAFF table were caused by the same UPDATE operation, an attempt to update a row with a value of 10 in the ID column. Because the runwork application deleted the row before it inserted the same row in the source table at the LONDON database, a SQLCODE of 100 is generated (no row was found that meets the specified search condition).
Although the problem triggered an exception, the Q Apply program applied the transaction to the target table for the following reason:
- The STAFF0001 Q subscription specifies that the Q Apply program should check the key values of the TARGET table for conflicts before applying a row.
- Because the runwork application deleted the row with an ID of 10, there is no key in the STAFF table that matches the key in the UPDATE statement, so there is no conflict. The Q Apply program turns the UPDATE into an INSERT and inserts the row with only the values that were specified in the UPDATE statement (ID of 10, SALARY of 74,999.00).