I have a DB2 database but the stored procedures in the batch are running slowly. I would like to investigate the cause and want to collect more running statistics including the elapsed time, cpu time, i/o, etc., of the SQLs in each stored procecdure.
Anyone has ideas how I can collect and analyze (i.e., any analyzing tool) the statistics?
If you are using DB2 version 9.7 or higher you can use the new bunch of monitor routines. If you want to monitor statistics of single statements I recommend the MON_GET_PKG_CACHE_STMT table function. The advantage of this function over snapshot mechanism is that it is able to monitor both static and dynamic SQL statements. Snapshot mechanism can provide information about dynamic SQL statements only.