I think I’m beginning to better understand why IBM is not fond of tools like db2top. The truth is that the developers really only get exposure to the largest Db2 clients. These are the ones that really pay the developer’s salaries, after all. These clients often have not just one tool for working with databases, but ALL THE TOOLS.
For the rest of us, we have to make do with what we can get for free. There used to be a lot of random choices in this area. db2top itself came out of AlphaWorks, which was essentially stuff that the IBMers who worked in Global Services actually supporting IBM software wrote to make their jobs easier.
When I originally started using dsmtop, I wrote about some of the differences using them side-by-side. However, for the last 8 months or so, I have not been able to use db2top at all on one of my larger clients. The reasons are complicated, but having my favorite tool taken away has been painful. I’ve tried to fill that hole with dsmtop, but it just doesn’t do what I need it to. Here are my main issues with it.
HADR Standby Monitoring
Because dsmtop requires a database connection, it doesn’t work on the standby unless the reads on standby(ROS) feature is enabled. I actually have very few clients who want to pay for the full licensing on the standby that requires, particularly with all the drawbacks of ROS. Now, granted, you don’t always need to watch db2top on the standby, but it can be a nice way of watching while HADR catches up when you’re waiting for that. Instead, I just use:
db2pd -d dbname -hadr -repeat 30
This isn’t really a major one like the next couple.
Waiting for Database Issues
The number one purpose for db2top in my mind is to quickly be able to look at what is happening in the database right now, or at least in the last 5 seconds. This is something I do during critical peak periods, during load tests, and when someone has engaged me to investigate a performance problem. Looking at things at a high level using db2top lets me bounce around and look for different issues with the press of a key or two instead of having to query and use different db2pd commands to find what I want. This is one of the areas where dsmtop really falls down for me. The top issue here is that when watching the database screen, db2top would change the background of the “Lock Wait” section any time there was a lock wait, and this could clue me in to go to the locks screen. I don’t see dsmtop doing the same thing.
Secondly, I tend to like to watch the sessions screen. This gives me a good idea of how busy the database is. db2top would let me hide idle sessions by pressing i. I can’t find this functionality in dsmtop. There are some options to filter on a regex, but it’s a global search across all fields, and isn’t very useful. A search on EXECUTING gets me connections while they are executing, but would fail to show those in lock-wait.
Investigating Locking Issues
One of my favorite areas of db2top is looking into locking issues. Maybe I’m older than dirt, but I remember trying to do this before db2top, and it wasn’t terribly fun. The problem I tend to have in dsmtop, is that the screen gets flooded with locks held by event monitors and such, and the ones that are more relevant are basically impossible to see on an ongoing basis. I struggle to find what to sort on, and the regex thing doesn’t seem to work to exclude things. In db2top, problem locks (lock-waiters and blockers) would move to the top to make it easier for me to see them. I’ve actually started using db2pd and the -lock wait and -wlocks options with a query to MON_GET_APPL_LOCKWAIT handy or only going into dsmtop once I’ve identified with db2pd that there’s an issue. The lock chain option in dsmtop still works.
Understanding the Refresh and Collection Interval Parameters
This is a bit difficult. While db2top had an interval to specify, that one interval was the only time-based parameter. The data on screen in delta mode (without hitting k for cumulative) would generally show you the values for each metric over the last interval seconds.
dsmtop has two – the collection interval and the refresh interval. The collection interval specifies how often to collect baseline data, while the refresh interval specifies how often to refresh the data on the screen. In the deltas view, this means that all metrics are specified in either per minute or per second. You can’t specify which you want. The values are updated every refresh inteval seconds, using all data between the last baseline collection and now. Confused yet? Because I had to look it up, and I do every time I try to understand it.
I prefer the db2top method because it is very clear, and shows me what I really want – a “what’s happening right now” (or in the last 5 seconds) view – not a “what has happened over the last 5-60 seconds” view.
No Reset
Now let’s say I’m tired of the delta view and I want to get a better view of what’s happening over a bit of time. With db2top this was easy because I could go into cumulative mode (by hitting k), reset the counters, and watch things add up. In dsmtop, I can still go into cumulative mode (by hitting alt-k), but the only option here is everything since the database restarted.
This is not all that useful to me. I don’t really need to know how many transactions over the last eight months. I don’t see a reset option available. I can understand that the tables being queried don’t have a reset functionality like the old snapshot interfaces did, but I personally have coded reset emulation in both native SQL stored procedures and functions (similar to this developerworks article) and in python. It isn’t exactly a walk in the park, but if I can do it, surely a real programmer can do it.
Summary
What I really wish is that people at IBM were forced to actually use the tools they provide to administer Db2 databases. It sometimes feels like tools like tools such as dsmtop are developed based on a one-time set of requirements, and not improved over time by those who use them. When I talk to people at IBM who support Db2 on cloud or who help IBM’s clients with issues, I get the impression they use custom options on db2pd or custom scripts to gather information rather than the other tools available. db2top was coded by peon DBAs, and it’s clear that they really used it. A decade or two later and there still isn’t a better tool for the most basic monitoring and investigation needs.
I still use dsmtop, but most of the time, I find myself frustrated with it. It is such a relief when I go to do something on a different client where I can use db2top.
Nice blog to understand the differences between db2top vs dsmtop. Always db2top was best toll for DB2 LUW
Thank you as always for your knowledge sharing.
Very good article. That reminds me a tool called db2mon, that was before db2top but for Windows. It was a nice tool to get what was happening in the database. There is a new tool, called db2Gdba, that looks similar to db2mon, but I haven’t used that much. And I do agree with you about IBMers should be forced to administer a database with their own tools.
How are we even suppose to monitor database backup ( offline backup ) with dsmtop you just cannot connect in jdbc if the db is down.
The fact that dsmtop doesn’t support connecting to a standby is a bit of a show stopper for me. I am back to using HADR a lot, and don’t want to muck around with d2pd, though it has its uses. I also found dsmtop a pain to set up. I don’t know about version 11.1 and later, but it was difficult to use in version 10.5, plus needed to run under X11. I thought it was a text-based tool.