Collecting positive SQL codes with IBM Db2 Query Monitor
jthyssen 50A79HCRE7 Visits (544)
One of the most popular features of IBM Db2 Query Monitor is the ability to capture negative SQL codes along with the associated the SQL text and SQLCA structure. This is useful if a program that executed an SQL statement does not write the SQLCA in case of errors. Typical examples would be very old programs or vendor software.
However, in some scenarios a positive SQL code is also considered an error. For example, an UPDATE statement unexpectedly ends with +100 (row not found). For this reason, the ability to optionally capture positive SQL codes was recently added with APARs PH01452, PH06444, P06825, and PH05826.
On the dialog for workload lines, you will now find entries to optionally capture positive SQL codes:
Also note that the dialog has been rearranged slightly and grouped into sections for easier viewing.
You can create multiple workload lines which allows you to have different options for different workloads. For example, you may create a CICS workload with one set of OPTKEYS and other options; a DISTSERV workload with another set of options; and so forth. If you only need to collect positive SQL codes for a certain subset of your workload, you can create a new workload with specific filters and enable positive SQL codes for that workload only.
FAQ: what is the difference between summary SQL codes and exception SQL codes? It is recommended that you also capture SQL codes as exceptions as this gives you additional information about the SQL statement including metrics, object metrics (if enabled), and more importantly host variables (if enabled). For some SQL codes such as -181, having host variable information can be very valuable. As host variables can potentially contain sensitive data access to view host variables is protected by a SAF resource so access can be restricted if needed.
Once collection of positive SQL codes have been configured and the monitoring profile has been refreshed on the collector, you will observe positive SQL codes under option 2.View SQLCODES. The screen shot shows the overview grouped by SQLCODE:
You can further drill down to individual statements.
From this list, you can view the full SQL text or the SQLCA structure.