Collecting object statistics with the RUNSTAT utility

DB2's optimizer does a great job of finding the best execution path for a given SQL query. However, it relies on object statistics to achieve its purpose. We have discussed briefly, in Chapter 2, Administration and Configuration of the DB2 Non-partitioned Database, how to set up automatic maintenance, also called asynchronous stats collecting. We will also look briefly at synchronous stats collection.

For large tables, however, it is better to gather the statistics on just a part of the table. Since the default setting for automatic stats is to gather stats on all rows of a table, this can be time-consuming. You can override this setting for a particular table by setting a statistics profile, so DB2 can collect statistics on just a sample of the table.

How to do it...

In this case, we will run the stats on a daily sales table and set the profile. In our case, we collect samples using Bernoulli sampling, which collects a sample of 10 percent...