DB2 LUW Essential Monitoring Metrics

With DB2 LUW there are many built-in tools to use to monitor instances and databases. You need to pick what works best for you. I use db2pd, table functions, administrative routines and convenience views along with a few custom scripts. The essential metrics to monitor are:

CPU — (during normal and peak operation to include utility execution)

IO — Disk wait, queuing, response times, hot disks, log write rate

Top 10 SQL – I have been using a Top 10 SQL script for over 10 yrs and have provided it

during DB2 Tech Conference presentations to users around the world. DB2 9.7 and 10 now have a similar report available through the MONREPORT.CURRENTSQL and MONREPORT.PKGCACHE stored procedure. The nice thing with mine is you can customize  how it ranks the SQL (rows read, System CPU, User CPU, number of executions, etc). Other areas to monitor are overall memory usage, and select DBM and DB CFG shared memory

areas. For OS monitoring, use VMSTAT, IOSTAT, TOP, TOPAS, MPSTAT, SAR, and on windows Task Manage or Perfmon. You can get CPU and memory usage from the sysibmadm.env_sys_resources convenience view or associated table function. Also, through db2pd. I have done hundreds of presentations on this topic over the years. In the next month I will be writing an article on the new monitoring in DB2 9.7 and DB2 10 and how to transition from snapshot monitoring to the new monitoring infrastructure. Stay tuned!