Comparing dsmtop and db2top

dsmtop is a long-awaited refresh of the wildly popular db2top. Like db2top, dsmtop is a free tool, included with DB2. It is in the base DB2 install starting with 11.1, and can be installed on DB2 10.1 or 10.5.

History of db2top

As I personally recall, db2top first came out of IBM AlphaWorks. My understanding of IBM AlphaWorks is that it was largely comprised of tools that were written by IBMers in the trenches in IBM Global Services to do their jobs better. Someone saw these and shared them to a wider audience. db2top became available as a separate download in the days of DB2 8.1. It was included with every install of DB2 starting with 8.2 fix pack 17, 9.1 fix pack 6, 9.5 fix pack 2, and 9.7 GA.

db2top was just a killer tool back then. And really still is today. In my opinion, it is the best tool for when you simply need to be monitoring and watching a database to look for problems. However, it was basically never improved, other than bug fixes, after it was introduced. I got the opinion that the community’s love of this out-dated tool still drives some IBMers nuts. But there was no other easy way to simply watch a screen and look for things like negative locking phenomena while keeping an eye on key performance indicators like buffer pool hit ratios and the number of actively executing connections.

Another frustration I’ve always had working with db2top is that in the variety of environments I work in, it doesn’t work on Windows. This makes switching between environments tough, especially since it is only a small percentage of my team’s work that is on Windows – something that seems to be true for most DBAs.

Enter dsmtop

dsmtop is a new, similar tool to db2top. It has a similar, text-based interface, but is java-based, and can run on Windows as well as on AIX/Linux operating systems. There are a lot of similarities, but there are a number of differences as well, so while I’m spending a day watching database performance, I thought I’d have the two up side-by-side to see what I can share about each.

