Db2 Query Monitor: Integration with the Db2 Admin;  Plus new formatting of SQL

By Tom Glaser

Date: Nov. 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 statement text.
• 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 -SQL Codes 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: Have you ever wanted to jump directly into the IBM Db2 Admin Tool from Db2 Query Monitor? Now you can. As of Oct. 30th 2024, you will be able to download and install APARs PH62040/PH62041 which will provide connectivity to the IBM Db2 Admin Tool (Admin Tool), directly from Db2 Query Monitor.  You must be at V13 of the Admin Tool.  This blog will highlight the integration between these two products.  You will start to see more integration between the IBM Db2 products.  As of Oct. 4^th, you can now jump from Db2 SQL Performance Analyzer into the Admin Tool.  More to come…  

IBM Announcement:

https://www.ibm.com/docs/en/dqmfz/3.4.0?topic=wnidqm-new-changed-functions-in-db2-query-monitor-340-since-ga

Installation/Update of Db2 Query Monitor Code for V3.4:

Following are the four APARs that are required for upgrade Db2 Query Monitor and the Admin Tool

PH62040/PH62041 – Admin Tool

PH63437/PH63438 – Query Monitor

Db2 Query Monitor V3.3 will not support calling the Admin Tool.

Key Functions:

Access to the Admin Tool will come in two phases.  This is phase 1 and it provides access to the Admin Tool under Summaries (option 1 under TSO).  Access to Current Activity (option 2 under TSO) and Exceptions (option 5 under TSO) will come in phase 2, at a later date.  

Db2 Query Monitor is accessible by two interfaces, ISPF (TSO) and CAE (Web Client).  This update only applies to the ISPF interface.

The first time you access Db2 Query Monitor, two pieces of information are needed to access the Admin Tool.  Select option "0" in Db2 Query Monitor (Settings): 

It invokes Db2 Admin panel with information about collection and package:

Command ADMIN_TOOL in the following panels will invoke Db2 System Catalog:

-        Process application DBRM

-        Process SQL from a sequential data set or PDS member       

-        Process a QMF statement                                    

-        Process stabilized dynamic queries

Another option to access the Admin Tool is to simply type ADMIN on the command line:

Need the DDL for a table from the Query Block Report?

Add Return Code for Package Compare:

New option was added to allow to set return code for Batch job to 5 if the access path was changed. The default is "N" (No). This option is available for functions Compare(1.7) and Test (1.8)

In Summary: As you can see, being able to compare before and after binding a package can be huge.  Not only to tell you if the new changes are better for performance reasons, but to know what the new changes will be like in production.  If you have ideas on ways to make SQL Performance Analyzer better, you can submit an Aha! 

I will be interested to see the "New Formatting of SQL" mentioned in the title.  At our site we use jump from CQM (Query Monitor) into Performance Analyser to grab SQL into an ISPF edit window. In earlier versions the formatting of the SQL was pretty crude, doing line break only on certain common clauses like SELECT, FROM, WHERE, ORDER BY maybe I forget exact list but many important clauses ignored, however complex SQLs were not readable really appearing  in 80 char width mainly text wrap format.  I wrote my own REXX EDIT Macro to format the SQL nicely (well at least very readable), and we have another EDIT Macro to do a compact Explain with extended important details, in our own In House Format (very compact yet having detail).  PLAN_TABLE alone is not great for understanding access paths. We add critical details like: Index Key columns, Operator of last Matching column predicate, plan step cost estimates  from DSN_DETCOST_TABLE, and a few critical Catalog stats. Indicators of the presence of high filtering stage 2 predicates. It is important to me to see the important access path details in one place without lots of scrolling, and without too many drill down steps.   

Clearly it is difficult to make all customers happy with Explain formats, either compact or verbose, and which explain fields are critical?  Even SQL formatting has personal taste, like indentation, alignment, and whether commas appear on a line before a list item or appear after a list item.  Overriding goal though is to make the SQL very readable, so we can make trial modifications before re-explain to see if access path is fixed. The other goal is the SQL formatting and the Explain  fast response.

Db2 Query Monitor: Integration with the Db2 Admin;  Plus new formatting of SQL

By Tom Glaser

Date: Nov. 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 statement text.
• 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 -SQL Codes 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: Have you ever wanted to jump directly into the IBM Db2 Admin Tool from Db2 Query Monitor? Now you can. As of Oct. 30th 2024, you will be able to download and install APARs PH62040/PH62041 which will provide connectivity to the IBM Db2 Admin Tool (Admin Tool), directly from Db2 Query Monitor.  You must be at V13 of the Admin Tool.  This blog will highlight the integration between these two products.  You will start to see more integration between the IBM Db2 products.  As of Oct. 4^th, you can now jump from Db2 SQL Performance Analyzer into the Admin Tool.  More to come…  

IBM Announcement:

https://www.ibm.com/docs/en/dqmfz/3.4.0?topic=wnidqm-new-changed-functions-in-db2-query-monitor-340-since-ga

Installation/Update of Db2 Query Monitor Code for V3.4:

Following are the four APARs that are required for upgrade Db2 Query Monitor and the Admin Tool

PH62040/PH62041 – Admin Tool

PH63437/PH63438 – Query Monitor

Db2 Query Monitor V3.3 will not support calling the Admin Tool.

Key Functions:

Access to the Admin Tool will come in two phases.  This is phase 1 and it provides access to the Admin Tool under Summaries (option 1 under TSO).  Access to Current Activity (option 2 under TSO) and Exceptions (option 5 under TSO) will come in phase 2, at a later date.  

