DB2 10.5 for Linux, UNIX, and Windows

num_freqvalues - Number of frequent values retained configuration parameter

This parameter allows you to specify the number of "most frequent values" that will be collected when the WITH DISTRIBUTION option is specified on the RUNSTATS command.

Configuration type: Database
Parameter type: Configurable Online
Propagation class: Immediate
Default [range]: 10 [0 - 32 767 ]
Unit of measure: Counter

Increasing the value of this parameter increases the amount of statistics heap (stat_heap_sz) used when collecting statistics.

The "most frequent value" statistics help the optimizer understand the distribution of data values within a column. A higher value results in more information being available to the query optimizer but requires additional catalog space. When 0 is specified, no frequent-value statistics are retained, even if you request that distribution statistics be collected.

You can also specify the number of frequent values retained as part of the RUNSTATS command at the table or the column level by using the NUM_FREQVALUES command parameter. If none is specified, the num_freqvalues configuration parameter value is used. Changing the number of frequent values retained through the RUNSTATS command is easier than making the change using the num_freqvalues database configuration parameter.

Updating this parameter can help the optimizer obtain better selectivity estimates for some predicates (=, <, >) over data that is non-uniformly distributed. More accurate selectivity calculations might result in the choice of more efficient access plans.

After changing the value of this parameter, you need to:

Run the RUNSTATS command again to collect statistics with the changed number of frequent values
Rebind any packages containing static SQL or XQuery statements.

When using RUNSTATS, you have the ability to limit the number of frequent values collected at both the table level and the column level. This allows you to optimize on space occupied in the catalogs by reducing the distribution statistics for columns where they could not be exploited and yet still using the information for critical columns.

Recommendation: In order to update this parameter you should determine the degree of non-uniformity in the most important columns (in the most important tables) that typically have selection predicates. This can be done using an SQL SELECT statement that provides an ordered ranking of the number of occurrences of each value in a column. You should not consider uniformly distributed, unique, long, or LOB columns. A reasonable practical value for this parameter lies in the range of 10 to 100.

Note that the process of collecting frequent value statistics requires significant CPU and memory (stat_heap_sz) resources.