Finding the Problem using MONREPORT Reporting Module — It will be what SQL is running!

Many times I get calls that something is sucking up the server and clients need to know what’s up. A very simple way nowadays is to use the MONREPORT reporting module first provided in DB2 9.7.  It should be the first tool in your toolbag.  Simply connect to the database where the reported problem is, and from a command line issue the following command:

“db2 call monreport.currentsql > csql.txt”

Examine the contents of the output file and if a query is actually executing it will be captured here.  You can run subsequent calls to the module and don’t redirect the output to a file until you “catch” one executing. If you don’t catch one executing after repeatedly calling the monreport.currentsql stored procedure there is nothing running of interest or whatever is running is running sub-second. It is then doubtful SQL is your problem. Believe me, if it is, you will catch the SQL using this technique, guaranteed. There are other ways but this is easiest. Stay Tuned!