Db2 Provided Monitoring Tools — A Plethora!

I though I would do this post to highlight many of the monitoring tools that are provided with Db2 and that I use on a regular basis for monitoring and tuning databases and applications. The first tool I use when troubleshooting an application performance or locking problem is db2top (or dsmtop if you prefer). I find db2top useful for reviewing sessions and applications that are connected to the database. You can easily find the top executing application in terms of cpu, IO or a bunch of other metrics. You can then go right into an explain of the SQL within the tool. The next set of tools I use is the MONREPORT reporting package. Prior to this package becoming available, I had a script that used a RANKING function to capture and rank the Top 10 SQL. With the MONREPORT package, you can capture currently executing SQL by issuing the “db2 call monreport.currentsql” command. This will show you the top executing SQL in terms of CPU, Rows Read/Written and Direct Reads/Writes. You can then use db2exfmt to explain and review the SQL. Next, the MONREPORT.PKGCACHE option will show you dynamic and static SQL in the package cache and will rank them in 10 categories, such as CPU, IO, WAIT, Lock wait, etc…What I then do with the highest ranked SQL in terms of cost (or a specific SQL of interest) is take the EXECUTABLE_ID of the SQL and run that through the EXPLAIN_FROM_SECTION stored procedure which will capture the explain information into the explain tables. I then use db2exfmt to format the SQL and access path for review. I can also take the SQL and run it through design advisor to see if there are any indexes that might offer a big improvement. But the first option in tuning SQL I use is to see if there is a rewrite of the SQL to improve performance, along with checking to make sure runstats are correct and current. Only after that do I pursue an index solution, however, don’t discount the use of design advisor as I have found that in many client databases, indexes have been poorly designed and big improvements can be made. And finally, I use the new LOCKING event monitor if I am pursuing locking problems. This event monitor should be defined and running on all your databases as in conjunction with the db2evmonfmt tool it can provide you and application developers with the information necessary to correct lock-contention issues.

I also use db2pd and many of its options to review OS information, bufferpool performance, memory usage and transaction, application and locking activity. The combination of transaction, application and locking activity can be used to identify lock holders and waiters and help you to pinpoint lock contention issues ( example: db2pd -d dbname -trans -applications -locks showlocks). A very powerful option with db2pd is the -tcbstats index option. When specified, it will show you insert, update, delete activity for indexes and tables and shows much of the meta data associated with a table and tablespace. It can be used to identify the most heavily used indexes, tables and tablespaces, and overflows. An lastly, I use the db2pd -dbptnmem option to review instance and database memory allocation and usage. Some of the above monitoring tools require either that snapshot monitoring switches or request and activity based monitoring DB CFG parameters be set so that the information is collected for use with these tools. I didn’t touch on the MON_GET table functions which provide a wealth of Db2 information at very low overhead. They will be covered in a later post.