Maintaining Db2 database statistics

You can improve the ability of Db2 to choose efficient access paths by collecting database statistics routinely to keep the statistics accurate, up to date, and in sync for related objects.

Use the following approaches to maintain database statistics:

• Collect statistics at least once against each table and all of its associated indexes.
  You might not need to collect statistics again for objects that are stable in size and contain data values that change infrequently.
• Whenever you collect statistics for a table or index, always collect statistics for the entire table space and all associated indexes. If you collect statistics for a single partition, collect statistics at the table space level and index statistics for that partition.
  The goal is to maintain consistent statistics for related objects so that Db2 has consistent information for access path selection. When the statistics for related objects are out of sync, Db2 cannot discern between the accurate and inaccurate statistics, and suboptimal access paths can result.
  
• Collect statistics routinely for tables that have characteristics that vary over time.
  For example, you might prefer any of several common approaches to routine statistics collection:
  
  • Collect statistics on a routine schedule or based on data volume growth, regardless of when data is reorganized.
  • Collect statistics only after you reorganize data the table space level. The goal of this approach is to maintain perfect cluster ratio statistics. If you follow this approach, do not collect statistics on a single index after you reorganize it. Instead, wait until the next table space level reorganization.
  • Use DSNACCOX stored procedure to get recommendations for when to collect statistics.
  • Collect statistics only when data or indexes change significantly, such as in the following situations:
    • When you load data into a table, and before you bind application plans and packages that access the table. You can collect statistics inline with the LOAD utility.
    • After you create an index. Before a statically bound application can use a new index, you must rebind the application plan or package. Dynamic SQL statements can take advantage of a new index at the next prepare for each statement.
    • After certain ALTER statements, including any that requires you to drop and re-create an object, ALTER TABLE DROP COLUMN, ALTER TABLE ROTATE PARTITION, or ALTER TABLE ALTER COLUMN that changes the data type, length (except for VARCHAR columns), precision, scale, or subtype of the column.
    • After utilities such as RECOVER or REBUILD, and especially point-in-time recovery.
    • After a REORG utility operation that resolves pending definition changes from certain types of ALTER statements.
    • After heavy insert, update, and delete activity.
  
• Tables that constantly change size present difficulties for the collection of useful statistics.
  Such tables might not warrant frequent statistics collection because of the difficulty of collecting statistics at a representative time. You can use the following approaches for such tables:
  • Define tables as VOLATILE, or specify a value for the NPGTHRSH subsystem parameter, to favor index access whenever possible. If statistics indicate that such tables are empty during bind or prepare, Db2 can sometimes use real-time statistics to find size information for such tables. However, real-time statistics do not contain cardinality information and they cannot replace the collection of complete statistics in all cases.
  • Attempt to collect statistics when the table contains some representative data, regardless of whether you can determine exact peak amount.
• Collect statistics periodically for Db2 catalog objects to provide Db2 with more accurate information for access path selection for user queries to the catalog.
• Use care when you use SQL statements or tools to update statistics.
  If such updates introduce invalid data, they can cause unpredictable results, including abends for RUNSTATS and other utilities. If such problems occur, you can run the RUNSTATS utility and collect statistics at the table space level to resolve the problems, in most cases.