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
To use db2top, you must have SYSMON or higher (SYSMAINT, SYSCTRL, or SYSADM) authority. With a privliged user simply issue:
db2top -d sample
Once you have entered db2top successfully, it will look something like this:
Pressing the h key in db2top will get you the help screen, which has a list of all the nifty options:
1. Pressing the d key in db2top will get you to the database screen:
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.
2. Press l to get to the sessions screen:
To expand column further use alt + > (right) and alt + < (left)
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:
3. 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 for your time.