Db2 Tools for z/OS

By Tom Glaser

IBM recently released support for monitoring and creating alerts based on GETPAGE activity by an object in Db2 Query Monitor for z/OS (via APAR PH51758).
This blog will describe this new enhancement and provide you with step-by-step instructions on how to set up and create notifications when the GETPAGE activity of a specific object increases. Also, optionally cancel the thread automatically.   
IBM Db2 Query Monitor for z/OS is a tool used to monitor SQL statements running in Db2 for z/OS. Prior to this APAR, Db2 Query Monitor was able to create an alert on GETPAGE activity, but only against a specific subsystem ID, plan, package, etc., and not for a specific object. With this APAR, Db2 Query Monitor has been enhanced to monitor GETPAGE activity against a specific object.
When a user requests data from a table, Db2 looks at the buffer pool. If the page requested is,

• In the buffer pool, no I/O occurs. 
• Not in the buffer pool, the buffer manager requests the page from the media manager, and I/O occurs.

The fewer the I/O requests, the better. 

There may be times when the access path for the query changes and Db2 unexpectedly makes more requests for GETPAGES or the table has more data than expected. Let’s say, for example, a transaction normally incurs 10 GETPAGES.  
Do you want to be notified when this transaction starts returning 1,000 or 10,000 pages? 

Prerequisite

You must have the Consolidated and Analysis Engine (CAE) installed to receive an alert against GETPAGE activity. For details on how to set up the CAE, see Installing CAE components.

How do I set it up?

The DB2 Query Monitor profile is the first place to start. Here, you need to set up an alert against GETPAGE activity. For a blog posting on how to set up alerts, see ALERTS. This can be done through the ISPF (TSO) or Web Client interface.

Let’s start with the profile now. Updating the profile can be done through any of the following:

ISPF (TSO)

1. On the Update Profile Line screen, set the Threshold Alerts field to Y.
   Note: If you don’t want to monitor a specific object, then you can update the Alert Getpages field. This blog is to enable monitoring of a specific object, so you should not update the Alert Getpages field.
   
2. Set the Include Getpage Objects field to Y.
   Help Panel:
   
3. Click Enter or PF3. The Alert GETPAGES Inclusion List screen is displayed.
   

This shows that Db2 Query Monitor is monitoring the SYSIBM.SYSPACKSTMT table and will alert us if the query exceeds 100 GETPAGES.

Web Client

Now that your profile has been updated to look for queries that exceed 100 GETPAGES for the SYSIBM.SYSPACKSTMT table, you will need to build your Action, Scope, and Response.

ACTION

• Set the Event Type field to GetPageObjectThresholdExceededProblem.
  

SCOPE

A scope defines the criteria that need to match for a certain type of alert. Our alert is against a Db2 object.

1. Click Elements and then +.
   
2. Select Constraint Based Scope and enter a name.
3. Select a subject as shown in the below example.
   Note: If you want to view specific user IDs, you can add the ID in the subject.user field.
   
4. Select the Events tab and provide a name to create one more scope.
   A new scope is created.
   

RESPONSE

A response is an action that the Db2 Query Monitor executes automatically when specific events of interest occur. This will tie the scopes to the Response, execute the email note, and if desired, cancel the thread.

1. Select the Event Scope from the drop-down.
   
2. Click Everything on the Domain Elements tab and change this to the scope that was created under Db2SqlStatement.
   
3. Select the checkbox to activate the event scope.
4. Click the Actions to Execute tab. Here, you can indicate what actions to take.
   We can send an email message (created under Actions) to the person to indicate that the event exceeded 100 GETPAGES.
   Note: Optionally, you can click the event Cancel Thread to automatically cancel this thread.
5. After running a query that produces more than 100 pages against SYSIBM.SYSPACKSTMT, I received an email message.
   

As you can see, the Web Client interface for Db2 Query Monitor can do a lot of things. It’s a cross between an artist and an inventor. If you have a certain task in mind, bring out your creative side and become that artist. Use the Web Client to invent the task and create the result you want to achieve.
If you have ideas on ways to make Db2 Query Monitor better, you can submit an Aha! 
#Db2 #Db2forz/OS #Db2Toolsforz/OS