dsmtop works with DB2 10.1 and higher, and can be downloaded from a developerWorks community (scroll to the bottom and the “Files” section). It comes with the code starting with 11.1, and may not continue to be updated on that community =(. Here’s hoping they’ll also incorporate it into a future fix pack for 10.5.

Setting up dsmtop for DB2 10.5

To set up dsmtop:

1. unzip the file (the unzip command may work on Linux/Unix for this)
2. Set your path to include a path to Java 1.7 or later libraries. This can most likely be accomplished on AIX/Linux using this in your profile after sourcing the db2profile:

   export PATH=${DB2_HOME}/java/jdk64/bin:$PATH

3. Add dsmtop to your path:

   export PATH=/dsmtop/bin:$PATH

Assuming you have done this as an ID otherwise set up to access DB2 (the db2profile sourced), you can execute it with similar syntax to db2top:

dsmtop -d sample

This is my favorite way to execute it, and uses JDBC type 2 drivers. You can also execute it with JDBC type 4 driver syntax:

dsmtop -d <database name> -n <host name> -r <port no> -u <user ID>

dsmtop will then prompt you for the password.

I’m not fond of using the type 4 driver syntax for so many things these days. I think the push that direction comes from the way Oracle does things, but it just doesn’t feel like the DB2 way.

What they look like

Database Screen

Here’s the most basic screen (in my opinion), side-by-side. This is the database screen which is often my jumping-off point when watching database performance.

Please excuse the wide image, but this is how I’m running them while watching performance today. Having them up side by side is instructive.

The first difference to notice is that db2top’s default refresh interval is 2 seconds, while dsmtop’s default refresh interval is 5 seconds. No matter what I do, I cannot get the two sessions to refresh at exactly the same time, so I’m leaving them at the default intervals for now. This also means that I can never get the same exact numbers out of two different sessions, but running in cumulative mode, I should be able to get pretty close.

There are also far more fields on the dsmtop screen. db2top’s database screen gives me 28 fields plus the graphs, while dsmtop gives me 40. Let’s look first at what’s on the dsmtop screen that was also on the db2top screen.

The fields in the red rectangles are the ones that also appear in db2top. However, there are several fields from db2top that are missing in dsmtop:

The loss of the sort overflow percent strikes at my heart. That’s one I always look at. Yes you can calculate it in your head, but I like seeing the number right there. That’s the only lost number I’ll really miss.

All memory in dsmtop seems to be specified in bytes. There’s an option on the “Options” menu that seems to point at being able to format it with K, M, G, but it doesn’t appear to work for me on this server. This is mildly annoying.

I love the addition of the “System Physical Mem” – even though this is a value that isn’t going to change, it’s often a question that I ask while staring at this screen.

Note that there are some discrepancies in the numbers here, too. One that caught my eye was the Lock Wait count. In db2top, this is only populated with current lock waits and it flashes to black text on a white background if there is a current lock wait in progress. I’m still watching to see what an active lock wait looks like in dsmtop. That’s new – to find myself hoping for a lock wait!

There is still information missing on this screen that I’d like to see, and fields that I never use. I want to see Lock Timeouts, and I want to see Read Efficiency (Rows Read/Rows Selected).

Monitoring HADR

Only dsmtop can tell you about multiple standbys, so that is a point for it. However, dsmtop requires a database connection, which means you cannot run it for the standby, unless you have Reads on Standby enabled and apply a special build. db2top can be run on the standby, as it does not require a database connection. I don’t often want to see what’s going on for a standby, but during load tests, I do, and I have to use db2top for this on most systems. Most of my implementations do not enable reads on standby because of the licensing implications.

Collection Interval vs Refresh Interval

In dsmtop, the delta span feels incorrect, counting up to 60 seconds even with a 5 second refresh interval and changing figures.

In db2top, the collection interval and the refresh interval were the same, at least as far as this user could tell. If you had an interval of two seconds and you were looking at deltas, those numbers seemed to cover what happened over the last two seconds. dsmtop has a collection interval, which specifies the periods at which the delta baselines are reset. This is 60 seconds by default. It also has a refresh interval, which is how often the current metrics are updated. The default refresh interval is 5 seconds. I wonder if one could use this as a poor man’s reset – how large can I make the collection interval? There are some experiments to be done here.

Impact

I don’t have hard numbers on impact, nor have I done a science to test it. However the old db2top relies on numbers out of the snapshot monitor, which was a heavier weight interface. The new dsmtop pulls data from the MON_GET* interfaces. This means dsmtop is generally less impactful. However, if you watch it much, you end up seeing dsmtop’s queries in your monitoring. There are large numbers of queries. They show up in other monitoring tools, and the only reason you might not see them in dsmtop is that dsmtop can exclude its own queries from monitoring. You can disable this behavior on the settings screen of dsmtop:

Documentation

There is decent documentation on dsmtop. Oddly, it is not in the IBM DB2 Knowledge Center, but in a wiki. The reference page on what is in the various views is excellent.

With db2top, we had a preferences file to store things like column ordering and sorting and so forth. dsmtop has a similar file. You can write out your current settings as preferences by choosing File->Write preferences:

Functionality I’d Love to See

I would love to see a screen that would show balance of memory and a few other metrics between all databases in an instance. I would also love to see a screen showing balance of memory between multiple instances on a server. I understand the difficulties with this, but they’re still screens I would use from time to time.

As far as I can tell, there is no reset functionality in dsmtop. This is something that will keep me using db2top from time to time, because seeing only data since a certain time can be very useful, and is something that IBM is making it harder and harder to do without tools or scripts. I understand this is because dsmtop is not using the snapshot monitor, but even I have written tools to simulate a monitor reset – it is certainly possible.

In db2top, I could add numbers on cpu activity to the top right corner with a quick entry in my .db2toprc file. This is a number I find it useful to watch while watching load tests, even if it was a couple of seconds behind. I have not yet found a way to get that in dsmtop.

Maybe it’s pie-in-the-sky wishful thinking, but I’d love to be able to customize my own screen to show the metrics I want to see.

Navigating dsmtop

If you want to use the same keystrokes to get to the same screens you used in db2top, simply use your ALT button with them. The keystrokes changed for changing screens because there is more there. There are menus at the top with submenus and a lot of options.

I am still exploring to understand everything that is in dsmtop, because there is more there.

Overall Comparison

There are pluses and minuses to each of db2top and dsmtop. I’m trying to use dsmtop when it’s available because I suspect I will grow to love it as I get used to it. I was absolutely thrilled when I heard it would work on Windows, and I need to be used to a tool that can be used on any OS.

System Details

In case it is relevant to anyone, the examples here are using DB2 10.5, Fix Pack 8, and dsmtop v1-3e.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home