Db2 Query Monitor is accessible by two interfaces, ISPF (TSO) and CAE (Web Client).  This update only applies to the ISPF interface.

The first time you access Db2 Query Monitor, two pieces of information are needed to access the Admin Tool.  Select option "0" in Db2 Query Monitor (Settings): 

It invokes Db2 Admin panel with information about collection and package:

Command ADMIN_TOOL in the following panels will invoke Db2 System Catalog:

-        Process application DBRM

-        Process SQL from a sequential data set or PDS member       

-        Process a QMF statement                                    

-        Process stabilized dynamic queries

Another option to access the Admin Tool is to simply type ADMIN on the command line:

Need the DDL for a table from the Query Block Report?

Add Return Code for Package Compare:

New option was added to allow to set return code for Batch job to 5 if the access path was changed. The default is "N" (No). This option is available for functions Compare(1.7) and Test (1.8)

In Summary: As you can see, being able to compare before and after binding a package can be huge.  Not only to tell you if the new changes are better for performance reasons, but to know what the new changes will be like in production.  If you have ideas on ways to make SQL Performance Analyzer better, you can submit an Aha! 

Hi Mike,

Query Monitor is using a stored procedure from the Admin Tool to format the SQL statement.  Here is an example of a query that's not formatted and the same query formatted.

...and formatted:

I hope this helps.

Thanks, Tom

I will be interested to see the "New Formatting of SQL" mentioned in the title.  At our site we use jump from CQM (Query Monitor) into Performance Analyser to grab SQL into an ISPF edit window. In earlier versions the formatting of the SQL was pretty crude, doing line break only on certain common clauses like SELECT, FROM, WHERE, ORDER BY maybe I forget exact list but many important clauses ignored, however complex SQLs were not readable really appearing  in 80 char width mainly text wrap format.  I wrote my own REXX EDIT Macro to format the SQL nicely (well at least very readable), and we have another EDIT Macro to do a compact Explain with extended important details, in our own In House Format (very compact yet having detail).  PLAN_TABLE alone is not great for understanding access paths. We add critical details like: Index Key columns, Operator of last Matching column predicate, plan step cost estimates  from DSN_DETCOST_TABLE, and a few critical Catalog stats. Indicators of the presence of high filtering stage 2 predicates. It is important to me to see the important access path details in one place without lots of scrolling, and without too many drill down steps.   

Clearly it is difficult to make all customers happy with Explain formats, either compact or verbose, and which explain fields are critical?  Even SQL formatting has personal taste, like indentation, alignment, and whether commas appear on a line before a list item or appear after a list item.  Overriding goal though is to make the SQL very readable, so we can make trial modifications before re-explain to see if access path is fixed. The other goal is the SQL formatting and the Explain  fast response.

Db2 Query Monitor: Integration with the Db2 Admin;  Plus new formatting of SQL

By Tom Glaser

Date: Nov. 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 statement text.
• 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 -SQL Codes 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: Have you ever wanted to jump directly into the IBM Db2 Admin Tool from Db2 Query Monitor? Now you can. As of Oct. 30th 2024, you will be able to download and install APARs PH62040/PH62041 which will provide connectivity to the IBM Db2 Admin Tool (Admin Tool), directly from Db2 Query Monitor.  You must be at V13 of the Admin Tool.  This blog will highlight the integration between these two products.  You will start to see more integration between the IBM Db2 products.  As of Oct. 4^th, you can now jump from Db2 SQL Performance Analyzer into the Admin Tool.  More to come…  

IBM Announcement:

https://www.ibm.com/docs/en/dqmfz/3.4.0?topic=wnidqm-new-changed-functions-in-db2-query-monitor-340-since-ga

Installation/Update of Db2 Query Monitor Code for V3.4:

Following are the four APARs that are required for upgrade Db2 Query Monitor and the Admin Tool

PH62040/PH62041 – Admin Tool

PH63437/PH63438 – Query Monitor

Db2 Query Monitor V3.3 will not support calling the Admin Tool.

Key Functions:

Access to the Admin Tool will come in two phases.  This is phase 1 and it provides access to the Admin Tool under Summaries (option 1 under TSO).  Access to Current Activity (option 2 under TSO) and Exceptions (option 5 under TSO) will come in phase 2, at a later date.  

Db2 Query Monitor is accessible by two interfaces, ISPF (TSO) and CAE (Web Client).  This update only applies to the ISPF interface.

The first time you access Db2 Query Monitor, two pieces of information are needed to access the Admin Tool.  Select option "0" in Db2 Query Monitor (Settings): 

It invokes Db2 Admin panel with information about collection and package:

Command ADMIN_TOOL in the following panels will invoke Db2 System Catalog:

-        Process application DBRM

-        Process SQL from a sequential data set or PDS member       

-        Process a QMF statement                                    

-        Process stabilized dynamic queries

Another option to access the Admin Tool is to simply type ADMIN on the command line:

Need the DDL for a table from the Query Block Report?

Add Return Code for Package Compare:

New option was added to allow to set return code for Batch job to 5 if the access path was changed. The default is "N" (No). This option is available for functions Compare(1.7) and Test (1.8)

In Summary: As you can see, being able to compare before and after binding a package can be huge.  Not only to tell you if the new changes are better for performance reasons, but to know what the new changes will be like in production.  If you have ideas on ways to make SQL Performance Analyzer better, you can submit an Aha!