DB2 Basics: db2top

There are a lot of things I can cover on db2top, and probably more tips and tricks using db2top than many other tools out there. Searching the web on db2top gets more good results than on many other db2 topics. I thought I’d start with some of the basics. Using db2top requires some general knowledge of how db2 works. I really debated whether it even qualified for my DB2 Basics series, but there are a few basic things that can help with using db2top.

What is db2top?

db2top is a “real-time” tool for seeing what is currently going on within a db2top instance or database. I put real-time in quotes, because technically it is a couple of seconds (depending on how you invoke it) behind real time, and it can also be used in a number of ways. db2top presents data in a similar manner to the unix/linux tool top. I think of it as a text-based GUI. Not fully GUI, but it is more graphical and dynamic than many text interfaces.

db2top originally came out of IBM’s Alpha Works. It was bundled into db2 starting in fixpack 10 of 8.2, 9.1 FP6, 9.5 FP2, and 9.7 and higher GA. I installed the alpha works version on databases long before it was officially included with db2. db2top is a revelation in database monitoring for those of us who haven’t generally had performance monitoring tools. A way to watch what’s going on in db2 in near real time.

db2top is only available on the unix and linux platforms, not on Windows.

My understanding is that db2top interfaces with the snapshot monitors. I think that db2top enables the monitoring switches that it needs when you start it up – for db2top’s use only, so that you get data even if you don’t have all the monitoring switches enabled by default.

Running db2top

It is simple to run db2top. There are two basic modes you can run db2top in – interactive or batch mode. This blog entry will focus on interactive mode. I’ve only run it a few times in batch mode, and found replaying the data a bit difficult and the data collected a bit large in size. For historical data, I prefer a custom process of capturing snapshot data periodically or a vended tool for that.

To use db2top, you must have SYSMON or higher (SYSMAINT, SYSCTRL, or SYSADM) authority. With a privliged user simply issue:

db2top -d sample

If you do not specify the database name, and have not previously written out a .db2toprc, then you’ll get this error:

*************************************************************************
***                                                                   ***
*** Error: Can't find database name                                   ***
***                                                                   ***
*************************************************************************

Once you have entered db2top successfully, it will look something like this:

Notice on this screen that the instance and database name are displayed in the upper right – I’ve blurred them out. I also have the CPU displayed in the upper right. That’s a customization I’ll explain later. In the upper left, you can see a number of options. One of them is the refresh interval, which in this case is the default of 2. You can specify a larger refresh value by invoking db2top with the -i option and the number of seconds. Also, if you have a problem on the system that is preventing timely refresh, this value will show the time since the last refresh, even if it is larger than the specified interval. This can be useful to know on systems that are really in trouble.

This initial screen also tells you how long this database has been active, and the time of the last backup – useful pieces of information in some cases. The initial screen also lists a few of the more common interactive options.

.db2toprc

If you only have one database that you want to run db2top for on a particular instance, or if you have a default database you want db2top to run for, once you’re in db2top, hit ‘w’ to write your current configuration out to .db2toprc. It will include the database and column orders on the various screens. Once you have done this once, the database name you used when invoking db2top that time will be assumed, and you can just get into db2top using db2top – no need to specify a database name.

A file called .db2toprc will be generated in the home directory of the current user. If you need to specify a different location, you can use the environment variable DB2TOPRC to specify the location to look for the .db2toprc file. In the absence of this variable, db2top will look first in the current directory, and last in the current user’s home directory. I like to keep my .db2toprc files in the home directory to keep things simple.

There are some fun things you can do with .db2toprc. It allows you to store your favorite order for columns on the various db2top screens – you can store this either by using w when in a session with the desired order, or by editing .db2toprc directly.

One of my favorite things to do with .db2toprc is to include cpu utilization. The line for this is in the various documentation locations – both the PDF that has been passed around since AlphaWorks days and in the IBM Knowledge Center. In some versions there were typos, and different element numbers are needed on Linux and AIX, so make sure the line works for you. To get that nifty little cpu report in the upper right corner, you just add this line to .db2toprc (this one works for me on AIX 7):

cpu=vmstat 2 2 | tail -1 | awk '{printf("%d(usr+sys)",$14+$15);}'

Note that this method generally has numbers that are 4 seconds behind, but tends to be close enough. I like to have it to glance at while I’m monitoring, but if I need something detailed or long term, my SA is likely to have much better numbers and historical information.

Help Screen

Pressing the h key in db2top will get you the help screen, which has a list of all the nifty options:

I cannot possibly go through all the available db2top options in this article, but that’s a nice place to play from and see what you can find.

From the help screen, just hit enter to get back to the screen you were on when you hit h.

Database Screen

Pressing the d key in db2top will get you to the database screen:

This is the screen I often start from before moving into other screens. Note the “Lock Wait” field at the lower right. If there are any lock-waits, that will be highlighted in white, making it stand out. It’s great for quickly seeing if you’re seeing lock-wait issues, lock time out issues, deadlock issues, and so forth. This screen also shows you values for overall key performance indicators – such as your bufferpool hit ratio, percent of sort overflows, Total and active sessions, deadlocks, log reads, and so forth. Too bad it doesn’t have an index read efficiency value.

Another tip that’s good to cover on this screen – by default, db2top subtracts metrics and shows you only what has occurred in the last interval (default 2) seconds, where the default interval is 2 seconds. You can press k to tell db2top to show you everything since the last database activation or since the last reset in this session. This can be useful for seeing things longer term, but honestly, if I wanted data since the last activation, I’d be using the mon_get functions and views. What you can do is use k in addition to R. If you press R, it will reset the monitor switches in the db2top session, meaning you can set a point in time manually, and then see everything since that time. If you press R, it will prompt you to confirm like this:

Once you have confirmed, it will switch to this cumulative view. Pressing k repeatedly will toggle between the cumulative view (with whatever reset point you’ve specified), and the current view of only things that have happened in the last interval seconds – you can switch between the two repeatedly without loosing your reset point. This behavior applies on most screens, not just the database screen. So if you then hit l to go to the sessions screen while you’re in cumulative view, you’ll be in the cumulative view and can easily get out of the cumulative view by pressing k

Sessions Screen

This is another of my favorite screens. I like to look at it to understand how busy my database is. Press l to get to the sessions screen:

This is a screen I like to view in a wider window. db2top does well with resizing your screen to see additional columns of output – it will give you as much as you can fit on screen, so simply making my terminal window wider, I get:

Also, depending on what I’m looking for, I often play with column order on this screen. To change the order of the columns on any db2top screen, simply hit c:

On this screen, you can enter the numbers of columns in the order you want them. You don’t have to specify all columns you want to see if you want to just bring a single column to the front, you can bring that to the front by specifying only it.

Hitting enter will then take you back to the sessions screen with the different column order:

From the screen itself, you can change the column sort order by hitting z (for desc sort) or Z (for asc sort), and then entering the column number. But wait, the column number for sort that you enter is the DEFAULT column number. This gets confusing because you could have a totally different order on your screen. To get the column number that you really want, you’ll have to toggle over to the columns screen by pushing c and then go back to the sessions or whatever screen that you want the sort to occur on, and then toggle back to the columns screen again to make sure the sort is what you wanted. Also remember that numbering starts with 0:

And this is the result on the columns screen:

One last useful thing that I want to cover on the sessions screen. Pressing i will toggle whether idle sessions are visible or not. Especially if you’re using a non-standard sort-order, it can be useful to only look at sessions that are not idle.

Locks Screen

My favorite way to look at lock chaining issues is using db2top – it is so much quicker to see this way. To get to the the locks screen, use the U option (upper case, not lower case):

Now this screen looks pretty boring on a largely idle database, but notice in the middle of the bottom, it says “L: Lock Chain”. What this means is that if you press L, it will bring up a lock chain that shows all application handles that are currently holding locks that are blocking other applications.

(thanks to @idbjorh for the image)

Dynamic SQL Screen

While I now prefer using mon_get_package_cache_stmt for digging into SQL, I have also used this screen before. Press D in db2top to get:

This lists the dynamic SQL currently executing in the database. Notice that like the locks screen, there’s an option for more information in the center bottom of the screen: “L: Query Text”. Since the column only shows you so much of the query, you can press L and then enter the SQL_Statement Hashvalue to get the full query text:

The full text appears like this:

Notice the options at the bottom of the window. Directly from there, you can explain the statement or write it out to a file – which is good because copying from that “window” does not work well.

Nifty New Tip

And in the “stuff I didn’t know, but learned while blogging about something I do every day” category – did you know you can scroll through the columns by pressing < and >? It works, and can be interesting. I do not know of a way to scroll up and down, but with a two second refresh, I can understand how scrolling up and down would be pretty meaningless. I wonder if there’s a way to do that in replay mode.

I also learned that hitting the right or left arrow will cause an immediate refresh of the data, not waiting for the 2 second interval.

This is Just a Primer

There are so many things on each of those screens that I did not cover in detail. Things I do fairly frequently. There are also screens I use at least once a week that I did not cover. I remember when they made it so buffer pools could easily be changed online, I spent most of a holiday peak period watching the buffer pools screen (among others) and tweaking buffer pool sizes.

db2top is astoundingly powerful, but it requires some experimenting and researching to learn about it and figure out how it works for you. If you have vended or pay-for-use IBM utilities, it may not be all that useful, but it’s great for when you don’t have those tools.

References

developerWorks article on db2top: http://www.ibm.com/developerworks/data/library/techarticle/dm-0812wang/
The K guy’s excellent series on db2top: http://www.thekguy.com/db2top

• 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