Db2 Query Monitor: Exporting Custom Views (V3.3/V3.4)
By Tom Glaser
Date: January 2024
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 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 -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: Moving columns around in either the ISPF or the CAE (Web Client) interface to IBM’s Db2 Query Monitor is easy. But, moving this setup from one LPAR to another can be tricky. In this blog, I’ll provide details on how to move columns around, size them and sort them. And most importantly, how to move these changes from one LPAR to another without having to manually configure them once again.
There are two interfaces to Db2 Query Monitor, the ISPF/TSO and the CAE (Consolidated Analysis Engine) interface, also known as the Web Client. Going forward in this article, I will refer to the CAE as the Web Client. Db2 Query Monitor comes with a default grouping of columns. This is the default setting when viewing statistics about Db2 subsystems being monitored for ISPF:
I like to view my columns in a specific order; this allows me to quickly find the data I’m looking for. I like to see the total Elapsed Time next to the AvgX Elapsed Time. The Total Getpages next to the AvgX Getpages, and so on. I also like TSO and the Web Client to be set up the same. You will need to set this up manually, but once set up, you shouldn’t need to change it.
Let’s start with TSO:
ISPF/TSO
ISPF has multiple primary line commands to allow you to change the length, the sorting and the order of columns. For a link to a blog on Primary Line commands, click here:
CFIX: Enables you to fix and unfix columns.
CORDER: Enables you to reposition columns.
CREMOVE: Enables you to remove all customizations
CRESET: Enables you to reset all customizations
CSETUP: Enables you to rearrange report columns, change the width of individual columns, and control the vertical ordering of columns. (same as SETUP)
CSIZE: Enables you to change the displayed width of columns
CSORT: Enables you to select one or more columns for sorting and thus modify the order of the rows displayed. (same as SORT)
SETUP: Enables you to rearrange report columns, change the width of individual columns, and control the vertical ordering of columns. (same as CSETUP)
SORT: Enables you to select one or more columns for sorting and thus modify the order of the rows displayed. (same as CSORT)
There are so many options to set this up. If you didn’t want to remember all these short cuts, just remember one, SETUP. You will not be able to access the above line commands until you get into the actual displays of data. If you try from the home panel, you will receive an error message. Once you display a report, type SETUP to modify your columns.
Here’s an example of looking at Db2 subsystem metrics:
This is an example of looking at SQL statements for a package:
For example, here’s how I like the panel to be displayed:
I look at the Exec Count field, not the CALLS. The EXEC COUNT represents the number of times a particular statement has executed. The CALLS column represents the number of SQL calls associated with the DB2 metrics data gathered by Query Monitor. EXEC COUNT is the actual query. CALLS includes such things as FETCH, OPEN CURSOR, CLOSE CURSOR, COMMIT, etc. EXEC COUNT is what I’m interested in. I like to follow that up with the TOTAL ELAPSED and then the AVGX ELAPSED. Not to confuse you, AVGX ELAPSED is different than AVG ELAPSED. AVGX ELAPSED matches the EXEC COUNT field. AVG ELAPSED matches the CALLS field. Again, I’m interested in the EXEC COUNT. After ELAPSED, then CPU Times, GETPAGE activity and then DELAYS.
This is one example of modifying the positions of the columns, sorting them and changing the size of the field.
I recommend using this panel and update the display panel for each object type.
Start by selecting option 2 (Db2 ssids) and set your columns. Drill into another object, such as Plans and update that panel. Next could be objects, and so on. Once completed, you’re all set. The next time you access Db2 Query Monitor, you’ll be able to access the panels in the order that you like.
What happens when you change LPARS? Do you start over? You can. Let’s review another option you have. The changes you made to your screens are saved in a PDS under your userid. While in Query Monitor, type DDLIST and look for a CQM file ending in ISPTLIB. When you first access Db2 QM, a dataset following a name such as userid.CQM*.ISPTLIB is created; this is an ISPF Dialog Table. Members within this dataset store the user’s configurations. Copy this file to the new LPAR (xmit, ftp, ndm, etc). When you login to Db2 Query Monitor, the modifications will be picked up; no need to change the columns around.
Web Client
The Web Client interface allows the user to make changes to size and placement of the columns. This can be temporary or permanent, just like under ISPF. The Query Monitor server stores this information for each user in Unix Systems Services(USS). For Windows, this is a different folder (see below).
Clicking on “VIEWS” allows the user to make changes:
The above details were for USS. While the information for USS and Windows is not documented, I tried the following and it seems to work. Nothing official, just something that worked for me that I’m passing along.
If the CAE server is installed on Unix Systems Services, the user’s custom views are stored here:
/proj/cqmdata/data33/knowledge/userconfigurations/
On Windows, shared custom views are here:
C:\Program Files\IBM\DB2 Query Monitor v3.4\knowledge\site\qm\configurations\customViews
On Windows, user custom views are here:
C:\Program Files\IBM\DB2 Query Monitor v3.4\knowledge\userconfigurations\USERID\qm\customViews\
When you copy the XML files over and restart the CAE Server, it will pick up the views.
As side note: This setup is required only on the CAE Server. If all Db2 subsystems are being monitored by the same server, no further setup is needed. But, if you have a server for test and a server production, then moving the files from one server to another will propagate that change.
In Summary: Having the ability to move columns around and learning what order the columns are key will speed up your ability to troubleshoot a problem quickly. Taking the next step and setting up these changes on other servers will help with the installation and setup of your Web Client interface.
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
#IBMChampion