By Tom Glaser

Date: Nov. 2024

IBM Db2 Query Monitor for z/OS (also referred to as Db2 Query Monitor) is a query analysis tool that analyzes SQL statements and provides accounting details about these queries. 

Features

Db2 Query Monitor enables you to:

• Collect, summarize, and display SQL resource consumption down to the level of the individual SQL statement text.
• Collect, summarize, and display Db2 object access statistics down to the individual table and index.
• Access data from multiple Db2 Query Monitor subsystems.
• View data from the perspective of a data sharing group.
• View active SQL statements that are currently being executed by Db2.
• Capture the full text of long SQL statements.
• Disable summary reporting for specific workloads, include or exclude specific -SQL Codes from exception or alert processing, set exception limits and thresholds, and define alert notification thresholds.
• View the expanded text description for an SQLCODE that is supplied by the IBM® utility program DSNTIAR.
• Display host variables.
• View information about the Db2 commands that are executed on monitored Db2 subsystems.
• Collect information relating to exceptional SQL-related events.
• Configure proactive notification to alert users of problems.
• Automate curative actions to deal with exceptional events as they occur.
• Access consolidated data and events for Db2 subsystems, whether within a single z/OS image or across multiple Db2 images, independent of the existence of a coupling facility.
• View and configure monitoring across your enterprise from a single console.
• …and more.

The purpose of this blog: Have you ever wanted to jump directly into the IBM Db2 Admin Tool from Db2 Query Monitor? Now you can. As of Oct. 30th 2024, you will be able to download and install APARs PH62040/PH62041 which will provide connectivity to the IBM Db2 Admin Tool (Admin Tool), directly from Db2 Query Monitor.  You must be at V13 of the Admin Tool.  This blog will highlight the integration between these two products.  You will start to see more integration between the IBM Db2 products.  As of Oct. 4^th, you can now jump from Db2 SQL Performance Analyzer into the Admin Tool.  More to come…  

IBM Announcement:

https://www.ibm.com/docs/en/dqmfz/3.4.0?topic=wnidqm-new-changed-functions-in-db2-query-monitor-340-since-ga

Installation/Update of Db2 Query Monitor Code for V3.4:

Following are the four APARs that are required for upgrade Db2 Query Monitor and the Admin Tool

PH62040/PH62041 – Admin Tool

PH63437/PH63438 – Query Monitor

Db2 Query Monitor V3.3 will not support calling the Admin Tool.

Key Functions:

Access to the Admin Tool will come in two phases.  This is phase 1 and it provides access to the Admin Tool under Summaries (option 1 under TSO).  Access to Current Activity (option 2 under TSO) and Exceptions (option 5 under TSO) will come in phase 2, at a later date.  

Db2 Query Monitor is accessible by two interfaces, ISPF (TSO) and CAE (Web Client).  This update only applies to the ISPF interface.

The first time you access Db2 Query Monitor, two pieces of information are needed to access the Admin Tool.  Select option “0” in Db2 Query Monitor (Settings):

Db2 Admin Tool Plan   - Enter the plan name that will be used to call Db2 Administration Tool.

Db2 Admin Tool ADB2FMP  - Enter Schema that will be used to call Db2 Administration Tool ADB2FMP stored procedure. If not specified, user id will be used as qualifier.

If you don’t know, jump into the Admin Tool and look for plans starting with ADB.  Also look up the Schema name for the storage procedure called ADB2FMP.  If not found, then it has not been installed.  This will now pass the correct data Db2 Query Monitor needs to call the Admin Tool.

Side note: Make sure you place an “/” next to the option “Display Full Command” as this will give you the full listing of drill-down accesses, like the following example:

Let’s walk through the new changes.  Jump into Summaries and let’s review “plans.”

Entering a “/” will display the possible drill-down actions: 

In the above screen shot, notice the high-lighted actions in blue?  They all start with A?. 

AP – Display Plan information in the Admin Tool

AK – Display Package information in the Admin Tool

AL – Display Collection information in the Admin Tool

AD – Display Database information in the Admin Tool

AS – Display Tablespace information in the Admin Tool

AO – Display Tables/Indexes (Object) information in the Admin Tool

Line Commands Online: https://www.ibm.com/docs/en/dqmfz/3.4.0?topic=summaries-activity-line-commands-ispf

For example, entering the number 41 or AP next to the plan will take you to plans.

Upon hitting enter, you will be taken to the Db2 subsystem name panel:

When you hit enter, this will jump you directly into the Admin Tool:

Notice you were asked to provide a Db2 subsystem ID?  That’s because Db2 Query Monitor had not drilled into the Db2 subsystem ID.  If you first go to the Db2 subsystem ID and then enter AP next to the SSID you wish to work with, Db2 Query Monitor will bypass asking for the Db2 ssid and take you directly into the Admin Tool.

Error Message: CQM863E - Not enough data to execute Db2 Admin Tool in this context.

There may be times where Db2 Query Monitor does not yet have the information it needs to drill into the Admin Tool.  Let’s take this example….we displayed plans on a specific Db2 subsystem and entered Db2 Query Monitor to display object information in the Admin Tool, using the “AO” command:

Db2 Query Monitor does not have the information needed; this screen does not provide the table and index names.  Drill down farther in the Db2 Query Monitor summary display to provide enough data to satisfy the Db2 Admin Tool query.  In this example, type in O for “Objects” and then follow that up with the AO command, like the following:

TCz Updates:  The Db2 Tools Customizer for z/OS is the tool used to install Db2 Query Monitor.  TCz will generate new CLISTs.  Users will need to specify the following in order to make the integration work:

·       Db2 Administration Tool library high-level qualifier

·       Db2 Administration Tool CLIST library

·       Db2 Administration Tool Table library

SQL Formatting

New ISPF commands were added to allow users to review formatted SQL statements using IBM Db2 Administration Tool ADB2FMP stored procedure.  The “VF” command is used for Summaries, the “SF” command is used for Current Activity, Exceptions and SQL Codes.   This update comes alongside the updates to access the Admin Tool from Db2 Query Monitor.   

The old method (which is still available, and for users who do to not have the Admin Tool):

The new method:

The SQL formatting feature is a part of the Admin Tool integration.  If you do not have the Admin Tool installed, SF/VF commands will produce an error.

In Summary:   

In this blog, I’ve shown you how to access the Admin Tool from Db2 Query Monitor, under the Summaries option.  This is just the beginning.  Access to the Admin Tool from Current Activity and Exceptions will be coming soon.  You will also see more and more IBM Db2 tools interacting with each other.   

If you have any suggestions on how to enhance batch reporting for Db2 Query Monitor or any other function within this tool, please submit an Aha on IBM’s web page.