Clean up stale database statistics before migrating to Db2 12
Paul_McWilliams 110000JT36 Comment (1) Visits (6302)
In Db2 12 we built on the prof
However, before migrating to Db2 12, you should prepare by making sure that you have a simple and clean base of statistics in the catalog. This preparation can be done in Db2 10 or Db2 11. When Db2 creates statistics profiles, it inherits the existing statistics already in the catalog. If you have old statistics, Db2 can suddenly start collecting those, even if they are unneeded and were only collected long ago. We've written before about problems that old and stale database statistics can cause for the query optimization process, and it's always been good practice to clean them up. It becomes even more important when you are likely to incur the added costs of standardizing their collection.
Here's an example: assume that for your table T1 the SYSCOLDIST catalog table contains some old HISTOGRAM statistics on columns C3 and also on C5, and some old COLGROUP statistics on the combined columns C2 and C4. These are old statistics you are not routinely collecting today. Add to this that you regularly run the following job to collect statistics:
RUNSTATS TABLE(T1) INDEX(ALL)
If the Db2 optimizer also recommends that you collect FREQVAL statistics on C1, then when Db2 creates a new statistics profile, it will include the following options:
If you collect statistics with USE PROFILE, you begin collecting all of these statistics, including those that you have not routinely collected recently, and your collection costs will increase. Plus, after you first collect statistics with this profile, all of the statistics now have the same STATSTIME values, and you cannot differentiate the old ones that you were not regularly collecting before.
We suggest that you use the following SQL query to identify rows in the SYSCOLDIST catalog table for multi-column and histogram statistics that are older than the most recent statistics collection, and-single column frequency statistics that are more than a year old:
SELECT TYPE, NUMCOLUMNS, TBOWNER, TBNAME,
You can then use DELETE statements with appropriate WHERE clauses to remove the unneeded statistics from SYSCOLDIST. You might want to first insert the records that you plan to delete into a separate table, in case you encounter access path problems after the DELETE, and you need to restore them.
An alternative is to use RUNS
Read more from Terry about statistics in Db2:
Terry Purcell is the lead designer for the DB2 for z/OS Optimizer at IBM's Silicon Valley Lab and is recognized worldwide for his expertise in query optimization and performance with DB2 for z/OS.