Db2 Tools for z/OS

 View Only

Db2 Query Monitor/SQL PA @ TechXchange 2023

By Tom Glaser posted Thu August 10, 2023 09:12 AM


Title:  Db2 Query Monitor/SQL PA @ TechXchange 2023

If you were asked the following SQL performance questions, could you answer yes to all of them?

·       What is the most CPU intensive SQL statement from a Db2 subsystem last week, would you be able to quickly produce it?

·       What Db2 package consumed the most I/O yesterday, would you be able find it?  And show this to me a graph format, for management, not just in a data grid format. 

·       Are you alerted to a resource unavailable condition (-904), and for specific reason codes, and against a Db2 catalog tablespace? 

·       Do you need the SQL statement that failed because of a deadlock, and you want this query to be emailed to Db2 Support, as well as the application?

·       If your most important Db2 package failed with a -805, are you paged indicating the package is unavailable?  Or, do you wait for someone to call Db2 Support or start a production control outage conference call?  Don’t you want to be the first to know?

·       Are you using the IBM Db2 Query Monitor product, but only the TSO interface and would like to see what the Web Client piece has to offer?

I know I’m asking a lot.  If you can answer yes to these questions, you must be using the IBM Db2 Query Monitor tool.  If not, are you interested?  There are certain skills a good Db2 DBA must have, especially when it comes to SQL.  When there’s a production issue, you will be asked:

1.     What is the problem?

2.     What caused the problem?

3.     How long will it take to resolve the problem?

I asked the question above, “What Db2 package consumed the most I/O yesterday, would you be able find it?  And show this to me a graph for management, not just in a data grid format.”  Let’s answer this…here’s a screen shot from the IBM Db2 Query Monitor TSO interface:

Using the Web Client interface, the above data will look like this? 

This tells you quickly, what package had the most getpage activity, in a visual format.

I’ve spent most of my life supporting Db2 as a DBA.  If you want to monitor SQL, come to one or both my sessions at TechXchange and let me show what this tool can do and how you can answer the 3 production outage questions above.  In session 2785, I’ll show you how to find the SQL statement, and using IBM’s Db2 SQL Performance Analyzer, we will look at how easy it is to play “what-if” scenarios.

Code: 2785
Title: Nip it in the bud using AIOps
Session Type: User Showcase Session
Speaker: Tom Glaser

Abstract:  This presentation focuses on finding the long running plans, packages, queries and sorting through this accounting data to determine what queries need to be tuned and finding the delay, to help answer the questions 'The program is running longer than expected. Why? Is it the access path selected by the Db2 optimizer? Which exact query is causing the issue?’  I will show you how to compare the statistics from one time-period to another, and across subsystems. Using IBM Db2 Query Monitor and Db2 SQL Performance Analyzer, let’s review the methods of finding that query consuming too much CPU, that query doing a lot of I/O and how to get alerted when these problems do occur. Let’s review the key steps in handling our own application performance assessment.  AI is now built into Query Monitor, check it out.

Code: 3038
Title: Top 10 reasons why you need to install the Web Client for Db2 Query Monitor
Session Type: Tech Byte
Speaker: Tom Glaser

Abstract: In these 15 minutes, I’ll show you the advantages of installing the CAE (Web Client) interface to IBM Db2 Query Monitor. The Web Client will allow you to compare workload and queries, even across subsystems. Create graphs for your manager, export performance data, automatically cancel threads, and set up an alert so that you’ll get an email message with accounting data, the SQL Statement and host variables. Don’t you want to be notified when a -904 occurs? And to me, the biggest reason, I can increase the font size….:).


Tom Glaser