MONREPORT.CURRENTSQL

A client of mine recently made some table changes along with some SQL changes for a frequently executed SQL statement. Made bufferpool changes to account for the larger page size and am seeing fairly low physical reads. However, right away I noticed from the disk busy graphs that the drives were busy at a steady 10% rate. This is unusual for this applications as most data is found in the bufferpool. I did some snapshots to try and find the SQL causing the disk IO but nothing jumped out at me. Ran Top SQL script and found a statement then I ran the “db2 call MONREPORT.CURRENTSQL” stored procedure and found the same SQL executing as I had from my Top SQL script. I drilled down and explained it and it had a cost of 688,000 timerons….Now looking at a rewrite recommendation. The point I want to make is that although I have had a Top SQL ranking script for over 13 years, I find the MONREPORT module to be pretty handy and you should make sure you use it along with CURRENTAPPS and PKGCACHE modules as part of you SQL tuning toolkit. It can also help you in identifying lock waits and other wait time events, such as log IO and bufferpool IO wait.