I usually don’t have (nor want) direct, real-time access to production databases, yet I’m sometimes asked to diagnose and correct performance issues with them.  So my typical MO is to collect periodic snapshot files (driven by cron or schtasks) and run them “offline” through a set of tools I’ve developed for postmortem analysis.  These tools of mine measure and report trends, calculate deltas, do performance calculations, and flag areas of concern.

But occasionally, I do need to monitor databases in real time.  For this, I’ve relied on third-party tools, such as Quest Spotlight, the free and simple db2mon, db2pd and get snapshot commands, and even Windows perfmon against the DB2 counters.  All the while, I wondered why IBM didn’t provide similar real-time monitoring tools.

Top for DB2

But IBM recently jumped into the game with the db2top utility for Linux and AIX.  As the name implies, it’s like top for DB2.  It uses a text user interface (it’s curses-based) which, despite the retro-80’s feel, is a benefit.  That is, it’s super-fast (jumping between views is quick and easy), and, of course, it runs from a command shell, so no X required.

Db2top shows just about everything available in DB2’s snapshots, and then some.  For example, it automatically calculates things like bufferpool hit ratios, sort overflow percentages, average I/O times, async read percentages, average execution times, and lock chains.  In most views, you can use the arrow keys to scroll right and left to see additional details; I found it useful to increase the screen width to see more without scrolling.  But, of course, the real benefit is watching instantaneous updates and deltas.  This avoids having to “do the math” yourself by processing a series of snapshot files.

With Any More Features, We’d Run Out of Alphabet

There are some nice tie-ins, like the ability to grab an instantaneous snapshot (S) for the corresponding screen, and run explains (L) directly from the Dynamic SQL screen.  Both toss the results directly into vi for viewing.  You can even force applications directly from the sessions (applications) view (bwaaaahahaha!)  And navigation aids like column sorting (Z, z) and screen “freezing” (f) come in very handy when drilling in for closer analysis, particularly when there’s a lot going on.  Quick reference help (h) is available in case you forget these keystrokes.

The bottlenecks (B) screen is useful for finding the “problem children” – those agent(s) consuming the most of each type of DB2 resource: CPU, memory, I/O, locks, sorts, etc.  This makes for a good starting point when jumping into a crisis.

The tool also supports capturing snapshots over time, running analysis reports, exporting to Excel, and replaying results.  I haven’t done much with these features yet, but they may very well take the place of my home-grown snapshot analysis tools.

Just What You Need

Before this, if you told me you had “Spotlight for a command shell,” I’d think you were crazy.  But db2top comes remarkably close.  Overall, it has a lot going for it: ubiquity (it’s there with every DB2 9.x *ix install), speed, and just the monitoring tools I need (no more, no less).  There’s not much lacking, but the following additions would be nice:

• Extensibility (or source code).  For example, I could then add my own performance calculations – things like log, victim, and threshold trigger ratios for page cleaners.
• Tie-ins to event monitors, at least db2detaildeadlock.
• A Windows version, even if it required Cygwin.

Yet I’d prefer it miss a few of these flourishes than be laden with extra features that only add bulk or cost, or that just get in the way.  Too many other tools fall into that trap.