Using advanced functions to process SQL error alerts

This topic describes advanced features and techniques that you can use to process SQL error alerts captured by Db2 Query Monitor. SQL errors are captured by Db2 Query Monitor. However, determining the reason for such SQL errors has required a user to log in to one of the user interfaces to determine the root cause of the error.

Not all SQL errors carry the same urgency. For example, a -904 caused by an object being stopped might be considered more severe than a -904 caused by for some other reason. Since Db2 Query Monitor includes the information from the SQLERRMC field of the SQLCA, it would be useful to use that information to filter alert responses, and even include that information in an email or WTO message.

The SQLERRMC from the SQLCA is passed in the MessageTokens field of Db2 Query Monitor alerts. This information provides examples of how this information can be used to enhance Db2 Query Monitor alert processing.

MessageTokens alert attribute in Scopes

Scopes are used to define groups of events based on specific criteria. Typical scope creation uses the Scopes Editor in the Configuration Browser to create and edit scopes. The MessageTokens attribute is not available in the basic Scopes Editor. However, using a couple of advanced Db2 Query Monitor features, you can access and use the MessageTokens attribute in Db2 Query Monitor scopes.

Let’s look at a scope created using the scope editor. In the scope pictured below, we have focused on SQL Errors for which Db2 Query Monitor has generated alerts. The scope allows further fine tuning such that actions can be limited to a list of installation defined “Critical SQL Codes”. Here is what the completed scope looks like.

Now let’s take the case where additional notifications need to happen if the reason for resource unavailable SQL Codes is that the resource is in a “stopped’ status. In this case, Db2 issues a -904 with a reason code of “00C90081”. For SqlError event types, the Db2 reason code is included in the MessageTokens field of the event record.

Here are the steps to create a scope which checks for a “stopped status reason code” in the “MessageToken” field of an SqlError with a -904 SQL Code.

  1. Create a new scope using the Scopes Editor of the Configuration Browser. This would be an event scope for -904 SQL Codes. The basic scope is shown here:
  2. Once the basic scope is created, use the text view mode of the editor to access an editable textual view of the scope source as shown here:

    Here is the text editor view:

  3. Now use the editor to add an “and” condition at the end of the scope to check for the Db2 reason code of “00C90081” that indicates a resource is in “stopped” status. The function you use to check the reason code is “regexMatches”. The syntax of the function is as follows:
    regexMatches(“search_string”,event.getAttribute(“MessageTokens”).toString())

Where the search_string is the Db2 Reason Code and 
The event.getAttribute function is used to get the MessageTokens from the SqlError event

    Here is the scope after editing:

    Save the changes by clicking on the red check mark as shown below:

    The completed scope is shown below.

    Click the Text Mode button to exit the text mode view.

    You have now returned to the default scope editor view:

MessageTokens alert attribute in Responses

In addition to the use of the MessageTokens field to provide advanced filtering in scopes, the field can also be used in Responses. It may be helpful and even desirable to include the information from the MessageTokens field in an email or WTO message in response to and alert.

The complete MessageTokens field can be accessed using the getAttribute function of QM. In addition, if there is more than one field included in MessageTokens you can extract the fields and compose a more usable message when an alert is triggered. For example, using the getAttribute and regexExtract functions, an email response to a -904 SQLCode can be coded as follows:

-904 SqlCode error.

Db2 subsystem: ${subject.db2Ssid}
Plan         : ${subject.plan}
Package      : ${subject.programName}
Auth ID      : ${subject.user}
Timestamp    : ${event.timestamp}
SQLCODE      : ${event.HighestSqlCode}
Current ts   : ${currentDatetime}
URL          : ${event.URI}
SQLCODE      : ${event.message}
MessageTokens: ${event.getAttribute("MessageTokens")}
SQLCA        : ${event.getAttribute("SQLSTATE")}
Db2 reason code : ${regexExtract("^(.{8}).(.{8}).(.*)$", event.getAttribute("MessageTokens").toString()).get(1)}
Type of resource: ${regexExtract("^(.{8}).(.{8}).(.*)$", event.getAttribute("MessageTokens").toString()).get(2)}
Resource name   : ${regexExtract("^(.{8}).(.{8}).(.*)$", event.getAttribute("MessageTokens").toString()).get(3)}

Using the example above, will cause Db2 Query Monitor to generate an email that looks like this:

-904 SqlCode error.

Db2 subsystem: IA1A
Plan         : DSNESPCS
Package      : DSNESM68
Auth ID      : CSTHUB
Timestamp    : 1641336134443
SQLCODE      : -904
Current ts   : Thu Jan 06 10:25:03 GMT-05:00 2022
URL          : Not specified
SQLCODE      : Query received code -904, indicating that a resource was unavailable (e.g., -904: unsuccessful execution caused by an unavailable resource)
MessageTokens: 00C90081Ÿ00000100ŸCQMTJHD
SQLCA        : 57011

Db2 reason code : 00C90081
Type of resource: 00000100
Resource name   : CQMTJHD