BackgroundThe RUNSTATS utility updates statistics in the system catalog tables to help with the query optimization process. Without these statistics, the database manager could make a decision that would adversely affect the performance of an SQL statement. The RUNSTATS utility allows you to collect statistics on the data contained in the tables, indexes, or both tables and indexes. Use the RUNSTATS utility to collect statistics based on both the table and the index data to provide accurate information to the access plan selection process in the following situations:
- When a table has been loaded with data, and the appropriate indexes have been created.
- When a table has been reorganized with the REORG utility.
- When there have been extensive updates, deletions, and insertions that affect a table and its indexes. ("Extensive" in this case may mean that 10 to 20 percent of the table and index data has been affected.)
- Before binding application programs whose performance is critical.
- When you want to compare new statistics with previous statistics. Running statistics on a periodic basis enables you to discover performance problems at an early stage.
- When the prefetch quantity is changed.
- When you have used the REDISTRIBUTE NODEGROUP utility.
Examples of the statistics available which help define the data model to the optimizer include:
- The number of pages in a table and the number of pages that are not empty.
- The degree to which rows have been moved from their original page to other (overflow) pages.
- The number of rows in a table.
- Statistics about individual columns such as he number of distinct values in a column.
- The degree of clustering of an index; that is, the extent to which the physical sequence of rows in a table follows an index.
- Statistics about the index such as the number of index levels and the number of leaf pages in each index.
- The number of occurrences of frequently used column values.
- The distribution of column values across the range of values present in the column.
- Cost estimates for user-defined functions (UDFs).
How to update the statisticsStatistics for objects are updated in the system catalog tables only when explicitly requested. There are several ways to update some or all of the statistics:
- Using the RUNSTATS (run statistics) utility.
- Using LOAD, with statistics collection options specified.
- Coding SQL UPDATE statements that operate against a set of predefined catalog views.
- Using the "reorgchk update statistics" command.
db2 -v connect to DB_NAME db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes" db2 -v reorgchk update statistics on table all db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes" db2 -v terminate
Remember: Don't run the RUNSTATS utility until after you have populated the database.
If you know the name of the table and to avoid having large numbers of tables that may take a long time to complete, it's preferable to do RUNSTATS on each table one at a time. The command looks like the following:
db2 -v runstats on table TAB_NAME and indexes all
Checking to see if RUNSTATS has been runOne quick way to see whether RUNSTATS has been performed on your database is to query some system catalog tables. For example, as shown in the script above, you can run this command:
db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes"