The first article about alerts and automated actions in Db2 Query Monitor for z/OS has discussed the basic principles. See here for more details:

https://community.ibm.com/community/user/datamanagement/blogs/preethi-n/2023/04/03/generating-alerts-and-automated-actions-from-db2

You should have a basic understanding of the concepts of “Actions”, “Scopes” and “Responses” and should have a browser interface to Db2 Query Monitor available before implementing this practical example.

This article shows how Db2 Query Monitor can generate a WTO when a Getpages threshold for a particular workload is reached.

Step 1: Workload definition in monitoring profile

It is essential that Db2 Query Monitor’s data collector task is configured to capture alerts for your workloads. The example shows a workload which should raise an alert when more than 50,000 Getpages are consumed for the execution of aSQL statement:

In addition, thresholds for In-Db2 CPU time (5 seconds) and In-Db2 elapsed time (15 seconds) are defined. No threshold for the number of SQL calls is defined, no alerts for SQLCODEs will be raised for that workload. Make sure that your workload is included in the overall monitoring profile and your workload filters are defined accordingly.

Save that workload definition and refresh the monitoring profile of your target Db2 subsystems to activate your changes.

Step 2: Definition of an Event Scope

In the browser client, navigate to “Scopes” (click on the “Configuration” tab, then click “Scopes”). Click on the “Events” tab. Make sure the “Enable Text View” icon is NOT highlighted, then click on the green “+” icon to define a new Event scope from scratch:

Click on “Constraint Based Scope”. This means, you will define a scope which consists of one or more conditions (=constraints).

Enter a name of your choice for the new scope in the following dialogue.

After clicking “OK” the scope editor opens with predefined settings. Initially, there are two constraints combined with an AND operator. The first constraint, “event is in scope Everything” is a basic condition which always applies for any type of event. We leave this unchanged. We need to change the second constraint, initially set to “event is a ActionFailure”.

Click on the constraint “Action Failure”.

A tree view of available event types is shown. Expand “SQL Event”, then “Db2 Sql Event”, “Sql Problem”, “Alert Threshold Problem”.

Click on “Get Page Count Exceeded Problem”, then on “OK”. The scope editor is shown again and you can see that the constraint is changed to “event is a GetPageCountExceededProblem”.

It is possible to add more constraints (not only with AND but also with OR operators) but let us continue with this simple example.

By clicking on the “Apply changes” icon in the title row the scope definition is saved, and the event scope is shown in the Events hierarchy.

You can verify the contents of a scope by clicking on its name in the Events hierarchy. Whenever an Event is selected, the editor is opened in the right-hand side of the browser and you can make changes to the event’s constraints.

The configuration of that first simple Event Scope is finished now. Let’s continue with Actions.

Step 3: Defining an Action

Click on the “Actions” link on the left-hand side of the browser window to launch a list of available actions, organized into folders. The most relevant folders are CAE-Agent based actions and CAE-Server based actions.

Expand the “CAE-Agent-based-actions” folder and click on “Sample WTO”. The definition of the WTO action is shown in the editor window on the right-hand side. We do not want to modify the predefined sample but take it as a template for a new action. So, click on the “Clone selected action” icon in the title row to create a copy.

Assign a new name for the action.

You can edit the action after clicking “OK”.

The editor window for a WTO action is opened. It contains several fields with drop down menus.

Action Group:

Allows to select between CAE-Agent based Actions, CAE-Server based Actions and other action groups. Leave this at “CAE-Agent-based-Actions” because the WTO action is initiated by a CAE Agent on z/OS.

Subject Type:

Allows to specify the Domain Element type which is associated with the event. Usually, alerts are raised when a Db2 SQL Statement is executed. Leave this at “Db2SqlStatement”.

Event Type:

Specify the type of event which should trigger this action. The drop-down list shows all possible events for the specified subject type (in this case Db2SqlStatement). If you specified a different subject type, you would see different event types.

As we leave “Db2SqlStatement” for “Subject Type”, Db2 Query Monitor displays various problem and error events such as SQLError, SQLProblem, AlertThresholdProblem etc. It is important to specify an event type which matches the alert condition you want to take action on.

In our example we defined an Event scope for a “GetPageCountExceededProblem”. A “GetPageCountExceededProblem” is also, at the same time an “AlertThresholdProblem”, a “SqlProblem”, a “Db2SqlEvent”, a “SQLEvent”, an “Event” and even more. The “Monitored Information Types” link shows the complete hierarchy of event types.

You can choose “GetPageCountExceededProblem” or any of its superordinate event types as value for “Event Type”. In our example, we set “GetPageCountExceededProblem”.

Choose “2 – Master console information” as value for “Routing Code” and “12 – Important information messages” as descriptor code.

The message text box contains a predefined message text which you can tailor for your needs.

Db2 Query Monitor can replace variables, identified by “${…}” with runtime values, for example SQL texts, Db2 subsystem IDs, Db2 thread identification data, event details, etc.

The example uses four variables:

${event.type}: The type of event which is captured by Db2 Query Monitor. Our example works with “GetPageCountExceeded” events, so this would always be “GetPageCountExceeded”.

${subject.db2Ssid}: This correlates to the value of “Subject Type”. We have specified “Db2SqlStatement”. “Db2SqlStatement” has several attributes, “db2Ssid” is one of them.

Note: if we specified a Subject Type without attribute “db2Ssid”, the browser interface would not allow to save that configuration. Db2 Query Monitor does a consistency check so that every attribute in the message text can be derived at execution time depending on the Subject Type and Event Type.

${event.message}: A message text which can also contain some more attributes from the event and the subject (=Db2SqlStatement)

${subject.plan}: The PLAN name associated with the SQL statement

Change the statement text to:

SQL Statement problem ${event.type} on ${subject.db2Ssid}: ${event.message} for ${subject.plan}

Note that, when you type “${“ Db2 Query Monitor makes suggestions for variables and attributes which it derives from the Subject Type and Event Type. 

Click on the "Apply changes" icon as shown in the screenshot. Your saved Action should look like this:

The definition of your action is now complete. In the next step we combine the Event Scope and the Action into a Response.

Step 4: Defining a Response

Click on the “Responses” link in the configuration window to display a list of currently configured Responses. Click on the “+” icon in the title row to create a new response.

Enter a new name for the Response and click “OK”. The window which is displayed now contains two tabs. Use tab “Domain Elements & Events” to specify an Event Type or Event Scope which should apply to this response. Tab “Actions to Execute” shows a list of applicable Actions for a chosen Event Type or Event Scope.

In the “Domain Elements & Event Types” tab you need to specify first for which Events this automated action should be triggered. When you click on the field labeled “Everything” (below “Response applies to event on these Domain Elements”) a new window is opened that allows you to select a more specific Domain Element type. Click on “Db2 Sql Statement” in this window, then "OK".

You can also toggle between “Event Types” and “Event Scopes”. Earlier in this example, we defined an Event Scope for GetPageCountExceeded problems. So, click on “Event Types” after “Response applies to” and change “Event Types” to “Event Scopes”.

As you can see the list of objects shown in the tree view has changed. Now all applicable Event Scopes for Domain Element type “Db2 Sql Statement” are shown.

You should also find the Event Scope that you defined earlier in that list.

Select that scope. Next, click on the “Actions to Execute” tab. The window displays two boxes. The upper box contains a list of event triggers which can be chosen to initiate your action. Typically, you want your action to be triggered when the event is posted for the first time and optionally when it is captured repeatedly. So, check the two fields “An event is posted” and “The repetition count of an event increments”.

Next, have a look at the contents of the second box. It lists all the actions which can be launched for that event. Remember that not necessarily all actions which are available in your Db2 Query Monitor configuration are shown here. Db2 Query Monitor displays a list of possible actions depending on

• Subject Type and Event Type specified in an Action
• The Domain Event chosen on the "Domain Elements & Event Types" tab
• The Event Type or Scope itself

You should see the Action that you created earlier in that list of possible actions.

If your Action is not listed, check the definition of the Event Scope and the Action as shown earlier in this example. Make sure:

• Your Action contains “Db2SqlStatement” as Subject Type
• Your Action contains “GetPageCountExceededProblem” as Event Type
• Your Event Scope contains “event is a GetPageCountExceededProblem”
• Your Response applies to Domain Element “Db2SqlStatement”
• Your Event Scope is selected on the “Domain Elements & Event Types” tab.

Finally, click the “Domain Elements & Event Types” tab and make sure that the field “Enabled” is checked.

Click on the “Apply changes” icon to save the Response and activate it immediately. In case you just want to save the configuration without activating the Response uncheck the field “Enabled” and click on “Apply changes”.

Step 5: Test your event

When your Response is in status “Enabled” Db2 Query Monitor will launch the action when an alert is captured that meets the definition of your Event Scope. 

Make sure your monitoring profile contains at least one workload configured for threshold alerts as shown in Step 1.

Run SQL statements for which you expect the configured threshold be reached (you can use Db2 Query Monitor’s Activity Summary to see the metrics for your SQL statements).

Alerts detected by Db2 Query Monitor are displayed on the Message Board in the browser client which can be reached by clicking on the “Alerts” link. Users can configure their own Message Boards but there should be at least one shared Message Board available:

You will likely see more messages than expected. By default, Db2 Query Monitor also generates messages for several types of events which are not related to alerts from Db2 workloads.

You should see a message which originates from an SQL statement exceeding the Getpages alert threshold in your workload. Look for messages with “Get Page Count Exceeded Problem” in the message text.

Note: Alerts from the Db2 workloads are displayed on the Message Board no matter whether you configured a Response with automated action for that alert. If your expected alert is not shown on the Message Board either

• The alert was not generated (maybe the workload was excluded from monitoring) or
• The threshold values have not been reached or
• The alert caused the counter of an existing message be incremented (i.e. it was a repeating event)

Be aware that not every single occurrence of an alert will generate a new message on the message board. Check the value for column “Rep.Count” (repetition counter) to see if multiple alerts have been detected for that event type.

Click on a specific message text for more details.

The detail window for a message contains several tabs. Under the “Attributes” tab you will see a list of all occurrences for that event type. It is possible that the first occurrence is several days old. Scroll through the list of occurrences and select one. You can see the attributes of that specific occurrence in the right-hand side of the window. You can also click on the “Text/HostVars” tab to see the complete SQL text and (when available) the contents of the host variables for that specific SQL execution.

The Message Board works independently of Scopes, Actions or Responses. You can acknowledge, unacknowledge, clear or restore messages, change their priority, create your own tailored message boards, etc. For more details see the Db2 Query Monitor documentation:

https://www.ibm.com/docs/en/dqmfz/3.3.0?topic=components-alerts

We do not dive deeper into the Message Board at this time. Make sure your expected alerts are shown on the Board before continuing.

When Db2 Query Monitor tries to launch an automated action from an alert it also logs this to the “Action Console”. Click on “Tools”, then “Action Console” in the browser interface. Select “Action History” from the tree on the left side of the panel. The interface shows a list of all actions launched by Db2 Query Monitor in chronological order. Every row in this table represents the execution of an automated action.

The “Status” column indicates whether the execution of that action was successful. In the event of an error, click on that entry and see the “Error Log”, shown in the lower part of the window.

If you can find an entry for your automated action with status “completed” you should also see the results of your action. In our example we generated a WTO message, starting with “CQMU001I”. Check your z/OS console log for the generated message:

Security Considerations

As mentioned earlier, the configuration of Alerts, Scopes, Actions and Responses is not open to every user of Db2 Query Monitor. Access to the web browser interface as well as access to the configuration capabilities can be secured by the “CQM.CAE” facility class profiles in RACF or equivalent security products. For more information see “Securing the functions users can access in the CAE” in the product documentation:

https://www.ibm.com/docs/en/dqmfz/3.3.0?topic=monitor-reviewing-setting-proper-authorizations

Summary

This example has shown the necessary steps to define an automated WTO action which Db2 Query Monitor triggers based on specific types of Events. The underlying “Knowledge Based Management Language” offers many options but is not easy to learn. For the specific purpose of get alerted from SQL statement executions by Db2 Query Monitoring we focus on specific subsets of KBML.

In the next part we will expand that example and generate mails from alerts in addition to or as an alternative to WTOs. We will also show how a more generic Event Scope can be defined that includes all possible types of threshold exceptions.