Tips for collecting statistics
Use the following suggestions to improve the performance of BMCSTATS:
- Turn off delete processing by not using the DELETEAGE parameter.
- Delete statistics after physical changes, for example, if you change the index columns.
- Use the supplied ASURSDEL sample SQL to periodically clear old statistics.
- Consider reorganizing the DASD MANAGER PLUS historical database and rebinding the plans to ensure optimization of the access path.
- Consider sampling statistics instead of collecting all statistics. Sampling greatly reduces resource consumption in producing statistics. If you need to use sampling, consider specifying a percentage of pages to sample. For more information, see BMCSTATS options for collecting statistics.
If you experience locking problems when updating the Db2 catalog, consider using the BMCUPRS utility to update catalog tables during a period of low activity. For example, you can use BMCSTATS SAVESTATS Y and UPDATEDB2 N for normal operation. During a quiet time, you can update the catalog by using BMCUPRS.
You also can specify OPTIMIZECOMMIT N for BMCSTATS to increase the number of SQL COMMITS after SQL DELETE, INSERT, and UPDATE operations.
- Executing BMCSTATS against table space sets is resource intensive. You should include only table spaces that are related by referential integrity (RI).
- Consider the following items when collecting column statistics:
- Collecting column statistics can be a time-consuming process, particularly on large tables. Collect column statistics only when you have a particular need and only on the relevant columns.
- Collect statistics on all columns periodically, such as when the number of rows changes significantly.
You can stack BMCSTATS commands for batch execution. To specify different option values for the same object type, place them in a separate command, as follows:
BMCSTATS TABLESPACE options
BMCSTATS TABLESPACE options- Be aware that SQL -530 messages appear when all of the following conditions exist:
- You are collecting history statistics, but not regular DB2 statistics.
- You are collecting history statistics, but have never collected catalog statistics.
You are writing the SYSTABSTATS_HIST or SYSINDEXSTATS_HIST when no SYSTABSTATS or SYSINDEXSTATS records have ever been written for the requested table.
Under these circumstances, BMCSTATS continues to collect statistics even though the SQL -530 message appears. Only the table that caused the SQL -530 message will not be updated.