Top 10 reasons why you need to install the Query Monitor Consolidated Analysis Engine (CAE), also known as the Web Client interface

Tom Glaser: tglaser@rocketsoftware.com

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 text statement.
• Collect, summarize, and display Db2 object access statistics down to the individual table and index.
• Access data from multiple 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 -SQLCODES 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: Db2 Query Monitor comes with two interfaces; ISPF and a Web Client, the latter  referred to as the Consolidated Analysis Engine (CAE).  Many organizations only install the ISPF (TSO) interface for various reasons.  I suspect this is in part that many are unaware of the benefits of the CAE.  This article will high light the 10 top reasons I think you should install the Web Client.  Going forward in this article, I will refer to the Web Client as CAE.  I have not listed the 10 top reasons in any particular order.  Some of these reasons may not benefit you.  For example, the CAE is required in order to monitor at the data sharing group level.  If your company doesn’t run data sharing, this reason is of no interest to you.  One company may wish to export their data from the CAE and import it into Excel.  Another company may not find that of value.

After my review of the top 10, I will discuss “Best Practices for Deploying the CAE.”

ISPF and the CAE UIs

The following figure shows an example of the ISPF and the CAE interface.  The exact data will be found in each interface.  However, the CAE interface provides additional functionality over ISPF/TSO.  Let’s review what I think are the top 10 reasons you should install the CAE interface in your environment.

Index to the Top Ten:

·       Data Sharing

·       Alerting: -551; -805; -904; -911/-913  (or any SQL Code)

·       Alerting: email notifications

·       Automation (cancel threads)

·       Connect to a Db2 Archive of Stored performance data

·       Charting (visualize data in a pie chart)

·       Workload Comparison

·       TopN Filtering Reports

·       Exporting Statistics

·       Custom Views Manager

Data Sharing

Data Sharing is a collection of one or more Db2 subsystems that share Db2 data.  Db2 subsystems that access shared Db2 data must belong to a data sharing group.  All members of a data sharing group share the same Db2 catalog and directory, and all members must reside in the same Parallel Sysplex.

Db2 Query Monitor includes both mainframe and GUI components.  Click here for an example of how Db2 Query Monitor works in a data sharing environment.

Looking at screen Figure 1 for ISPF, if you select the data sharing group, Query Monitor will display all members:

Figure 1

Figure 2

A key benefit is to view the workload on all data sharing members.  For example, you can drill down by Plan name and see the accounting data for each member.  The same information is in the CAE.

ALERTING: Let’s first define what is an “alert”

•        SQL Events that require attention

•        Individual SQL calls

•        Four user defined thresholds (a setting of zero turns it off):

•        Alert CPU; Alert Elapsed; Alert Getpages; Alert SQL Calls

•        By default, data is collected on all negative SQL statements

•        Alerts will be displayed on the blackboard (message board)

•        AutoClear: You can set Event AutoClear to a shorter period if needed

•        Alerts are not intended as a means of storing data that you go back to and review at leisure; exceptions already serve that purpose.

•        You can create Message Boards that filter messages to achieve various display results.

•        Monitor all subsystems from one screen

•        Start/stop alerts company-wide from one screen

Alerting: -551; -805; -904; -911/-913  (or any SQL Code)

Are you interested in receiving alerts?  This could top the list as the best thing about Query Monitor CAE.  Let’s start with a Db2 Resource Unavailable condition (-904).  This could be a critical issue, such as a buffer pool no longer being available, one of the Db2 Catalog tables may not be available, to a tablespace being in Check pending status.  The -904 “reason” code will display more details.   Monitoring this return code will notify the Db2 DBA that an issue just occurred, and someone needs to look at it as soon as possible; this could be via page or email. 

Here's an example of a -904 being written to SYSLOG in SDSF:

Figure 3 (The error message in SYSLOG written by QM)

Figure 4  (The actual error)

Alerts: email notifications

CAE has the capability to send email messages when an alert is encountered.  CAE uses the Knowledge-Based Management Language (KBML) to create an outline of an email message.  Taking the same Db2 SQL Code as above (-904), you can build an email message using an Action.

Figure 4

When the -904 is received, the email message will be sent, along with any of the accounting data you need, the SQL statement, and a link to take you directly into the CAE interface.  See the following figure for an example:

Figure 5

Automatically cancel threads

There are two built-in cancel thread actions included in Db2 Query Monitor.

Automatic Cancel Thread

Cancel a thread associated with the SQL statement without user authentication. This cancel thread

action:

• This cancel thread action uses the AUTHID of the CAE Agent address space to cancel the thread.

• This cancel thread action is intended for use in responses.

Cancel Thread

Cancel a thread associated with the SQL statement after authentication and under the user's AUTHID.

• This cancel thread action is available for use in custom launches.

Why would you want to automatically cancel a thread?  One good reason is a query that has been running for a very long time, a SELECT statement coming from DSNTEP2, SPUFI or QMF and it has consumed 20 minutes of CPU.  For these kinds of run-away queries, auto cancel is a nice option.

Connect to a Db2 Archive

