First Look – The New Replacement for db2top and dsmtop – dmctop!

One topic that comes up at most panels, Technical Advisory Board meetings and other longer interactions I participate with for IBM is DBA’s love of db2top. It is such an easy tool, and one that most DBAs I know choose over all others for real-time monitoring and basic investigation of a database. We all have our scripts, SQL, vendor tools, and other methods for deeper analysis, but when it comes to quick and useful, db2top is just great.

db2top, however, has not been updated for over a decade. It is also not available on Windows. If I recall correctly, it came out of “alphaWorks” which was often (but not always) useful tools that the hard working peons in IBM Global Services wrote to do their jobs better – not out of the IBM Db2 Lab itself.

IBM tried to replace db2top several years ago with dsmtop. I wrote fairly extensively about some differences between the two in “Comparing dsmtop and db2top” and also in “Why dsmtop isn’t Even Half as Good as db2top“. Last year at a couple of meetings in the fall, IBMers were asking questions about why we didn’t feel dsmtop was good enough, and it was kind of fun to just say “read my blog!”, since I had recently written about it in detail.

Enter dmctop

I have a decent ear to the ground with IBM. I hear about a lot of things before they happen. But there are always a few things that surprise me. I usually chalk it up to my own memory being faulty, and the deluge of information, but I was totally suprised when someone tweeted about dmctop recently. dmctop (DMC stands for Database/Db2 Management Console) is a wholly new thing from what I can see. Download dmctop here. There is also a small amount of information available about it in the IBM Db2 Knowledge Center.

This article is a first take on dmctop, with a few observations. I plan to write something in more detail once I’ve had a chance to use it more extensively. I have been using it side-by-side with db2top during some load tests so I could get some real experience with it.

The Basics

Install is simple. The files come with executables – dmctop for Linux and dmctop.exe for Windows. Unlike dsmtop, I don’t have to make sure I have the proper java in my path or anything like that. I haven’t seen some of the install or random errors I saw with dsmtop. You just have to put the executable in a logical location (some directory that’s in your PATH env variable makes sense) on a database server or client, and execute it. Execution is as simple as:

dmctop -d DBNAME

You can also use it remotely, without having the database cataloged, by specifying a hostname, port, username, and password. I started out with it on a central server I use for monitoring tools before moving it to some of my database servers. The remote connectivity works well, but as @LNumrych pointed out, you have to specify the -u parameter, otherwise it ignores the hostname and port and looks for a local database.

I haven’t yet tried it on a remote database that I have cataloged without specifying the hostname and port.

Once you start it you are greeted with a screen that looks something like this:

Menu Pane

Note this “pane” at the left:

This is the Menu. Your focus can be either on the Menu pane or on the main content pane. Shortcut keys mostly work when your focus is on the Menu pane, so if you’re trying to use a shortcut key and it is not working, make sure your focus is in the right place. You can tell if your focus is on the menu pane, because it has a double line around it instead of a single line for the border. However the border sometimes does and sometimes doesn’t change on the main pane. I’m thinking of it like command mode vs. insert mode in vi. In the screen shots above, the focus is on the Menu pane.

For users of db2top, this pane should look familiar. These are maily the same options we have in db2top, but there are definitely some additions. Fully expanded, the menu looks like this:

You can navigate up and down using the arrow keys, and select a highlighted item by hitting enter. Some items have sub-items. Hitting enter on any item with sub-items expands it on the menu, and does not change the screen in your main pane. Hitting enter on a sub-item or item with no sub-items changes your main pane to whatever you’ve selected.

Once you have some pane on the right with more data than will fit on the screen, all you have to do to scroll is use the arrow keys. So easy!

As far as ease of navigation and use, I’d mostly say that it is not only so much better than dsmtop, it is also actually better than db2top.

The Good

Time/Duration/Baseline and RESET!

One of my problems with dsmtop was understanding the time-related information it was providing for me. The parameters for timing were confusing, and I don’t think I really ever fully understood them, despite trying. I already understand the basic options for dmctop, and they make a lot of sense to me. When you open dmctop, it captures a baseline. By default, the metrics that are shown after this are since that baseline. Every 10 seconds (by default) those metrics are updated, but mostly still represent numbers since that baseline. You can reset that baseline at any time by making sure that the Menu pane is active, and hitting r. What I’ve described so far is the delta mode, and it seems to be the default. By hitting K while the menu pane is active, you can switch to “actual” mode. This means that the values displayed represent data since the last time the database was restarted.

Information about your mode is displayed in the upper left of the main pane:

Yes, in this case, I had dmctop up for over 6 hours – I’m playing with it, and I was curious about leaving it up for a while. The “Next Refresh” counts down every 10 seconds (by default).

The Database Overview Screen

So far, I’m enjoying the database overview screen. My two favorite features are the CPU utilization built in (not something you have to hack in by editing a file like db2top or just missing like dsmtop), and the Read Efficiency (one of my favorite metrics). Lock Timeouts flashes when there is one – I’m not sure if other areas flash yet. Note that many of the values in the throughput section are per second when you’re in delta time mode, and hard numbers when you’re in actual time mode. Likewise, some of the metrics in the other columns are per minute. It seems a bit odd that it’s telling me Deadlocks per minute, but the per minute indicator isn’t after Lock timeouts in Delta mode. I want to spend more time on this screen and get to know it better.

The Time Spent Screen under Throughput

I particularly enjoyed this screen while watching a load test. With a decent sized monitor and normal sized text, I was able to fit the entire thing on one screen:

One of the reasons I enjoyed this is the really easy layout of information you can get using monreport.dbsummary or you can query rather painfully otherwise (I love mon_get_database, but oh lord, the 450+ columns!), WITH easy reset to just see what had happened since my baseline. One note on this screen – it seems to not take a baseline until you actually go into this screen, but once you’ve done that, the baseline is retained when you hop around other screens and used when you return to it. I do wish it would establish the baseline when you open dmctop instead, but maybe I’m misunderstanding it, too.

I spent an hour or two recently looking up the ways to find connection time based on a very vague report of “slow connection time” for some connections from a fellow engineer (not DBA). Here all I had to do was scroll down, and I could see how much time was being spent on connections.

The layout of wait time here is awesome. I can’t wait to use this screen over and over again.

What Deserves More Investigation

Like dsmtop (unlike db2top), dmctop uses a database connection. This means, for example, that you cannot use it to watch an offline backup run (I tried, and went back to db2top and db2pd for that). I would assume it also means that you cannot run dmctop against an HADR standby with ROS disabled.

Filtering options also deserve some investigation. I don’t use filtering much, but some of my colleagues do. I see that at least on the sessions screen I can filter out idle applications (yay!), but further filtering that was possible in db2top doesn’t seem to be there.

I’d love to see more documentation. There is a page in the IBM Knowledge center that has some great info like what permissions an ID needs to use it, but it doesn’t delve into the details very much. It feels like I’ve been given a surprise present that I just have to figure out, and that’s really not such a bad thing. I would be poking and prodding and seeing what I could make it do anyway.

I’d like to do some analysis of the SQL it’s running to see how impactful/good/bad it might be.

There is a background mode that I’d like to play with. This wasn’t functionality that I really used in db2top, but I’m interested to see what it can do.

I want to spend more time with dmctop before I give a very thorough review, but I’m pretty optimistic based on first impressions. I went to investigate a problem on a server where I hadn’t yet installed dmctop, and I used db2top and actually found myself missing dmctop already, when I’ve had it for less than two weeks – this is an excellent sign for dmctop.