Db2 Query Monitor Batch Reporting 

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: Db2 Query Monitor provides two interfaces; ISPF/TSO and a Web Client (Consolidation and Analysis Engine) using a web browser.  However, there may be times you want to access the Db2 Query Monitor data via a batch job.  Db2 Query Monitor does not come with a true batch interface to its data.  Historical data is off-loaded and stored in a performance database; see below.  This blog shows the various options you have in running batch reports against Db2 Query Monitor’s historical database.

I will cover 3 options to running batch reports for Db2 Query Monitor:

1.     QMF

2.     COBOL

3.     SQL

Db2 Query Monitor delivers 7 “canned” reports in the following four libraries when Db2 Query Monitor is installed:

• QMF Queries – Delivered in hlq.SCQMQRY dataset

• QMF Forms – Delivered in hlq.SCQMFORM dataset

• COBOL Source Program generated by QMF/HPO – Delivered in hlq.SCQMSAMP

• DBRMLIB member– Delivered in hlq.SCQMDBRM (for binding the COBOL programs)

QMF Queries:

•    CQMDSC - Dynamic SQL CPU

•    CQMDSE - Dynamic SQL Elapsed

•    CQMDSG - Dynamic SQL Getpages

•    CQMSSC - Static SQL CPU

•    CQMSSE - Static SQL Elapsed

•    CQMSSG - Static SQL Getpages

•    CQMSSO - Summary of Objects

QMF Forms:

•    CQMDSCF - Dynamic SQL CPU

•    CQMDSEF - Dynamic SQL Elapsed

•    CQMDSGF - Dynamic SQL Getpages

•    CQMSSCF - Static SQL CPU

•    CQMSSEF - Static SQL Elapsed

•    CQMSSGF - Static SQL Getpages

•    CQMSSOF - Summary of Objects

COBOL Programs:

·       CQM#RDSC - COBOL program for Dynamic SQL CPU

·       CQM#RDSE - COBOL program for Dynamic SQL Elapsed            

·       CQM#RDSG - COBOL program for Dynamic SQL Getpages         

·       CQM#RSSC - COBOL program for Static SQL CPU           

·       CQM#RSSE - COBOL program for Static SQL Elapsed      

·       CQM#RSSG - COBOL program for Static SQL Getpages 

·       CQM#RSSO - COBOL program Summary of Objects 

QMF:

Db2 Query Monitor's batch reports are derived from Queries and Forms developed in QMF.  These batch reports are delivered in a manner that allows you to use them as a base for your own customized batch reports.  QMF Queries and Forms can be run online or in a batch job.  For online, this can be under ISPF/TSO, QMF for Workstation or QMF for WebSphere.  The intent of this blog is to discuss batch reporting.  Running QMF in batch requires running a QMF proc.  The QMF Proc points to the QMF Query and uses the QMF Form to format the output report.  My recommendation is to import the QMF Queries and Forms into QMF.  Once done, create a QMF Proc to run the query.  Once the QMF Proc is created, you can run this in batch.  Here’s an example of importing the SQL statement into QMF.

QMF Query:

Import Query

Save Query

Note the two parameters &START_TIMESTAMP and &END_TIMESTAMP.  These names can be anything, as long as you refer to them with same name in the QMF Proc and JCL.

QMF Forms:

With the query imported, let’s continue with Forms.  In this example, you can see the dataset with the 7 forms:

Here’s the QMF command to import the Form:

Import Form

Once the QMF Form has been imported, the form will be displayed:

Save Form

QMF Procs:

Once the SQL statement and Form have been imported and saved, you can write a QMF proc to execute the query.  Here is our example of a QMF proc:

Above, we are running a QMF query called CQMDSC_WITHOHVP and passing 2 parameters, a starting timestamp and an ending timestamp.  If running this proc online, QMF will ask for both parameters.  In batch, we will provide the parameters like the following JCL example:

Sysout example

COBOL:

IBM ships the load modules and the DBRMs with Db2 Query Monitor.  They also ship the source code in case you want to modify them.  The bind job is generated by TCz during the installation, as well as the library for running the batch reports.

COBOL Programs:

·       CQM#RDSC - COBOL program for Dynamic SQL CPU

·       CQM#RDSE - COBOL program for Dynamic SQL Elapsed            

·       CQM#RDSG - COBOL program for Dynamic SQL Getpages         

·       CQM#RSSC - COBOL program for Static SQL CPU           

·       CQM#RSSE - COBOL program for Static SQL Elapsed      

·       CQM#RSSG - COBOL program for Static SQL Getpages 

·       CQM#RSSO - COBOL program Summary of Objects 

