Bookmark and Share
RSS

Recent Posts

Db2 12 Automated Statistics Collection

August 01, 2017

Terry Purcell, who leads the Db2 Optimizer team, recently told me that at least 80 percent of all SQL access path performance problems reported to IBM could be avoided if customers had collected proper runstats.

The optimizer is very smart, but it can only do so much without information about the data being searched. To that end, optimization statistics feedback was introduced in Db2 11. However, it didn’t go far enough because the information was not automatically available to the runstats utility. You either had to purchase a tool that could read the information written to the feedback table, or you had to write your own programs to decipher the information and create input parameters to be used by runstats. As you can imagine, in most cases this information did not benefit the customer.

I'm very happy to see that Db2 12 now automates the process of generating feedback. Even better, the feedback is automatically fed directly into the runstats utility through statistics profiles.

To learn about the process of creating and using statistics profiles, see the Db2 12 for z/OS Managing Performance guide (section title: Statistics profiles).

To get started, the Db2 subsystem parameter STATFDBK_PROFILE must be set to YES. Of course these settings only take effect when the activated function level is V12R1M500 or higher. When set to YES, the system will either create new profiles (where none exist) or modify existing profiles based on the recommendation type. For example, when the TYPE="C", if the existing statistics profile does not include the column group, this is added with the COLGROUP syntax. KEYCARD recommendations add the corresponding index. If no statistics profile exists for the table, a new profile that includes the column group and INDEX(ALL) is created.

See the IBM Db2 Knowledge Center for a complete list of statistics recommendations.

The process of having the runstats utility use the statistics profile to collect statistics is specified through the input control statements with the parameter USE PROFILE:

RUNSTATS TABLESPACE ts-name TABLE table-name USE PROFILE

Have you implemented automatic statistic collection profiles? Please share your experiences in comments.

Posted August 01, 2017 | Permalink

comments powered by Disqus