Raghu On Tech

Pro tips on managing data at scale

Using MON_GET_PKG_CACHE_STMT To Identify Waits

by | Jun 7, 2020 | 0 comments

As a DBA I worked on a number of projects fighting fires and putting them off successfully. However in the beginning of my career I was not sure where to start, as I matured as a DBA there is a pattern that developed to troubleshoot performance issues. Each of the following areas need different kind of strategy

  1. Tuning SQL
  2. Creating and/or tuning indexes.
  3. Physical database design.
  4. Troubleshooting waits or bottlenecks (this will be the topic for the rest of this blog)

When troubleshooting bottlenecks or waits I use a number of tools such as db2top, db2mon (it provides great info), activity event monitors etc. However, if I need to quickly get an overview of my database wait statistics, I go after MON_GET_PKG_CACHE_STMT table function. If your package cache is sized appropriately, this can be invaluable in troubleshooting performance issues. Please remember that this will only provide you the wait statistics for the SQL statements that still exist in the package cache.

If you would like to see all the wait statistics available to you that are part of activities please visit below link, these same metrics are available to you via ACTIVITY event monitors in the ACTIVITY_METRICS table.


In this blog post, I am going to show you couple of SQL that gives you an overall picture of activity waits on your database as per the point in time snapshot of the package cache.

SQL 1:

Below SQL will provide the percentage of time your database activities spent waiting on some thing, when compared to the total activity time. Lesser this value is the better the health of your database. What is a healthy/normal value really depends on a lot of factors and business tolerance levels, I have databases that have WAIT percentages at around 10% and some of them around 30% and business is equally happy with both the databases.

Output from the above SQL would look some thing like below.

Output 1: From database one, non-pureScale.

Output 2: From database two, pureScale.

Now you know there are some waits in the database, read on to learn more about the waits.

SQL 2:

Now, to determine what finer components are contributing to the total wait time, you can run below SQL. This SQL looks lenghty but there is not a lot to it, I just had to PIVOT the result set using plain old SQL for the data to look pleasing on the eyes.

Output from the above SQL will look like below.

Output 1: This output corresponds to the output 1 of previous SQL. Below output is interesting because I have a semi analytical database thats taking advantage of intra partition parallelism and as a result 45.53% of 12.53% (from SQL 1) percentage of my total time is being spent with in FCM table queues for the parallelism (i.e. approximately 5.46% is the over head of the intra partition parallelism of my total activity time). As you can see there are some latch wait times and log disk wait times etc.

Output 2: Below output is from one of my pureScale databases, corresponding output of SQL 1. For my pureScale the wait metrics look entirely different compared to my non-pureScale intra partition database. As you can see my top wait metric here is physical read time i.e. POOL_READ_TIME. Most of my wait is on disk and remind you this is not bad for my environment at all. I still have healthy hit ratios and manageable physical IO. However going down you notice that there is CF_WAIT_TIME which is specific to the pureScale and it accounts for approximately 30% of the total 16.25% wait time (approx 4.8%) over head due to the CF.

Metrics like these are invaluable to understanding your workload and taking action to fix any suboptimal areas with in your database system. I hope you learned something from this article and let me know what simple SQL you use on a regular basis to troubleshoot your Db2 environments ?


Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This
%d bloggers like this:

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.