Customer Questions Answered from April 20th Query Monitor webcast

If you missed the webcast please register at https://bit.ly/QM_Apr20 in order to view the replay.

We did cover a few on the questions live on the webcast, but there were several others in the queue that we did not have time to cover.

Q1: Does Query Monitor’s object collection and web server interface require additional licenses?

A1: No additional licenses required. Object collection can be turned on in the STC parameter file. The CAE components (which includes the web interface) need some additional setup: one CAE agent STC on every LPAR as well as installing the USS-based CAE server.

Q2: Does Query Monitor have a method of selecting a starting date to an end date using INTV? For example, block commands.

A2: No, it does not. Today you must select each interval of interest one-by-one. However, it is an excellent idea.
Subsequently this Aha idea has been opened: https://ibm-data-and-ai.ideas.aha.io/ideas/DB2ZQUEMON-I-13 – please vote for it if you would also like to see this feature in the product!

Q3: The integration with IBM Query Workload Tuner only works if the Db2 archive is on the same subsystem where the data was collected.

A3: Yes, that’s correct. Query Monitor does not have the SQL text for static SQL statements. Instead the SQL text is retrieved from the Db2 catalog (SYSPACKSTMT). IBM Query Workload Tuner does not support reading the SQL text from a remote catalog.
A recent enhancement (APAR PH17912) for the QM offload process allows for filtering by SSID, so you can easily load performance data into the subsystem where it originates.

We suggest opening an Aha idea for Query Workload Tuner: https://ibm-data-and-ai.ideas.aha.io/?project=ZQUERY

Q4: Is it possible to run batch queries against the Query Monitor backstore VSAM datasets?

A4: No, the data must be offloaded to Db2 tables first. One option is to configure your z/OS system automation to trigger the offload as soon as the interval has been written:

CQM2401I INTERVAL PROCESSING ENDED FOR QMR2 INTV#(00002935)

Q5: what is the best configuration if you have multiple Db2s per LPAR

A5: The Query Monitor collector STC can monitor one or more subsystems on the LPAR. You can also run multiple Query Monitor collector STCs on the same LPAR. It is usually easier to install and manage a single STC per LPAR, but we do have customers that run multiple collectors on the same LPAR for various reasons, for example, separating the performance data for a development Db2 from a test Db2.

Q6: what is the overhead of collecting host variables?

A6: We do not have benchmark data for collecting host variables, but the expectation is the overhead is very low.

Q7: Are the alerts executed by the CAE server or the CAE agent? Can you use WTO and JCL alerts when running the CAE server on windows?

A7: Some actions are executed by the CAE agent: WTO, cancel thread, submitting JCL, running MVS operator commands, running shell script, and more. Other commands like email and SNMP are executed by the server. So yes: you can still use WTO alerts if the CAE server is running on Windows.

We generally recommend installing the CAE server on USS. The installation, network and configuration are usually easier on USS where everything is under the system programmer’s control. If you want to install the CAE server on Windows you’d usually have to provision a server image and have someone else install the CAE server. In addition, there might be additional network and firewall configuration to allow network communication between the CAE agent and CAE server.

In addition, it is java based so will run on zIIP processors, so CPU overhead should not be a concern.

Q8: Is it possible to capture SQLTEXT for exceptions even if OPTKEY(TEXT) is turned off.

A8: Yes, the SQL text will be logged for exceptions. In general, all OPTKEYS are logged for exceptions.