The CAE interface connects to a “source.”  A source can be an active Query Monitor Subsystem or an archive connection to your Performance History Database.  In figure 6, you can see an option called Archive Connection:

Figure 6

When connecting to the archive connection, you connect to your performance database.  You now have access to everything that has been offloaded.  In lieu of running SQL queries against the Db2 tables, Query Monitor pulls in this data and treats it as if it were online.

Charting

CAE provides the ability to take a data grid and convert it to a pie chart.  In figure 7, we are looking at all the plans that executed during a specific interval (period of time).  This data can then we sliced and diced various ways, such as by CPU, by Elapsed time, getpages, etc.  You can also see that this can be by plan, package, Authid, etc.  

Figure 7

Workload Comparison

When viewing summaries, you can specify one workload as a baseline workload and a second workload as comparison workload to view differences in metrics for the two workloads.

Comparing workloads allows you to:

-             -        Identify and analyze the differences in the performance metrics for the workloads you select. If the performance of a workload has degraded, you can compare workloads to find the plans, programs, or SQL statements that are contributing most to the decline in performance.

-             -        Measure the effect of any changes to a workload, such as tuning efforts, upgrading DB2 to a new version, or offloading some queries to an accelerator.

Baseline workload - The workload to which the comparison workload is to be compared.

Comparison workload - The workload that you compare to the baseline workload. The comparison workload is the workload for which you want to diagnose a particular problem, such as decreased performance.

The baseline workload and the comparison workload can be the same workload in different intervals or they can be workloads from different DB2 subsystems.  In the following figure, we are comparing a singleton SELECT statement from the current interval to 3 days earlier.  Query Monitor will show the before, after and the percentage in change.

Figure 8

TopN Filtering Reports

You can use top-n filters to limit the number of rows that are returned by the CAE Server when viewing SQL text.  The TopN drop-down is only applicable if you are in Perspective: Summaries > SQL text. When viewing large volumes of data, the top-n feature enables you to reduce the system resources that are required to view data of interest. 

The number of rows (n) that are returned by the top-n filter are controlled by the Rows dropdown, located beneath the data table.  There are approx. 150 various Topn reports.  In figure 9, you can see a list of many types of reports, such as User Defined Function (UDF) Db2 CPU Time.

Figure 9

Exporting Statistics

•       Query Monitor CAE user can export data in either:

•        CSV

•        PDF

•       CSV Format – can be loaded into Excel to produce graphical charts of the data

•       Scope

•        All rows in current data page – Export current page

•        All rows matching selection – Export all data

•       Exported data file is created with unique name and placed in web browser default directory path

•        Attach PDF report to email

•        Open CSV format in a spreadsheet for further analysis

Figure 10

Custom Views Manager: Create views (shared/private)

Custom Views is similar to CSORT/SORT on ISPF.  However, the CAE provides additional columns not available on the ISPF interface.  I like to create the views I like to use most, and then have one view that lists all of these columns, for those difficult SQL statements you are trying to troubleshoot.  Here are the additional columns of data you don’t get in the ISPF:

Figure 11

Best practices for deploying the Web Client (CAE)

•        Recommend installing Agent & Server under USS on z/OS.

•        When applying maintenance roll the server updates forward first, then do the agents.  A server can support back level agents.

•        TCz job C0CUNPX lays down initial image.

•        TCz job C1CUPPT is run for each PTF and upgrades the R/W image to latest maintenance:

•        Jobs must be run on each environment (dev, test, prod, …).

•        If you copy (Prefix/cqmv3r3/var) zFS between environments, you lose alerts and configuration for alerts.

•        CAE Server STC user ID must have read/write authority here.  Run the TCz jobs C0CUNPX and C1CUPPT under same user ID.

•        Job CQMCHMOD can assist with USS file permissions, but it needs to be run with super user authority.

•        CQM_LOGS  - Log directory:

•        On separate zFS to protect against out-of-space conditions.  Otherwise on same zFS as CQM_VAR_HOME.

•        Increase CQM_HEAP and REGION as much as you can, to prevent out of memory conditions.

•        We see a lot of issues with new installs around port conflicts.  Customers need to remember to reserve all the ports used by CQM.

•        Certificates for HTTPS Server:

•        SCQMSAMP(CQMCCERT) – create self-signed certificate.

•        SCQMSAMP(CQMICERT) – import self-signed certificate into CAE Server key store.

•        Modify proc CQMCAESV to set CQM_CAE_KEYSTORE_TYPE, CQM_CAE_TRUSTSTORE, and CQM_CAE_KEYSTORE to SAF keyring.

Figure 12

In Summary: Once CAE has been installed, you’ll be glad you did it.  The GUI response is great, the screens are colorful and if you want to call this modernizing the mainframe, it sure makes it modern over the ISPF. Think about alerts you want to monitor.  Do you want to automate sending email notes for long running queries to the application owners?  With CAE installed, you’ll now be able to see all the Db2 subsystems IDs that plan “ABC12345” ran in your data sharing group.  Life is good.

Do you have an idea?  Below is a link to take you to IBM’s aha web page to submit ideas for Db2 Query Monitor. 

https://ibm-data-and-ai.ideas.ibm.com/?project=DB2ZQUEMON