You must run job hlq.SCQMSAMP(CQM@NRPB) to bind the COBOL programs.  The DBRMs for the programs are stored in hlq.SCQMDBRM.   Once bound and authorities have been granted, run PDS member hlq.SCQMSAMP(CQM@NRPT) to execute the batch report.  To run the batch reports, un-comment the appropriate EXEC statement corresponding to the desired report.  

Notice the two parameters being passed to the QMF Proc?  Here’s an example of the output:

Batch report parameters

The sample batch report job CQM@NRPT uses the following parameters.

DB2_SSID

   (Required) Use the DB2_SSID parameter in the sample batch report job CQM@NRPT.

Description

   Specifies the name of the Db2 subsystem (ssid) on which the Db2 Query Monitor tables reside.

Syntax

   DB2_SSID(ssid)

Default

   None

Example

   DB2_SSID(DB01)

REPORT

   (Required) Use the REPORT parameter in the sample batch report job CQM@NRPT.

Description

   Specifies the reports (report_name) that are to be generated. The reports are printed to dynamically

   allocated JES SYSOUT files.

Syntax

   REPORT(report_name, report_name, ...)

Valid values

   • STATIC_CPU

   • STATIC_ELAPSED

   • STATIC_GETPAGE

   • DYNAMIC_CPU

   • DYNAMIC_ELAPSED

   • DYNAMIC_GETPAGE

   At least one report name is required and the REPORT keyword can be repeated.

Default

   None

Example

   REPORT(STATIC_CPU, STATIC_GETPAGE)

START_TIMESTAMP

   (Required) Use the START_TIMESTAMP parameter in the sample batch report job CQM@NRPT.

Description

   Specifies the start timestamp (timestamp). Data from intervals that start between the

   START_TIMESTAMP and END_TIMESTAMP will be included in batch reports.

Syntax

   START_TIMESTAMP(timestamp)

Valid values

   The timestamp must be a valid Db2 timestamp value (yyyy-mm-dd-hh.mm.ss.nnnnnn). Please refer to

   Db2 documentation for the allowable forms for a Db2 timestamp.

Default

   None

Example

   START_TIMESTAMP(2021-01-01-01.01.01.000001)

END_TIMESTAMP

   (Required) Use the END_TIMESTAMP parameter in the sample batch report job CQM@NRPT.

Description

   Specifies the end timestamp (timestamp). Data from intervals that start between the

   START_TIMESTAMP and END_TIMESTAMP will be included in batch reports.

Syntax

   END_TIMESTAMP(timestamp)

Valid values

   The timestamp must be a valid Db2 timestamp value (yyyy-mm-dd-hh.mm.ss.nnnnnn). Please refer to

   Db2 documentation for the allowable forms for a Db2 timestamp.

Default

   None

Example

   END_TIMESTAMP(2021-12-31-01.01.01.000001)

SQL:

You can query the Db2 Query Monitor performance database like any Db2 table.  DSNTEP2/4 would be the most common programs running as a batch job.  The SQL statements are found in the hlq.SCQMQRY dataset.  Use these queries as a starting point to build the query you are interested in.  The following query will produce Dynamic SQL CPU statistics.  This example is shown in PDS member hlq.SCQMQRY(CQMDSC):

&V1_START_TS: Starting timestamp

&V2_END_TS: Ending timestamp

Note: If you run this query online in QMF, you will be prompted to enter these 2 values.

Here’s the above query formatted a little bit better, using QMF for Workstation:

To run the above query in batch, here’s an example of using Db2’s DSNTEP2:

Note: hlq.SCQMSAMP(CQMQRY01)…combines all the queries into one PDS member.

Historical Performance Database

The performance database is a set of tables that can be queried just like any Db2 table.  Standard SQL can be run by SPUFI, DSNTEP2/4 or another tool such as IBM’s Query Management Facility (QMF).  Data set hlq.SCQMSAMP library contains a PDS member CQMCRDB, to create the Performance History Database database.  The QMF Queries and Forms are built around these tables.  When you run a batch job pointing to a QMF Proc(which you manually create), it will run the QMF Query, produce a result set and format the output based on the QMF Form.  Here’s a listing of the default table names for the Db2 Query Monitor performance database:

As an added bonus to this blog on batch reporting, here’s an example of running the QMF Queries and Forms from an online option, such as QMF for Workstation:

QMF for Workstation

The following is an example of running these queries and forms using QMF for Workstation

OUTPUT:

In Summary: While Db2 Query Monitor does not have a true batch interface, you can still access the data in the performance database using any option that executes SQL statements against the Db2 tables.  In this report, we have shown you 3 options of querying the Db2 Query Monitor performance database in batch.   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.