Why runstats on table in db2

This will give the optimizer false information and cause it to underestimate the number of rows in the table.

Many Runstats options were mentioned in the article and it can be overwhelming to new developers or developers switching database platforms. Listed below is summary of the Runstats best practices that will ensure that Runstats is running optimally, in most use cases. Effective scheduling of the Runstats utility and leveraging its various data collection features will facilitate the proper balance between feeding the optimizer critical information and the utility not being intrusive to the application.

Joe Geller Apr 06, AM. Michael, Howard is correct - Db2 will under estimate the filter factor and cardinality if columns are correlated but you don't have column group statistics. Michael Hannan Apr 06, AM. Column Group cardinalities help make the estimate more realistic. Correlation of column values decreases the group cardinality.

Search Options. Skip to main content Press Enter. Skip auxiliary navigation Press Enter. Contact Us. Quick Links. My Profile. My Contacts. My Networks. My Presentations.

My Inbox. My Forums. My Communities. Getting Started. Make a Discussion Post. Join a Community. Make a Connection. Explore Forums. Post Your First Message. Complete Profile. Skip main navigation Press Enter. Toggle navigation. Off to the races: Running Runstats by Howard Goldberg. For example, Common Runstats mistakes Not enclosing column groups in parenthesis to collect column group stats The optimizer cannot detect correlation between columns when multiple columns are used in a where clause unless there is an index on all of the columns or column group statistics are present.

Not using Statistic Profiles Failing to use statistics profiles is more a missed opportunity rather than a mistake. Figure 9 — Card values for skewed and un-skewed tables. Summary Many Runstats options were mentioned in the article and it can be overwhelming to new developers or developers switching database platforms. Please note this is just my rule of thumb for increasing these parameters.

Thanks Ember, how do you determine what columns currently have statistics collected on them. In DB2, we generally go with all columns. A powerful strategy can also be to gather statistics on columns that are frequently queried together or joined on together and have correlations of some sort. Hi Ember — can I get your opinion? Is there any value in performing runstats on tables that are only ever read from?

If you can reliably define such tables and have a regular annual? There are three potential problems in not doing runstats on them.

First usage patterns may change and you may not be aware of the change. Third, if you or a consultant or vendor run a query to evaluate runstats within the database, you or they might mistakenly feel something is being missed. Those are mostly minor annoyances. Your email address will not be published. Notify me of follow-up comments by email. Notify me of new posts by email. This site uses Akismet to reduce spam. Learn how your comment data is processed. Skip to content On looking over my previous posts, I found no fewer than 25 of them that mentioned runstats.

What Runstats is a utility. Distribution statistics include two things: Frequent value statistics — DB2 notes the most frequent values. By default, the 10 most frequent values. Using the above syntax, this is collected for every column. Dozens or hundreds of potential access plans are evaluated based on how expensive the optimizer thinks they will be, and the optimizer then chooses the cheapest access plan.

The most critical input to this process is the statistical information about the data being returned. A cost cannot be accurately estimated if there is no estimate of rows in the table. Runstats can be done fully online, though the activity may impact server resources like CPU utilization, and may also impact buffer pools as it involves full table scans. Even during high-impact or high-volume times, the cost of runstats is usually worth the impact. Db2 offers automatic runstats that can be useful.

However, there are some situations where they do not kick in when we need them to. It is therefore critical to augment automatic runstats with targeted runstats after high-impact operations, and to regularly make sure that statistics are updated on everything. Automatic runstats is configured by default in new databases.

The following parameters in the database configuration should be on for automatic runstats:. If using automatic runstats, it makes sense to either do a full runstats on a periodic basis daily, weekly, or monthly or to do a periodic runstats targeted to hit tables that have not had runstats done in a while.

There are some non-changing tables, particularly in analytics environments, where it may make sense to never do runstats. They are the exception, and not the rule. You can also control when automatic runstats happen and exclude tables by using a policy file.

The output above tells us that tables or statistical views in this database have never had runstats done on them. The others have various runstats times spanning over a few months. It is also possible to list out all runstats actions taken, whether they are automated or they are manual over a specified period of time:.

There are a vast number of options available to make statistics more detailed and relevant or to make execution of runstats faster or less impactful. Full coverage of every option is beyond the scope of this article.