I thought I’d share an overview of the real-time monitoring options for Db2 (LUW). This article focuses on comparing the options, with links to deeper information on each tool.
Types of Monitoring
First a quick overview of the types of monitoring that every DBA should be doing. The way I look at it, there are three different, though sometimes overlapping, types of monitoring for Db2:
- Historical Performance Monitoring
- Real-Time Monitoring
- Monitoring to Generate Alerts
Historical Performance Monitoring
Historical performance monitoring gathers data on metrics over time. These metrics are stored in a format that can be easily queried or explored. The main questions we’re trying to answer with them are things like:
- Why was the database slow at 2PM on Thursday?
- What trends do we see in database performance?
- What red flags do we see indicating potential performance problem areas?
- How do things now look different from our normal baseline?
These are important questions for any DBA to be able to answer. The tools in this space are generally not included with Db2. IBM provides a free option in the Database Management Console. Vendor solutions include ITGain’s SpeedGain, DBI’s Brother-Panther, and Quest’s Foglight.
Problem-Detection Monitoring and Alerting
Alerting is critical to keeping a database up and running and resolving problems before they become outages. There is just one main question we’re trying to answer here:
- What condition exists that risks imminent outage?
This monitoring area is key to proactively catching problems before they become outages, and to responding quickly when a serious problem does occur.
Real-Time Monitoring
Real-Time monitoring is focused on helping us understand what is happening right now within the database. The questions we’re trying to answer here are things like:
- What is happening right now?
- Why is the database slow right now?
- What is causing this effect that someone is seeing?
Being able to answer these questions is critical to troubleshooting and being able to immediately resolve issues. Most of the tools in this space are part of Db2. This is the area I’ll focus on in this article.
Real-Time Monitoring Options
There are a wide array of options in the real-time monitoring space, and different options are better for different purposeses. I’ve created this summary chart of the most popular options and where they shine:
For example, if I’m looking for a monitoring method where I can use SQL to get data to pass on to another tool, I would look at the column titled “Use SQL”, and it would tell me that the MON_GET* Functions or the SYSIBMADM.SNAP* views are my best choices. If I were instead looking for a method where I’m staring at the screen watching a database, I’d look at the “Update Info” column, which would tell me that db2top, dmctop, and dsmtop will all give me on-screen updates, while I could use the -repeat option with db2pd to get regular updates.
Dimensions
For each column in the table above, I’m addressing a particular feature that may cause you to choose one tool over another.
Update Info
Some tools print out a one-time report, while others offer options for the data on the screen to update in real-time. db2pd offers a -repeat X option that can be used to re-run the same command every X seconds, and display the new run on the screen, though the update is displayed sequentially rather than dynamically.
Use SQL
Depending on the use case, it can be very useful to access data using SQL. If one is writing a script, or a dashboard, or a Jupyter Notebook, the predictable output and easy manipulation of SQL is a significant advantage. I learned early in my career not to base scripts off of db2pd because the output is likely to change with only a fixpack update, breaking any scripting based on it.
Lightweight In-Memory Metrics
There are two major underlying interfaces that all of these methods depend on. Either the snapshot interface or the lightweight in-memory metrics. The snapshot interface has a greater impact on the database being monitored, while the in-memory metrics are the strategic direction for IBM moving forward, and are less impactful.
Remote Access
Some tools like db2pd can only be used on the server where the database is running. Frequently db2pd may only work as the instance owner, even. Other tools can be run on a central server or on your laptop and connect to the database remotely. This choice matters if you’re running scripts, depending on where you’re running them from and also depending on your access choices.
Reset Metrics
Both monitoring interfaces report numbers since the last time the instance or database was restarted by default. The snapshot interface has a reset option to allow you to see counters only over a specific time period. Some tools on top of the lightweight in-memory monitoring emulate this functionality.
Requires Connections
If a tool runs locally on a server, it may or may not require a database connection. For example, db2pd does not require a database connection, which could be an advantage if you are unable to connect due to some problem or due to workload. With the exception of db2pd, generally the snapshot interfaces do not require a connection while the lightweight in-memory monitoring interface does. db2top does not require a connection, while dmctop does.
Deprecation
In the Db2 world, “Deprecated” means that a feature is no longer improved in any way, and may be completely removed (“Discontinued”) in a future release. The snapshot interface has been Deprecated. New solutions should generally not be built on deprecated functionality, as you will quickly regret not having new functionality in the area of monitoring.
Tools
Below are links on each of the tools mentioned for more information.
db2top
IBM Db2 KC page on db2top
DB2 Basics: db2top
dsmtop
IBM Db2 KC page on dsmtop
Comparing dsmtop and db2top
Why dsmtop isn’t Even Half as Good as db2top
dmctop
IBM DSM KC page on dmctop
First Look – The New Replacement for db2top and dsmtop – dmctop!
db2pd
MON_GET* Functions
IBM Db2 KC page on Monitoring Procedures and Functions
Using DB2 LUW’s Monitoring Table Functions
My New Best Friend – mon_ Part 1: Table Functions
My New Best Friend – mon_ Part 2: Views
DB2 Administrative SQL Cookbook: Generating a Comma Separated List of Columns in a MON_GET Table Function
MONREPORT.DBSUMMARY
IBM Db2 KC page on MONREPORT.DBSUMMARY
DB2 Basics: MONREPORT
GET SNAPSHOT and the SYSIBMADM.SNAP* Views
IBM Db2 KC page on the Snapshot Monitor
Two Worlds of DB2 LUW Performance Monitoring
Man, I’ll tell ya – it’s like you’re sitting in the same room, listening to me voice how I have so much to learn as I begin my eleventh month as a LUW DBA. This is yet another stellar post. Yours are the first ones I look for as I encounter issues and add to my growing list of opportunities to learn. Many thanks for your sharing, and your excellent manner of communicating what you know.
Thank you! I love to hear that people get something out of the Blog. This one came from a presentation, and I realized that the chart would make for a great blog entry.
Hello! what about db2mon tool?
I’m not about Dmitri Liakh version, I’m talking about IBM version. Yes, it has short functional, but it has)
Hi Ember, thank you for another excellent post.
Note that we (Db2 development) recently published a set of scripts which collect, archive, and automatically purge historical monitoring information. As well as a script to generate some basic MON_REPORT style reports from the historical data.
The intention is for the scripts to remain open-sourced and open for community contribution.
We call this framework ‘db2histmon’ (Db2 historical monitoring) scripts.
We’d love to have community feedback.
The scripts can be downloaded from the github repro: https://github.com/IBM/db2histmon
I have a short blog introducing the collection/archival/purging framework script here: https://thinkingdb2.blogspot.com/2020/06/enabling-db2-historic-monitoring.html
And another short blog introducing the MON_REPORT style report generating script here: https://thinkingdb2.blogspot.com/2020/11/generating-reports-from-db2-historical.html
Thanks
Hello,
We are working with db2 v10.5 (upgrading to v11.5) and we have used the InfoSphere Optim Performance Manager. It is a pitty that this tool doesn’t work anymore since the end of flash support in our browsers.
OPM was a horrible load on the servers being monitored. You can get similar functionality out of DSM or even better the most recent DMC, which is free. Make sure you stop paying IBM for OPM if you’re paying for it separately.