Automated maintenance began with DB2 LUW 8.2. At this time the behavior of automated utilities had to be controlled manually either through the Control Center or the automation wizard. Now with DB2 9.5 automation of RUNSTATS and REORG commands can be controlled by an XML document referred to as an “automatic maintenance policy” These policies control which objects are considered for automated maintenance, and what options are to be used.
Automated Statistics Collection
The feature of automatic statistics collection allows you collect statistics on an as-needed basis. Automated statistics were introduced in DB2 8.2. This release allowed you to check statistics on a 2 hour interval. Statistics collection would then occur if needed based on the analysis. This may or may not be adequate depending on the volatility of the environment. Now with V9.5 we have real-time statistics collection through the DB2 optimizer. When a query runs through the compile/optimize process the statistics for the referenced tables are checked for accuracy, and statistics are collected synchronously as needed. The amount of time allowed for statistics collection is controlled through the optimization profile. The default is 5 seconds, which means that if the RUNSTATS is going to take more than 5 seconds it is automatically kicked off as a background asynchronous process. Automatic statistics collection is turned on by default when a database is created.
Configuration Parameters
- AUTO_RUNSTATS=ON
- AUTO_STMT_STATS=ON
- AUTO_REORG = ON
- AUTO_TBL_MAINT = ON
- AUTO_MAINT=ON
Automated RUNSTATS activity is tracked in a new log file called DB2_OPTSTATS_LOG. The file can be viewed either manually or with the SYSPROC.PD_GET_DIAG_HIST table function.
Peformance Impact
The real-time asynchronous process executes a “throttled” RUNSTATS, based on current database activity in order to minimize the performance impact.
Statistics Profiles
Statistics profiles control which statistics get collected when a RUNSTATS is executed. Statistic profiles can either be set up manually by running a RUNSTATS with the SET PROFILE option, or by the automatic statistics profiling by turning the AUTO_STATS_PROF, and AUTO_PROF_UPD configuration parameters to ON.
Automated Reorganization
The new automated reorganization feature is essentially an automated version of REORGCHK. If the REORGCHK determines that an index or table reorg is required, it will internally schedule the task.
Configuration Parameters
- AUTO_REORG = ON
- AUTO_TBL_MAINT = ON
- AUTO_MAINT=ON
The new automated maintenance features being introduced into DB2 LUW are not designed to replace the DBA, but to help alleviate these tedious tasks and let them focus on the more critical tasks at hand. See you next time …