Db2 Query Monitor: Host Variables
By Tom Glaser
Date: July 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 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: One of the many functions of Db2 Query Monitor is to capture host variables. I don’t know how many times I’ve heard someone say they know their Cobol program is passing the correct value to the SQL statement. When they run their program, no rows are returned….and it becomes Db2’s fault for not retrieving the correct rows. This is where Db2 Query Monitor shines. It can display the values being passed to the SQL statement so that you can accurately know what is running in Db2; and it’s in hex, so you can see non-printable characters.
If no input host variables are present for the call or the monitoring profile stated not to collect them, one the following messages is displayed:
• CQM104I No host variables. Db2 Query Monitor found no input host variables present for this call or the monitoring profile stated not to collect them.
• CQM103I Host variables not gathered. Db2 Query Monitor does not collect host variables for this call type.
CQM Parms:
Db2 Query Monitor has a large number of installation parameters. The following two are related to host variables:
ALERT_HOSTV_PER_ALERT
Use the ALERT_HOSTV_PER_ALERT parameter in CQMPARMS to control the amount of storage used for alerts.
Description
The maximum number of host variables per alert (nnnnn) that are collected. This parameter enables you to control the amount of storage that is used when collecting alerts.
Syntax
ALERT_HOSTV_PER_ALERT(nnnnn)
Default
16
Note: Under normal circumstances, the default value is appropriate. However, if your workloads average more than 16 host variables per alert, this parameter can be adjusted as needed.
HOSTVAR_LIMIT
Use the HOSTVAR_LIMIT parameter in CQMPARMS to control the amount of storage allocated for host variables.
Description
The maximum amount of storage (n) to be allocated for host variable collection per event.
Note:
• If you are using Security Guardium S-TAP for Db2 on z/OS and Db2 Query Monitor to simultaneously monitor the same Db2 subsystem, all products must have matching HOSTVAR_LIMIT settings to avoid receiving a mismatch error.
• If error message CQM1203I is encountered, with RC=0008, RSN=003F, increase the HOSTVAR_LIMIT setting to accommodate the collection of host variables for the monitored workload.
Syntax
HOSTVAR_LIMIT(n)
Default
1500
Range
Integers in the range 1 - 99999
Example
HOSTVAR_LIMIT(1000)
Performance History Files:
Db2 Query Monitor uses a set of VSAM data sets, collectively referred to as Performance History Files, to store information about the Db2 subsystems it monitors. Here are two VSAM files that store host variable information:
EXCPDATA -
Stores information related to exception SQL calls, text, SQLCA, and host variables. You can modify the parameters that control the EXCPDATA Performance History File by tailoring the EXCPDATA_* parameters in CQMPARMS
EXCPHSTV -
Stores information about exception host variable information collected during the course of an interval. You can modify the parameters that control the EXCPHSTV Performance History File by tailoring the EXCPHSTV_* parameters in CQMPARMS.
Security:
If you want to secure data in current activity, summaries, exceptions, or SQLCODES, you must configure a RACF facility class profile for the appropriate function (for example, a RACF facility class profile might be CQM.HOSTV.qmid which protects host variable information in current activity).
For host variable viewing and collection, Db2 Query Monitor checks a user's access when it sees a new workload. Db2 Query Monitor administrators should be careful when granting users who are granted READ access to CQM.EO.HOSTV.qmid and CQM.HOSTV.qmid and should consider the security of the tables that will hold host variable information. To view host variables in the Activity Browser, the user must have READ access to CQM.HOSTV.qmid. With auditing requirements around Db2, you want to protect who has access to host variables. For example, you could be displaying someone’s social security number or credit card information.
Profile Setup:
The option to monitor host variables must be turned on in the Db2 Query Monitor subsystem “profile.”
Here’s an example:
In the example above, “Gather Host Variables” is turned to Y. If Y, the unit of SQL activity that is collected will be turned on. Notice only packages that start with ADB will be monitored with this workload (profile line). Other packages could be monitored with different workloads.
To capture host variables, you need to define an exception. Exceptions are SQL events that a database administrator might be requested to research. These thresholds are defined in the monitoring profile. For example, if you want to know about queries that run longer than 1 second, this will become an exception….and this information will be displayed in the exception panels.
Displayed Host Variables:
Host variables can be displayed either by the ISPF interface and the Web Client (CAE=Consolidated Analysis Engine). We’ll look at both. You can view host variables under current activity and exceptions.
Let’s run a couple of tests. I’m going to call a stored procedure using the Db2 Admin Tool, I’ll pass it parameters and then we’ll take a look at them in Query Monitor.
Admin Tool: I’m going to call a Db2 Stored Procedure that allows me to execute a Db2 command:
We are going to enter the Db2 Command -DIS GROUP with a length of 20 characters:
Here’s our result:
Let’s look at Db2 Query Monitor and select option 5 for “Exceptions.” Enter the letter H
Notice the -DIS GROUP command and the length of 20…as well as a display in hex?
Web Client:
In this example, we set Perspective to Exceptions. When you select the command for a specific row, you have 3 options, but one of them is not H for Host Variables.
If you have many host variables, say 100 or more, you will need to manually line up the host variables to the SQL statement. Here’s an example:
Here’s another example:
Overhead
There is overhead associated with capturing hosts variables. Capturing host variables can quickly resolve SQL statement issues. You don’t want to turn this on for everything, as Db2 Query Monitor must extract the variable information and write them to VSAM files. Imagine the overhead of your Db2 subsystem executing millions of transactions per hour and capturing all that data. Just turn on what you need.
What Db2 Query Monitor does not capture:
- Db2 Query Monitor does not collect host variables in native stored procedures.
- Host variables cannot be collected on exceptions alone. The problem is that say the host variables are collected on the OPEN, and the threshold is set for total CPU which isn't known till statement closes, by the time the CLOSE call comes around the host variables are long gone and can't be collected.
- Db2 Query Monitor does not collect the values of host variable data for FETCH statements.
- You must set a threshold in order to collect the host variables, such as GETPAGE, CPU, ELAPSED TIME, etc. Monitoring the SQL return code by itself will not collect host variables, unless you set up the profile to enable SQL codes as exception. In this screen shot, I have GENERATE SQLCODE EXCEPTIONS set to Y. In “INCLUDE EXCEPTION SQLCODES,” you can include what SQLCODE you want to monitor. When found, it will include those host variables.
In Summary:
In this blog, I’ve shown you how to view host variable data in either the ISPF interface or the Web Client. 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.