Traditionally it was common to collect statistics from time to time to ensure the DB2 optimizer has the most current statistics required to choose the best access path for better performance. However, collecting statistics blindly may be expensive if the data has not changed dramatically or has not grown. Real Time Statistics can be used to identify the rate of changes since the last RUNSTATS. In addition, with Real Time Statistics you may no longer relay on catalog statistics to identify which objects require REORG and you can afford to collect statistics only for the usage of the DB2 optimizer.
TUC’s autonomic statistics features allows you to easily implement the functionality of DB2 version 10 autonomic statistics. DB2 version 10 provides stored procedures designed to identify objects that require statistics collection and determine how the statistics should be collected. TUC allows you to define the monitors options affecting the way objects are examined and statistics are collected.
TUC allows you to display and define the autonomic periods. The dialog allow you to display the information from the SYSIBM.SYSAUTOTIMEWINDOWS catalog table and define new entries. The PERIODS panel also displays the current active period.
TUC allows you to define the monitoring options you want to use with each call to the ADMIN_UTL_MONITOR stored procedure. Once defined, you can use the defined monitor options to call the stored procedure immediately or schedule a task using the DB2 administrative scheduler. The definition also includes the objects you want to monitor. You can monitor objects by policy coverage, by profile or by predefined conditions. TUC allows you to display the execution history and alerts created by each execution. You can see the results of each execution and browse the output.
The ADMIN_UTL_MONITOR also sets the RUNSTATS statistics options profile. TUC allows you to edit these options as stored in the SYSIBM.SYSTABLES_PROFILES catalog table. The RUNSTATS utility USE PROFILE option allows you to execute RUNSTATS for multiple tables when each table uses different defined options. TUC allows to define the RUNSTATS profile for each table. The dialog allows you to edit the different RUNSTATS options, such as COLGROUP, in an edit session.
This functionality is also available prior to DB2 version 10 since TUC preserves the RUNSTATS profile also in the TUC control database. Since the USE PROFILE option is not available prior to version 10, the RUNSTATS statements can be prepared for each table with the defined statistics options. Once you migrate to DB2 version 10, the options you have defined for each table will be automatically propagated into the DB2 catalog to allow you to immediately take advantage of the new DB2 version 10 feature.
RUNSTATS by Growth
LOAD RESUME does not update the Real Time Statistics incremental counters and might cause difficulties in triggering RUNSTATS when the volume of data has grown significantly. TUC can collect growth statistics and allow you to trigger statistics collections based on growth, including tables populated by LOAD RESUME.
Packages must be rebound to allow the DB2 optimizer to reselect an access path based on the most recent statistics. Often administrators are concerned about performance degradation due to bad selection of access path caused by poor statistics. DB2 programs access multiple tables and therefore it is important that we have good statistics for all referenced tables. One way to ensure we have good statistics is to rebind packages following REORG collecting inline statistics.
Rebinding packages might fail because of bind errors. Capturing these errors and allowing the job step to complete normally, allows better operability of utility jobs including rebind steps. The errors can be stored in a repository of errors that allows later examination or automatic handling. The list of packages may include obsolete packages that fail to rebind because tables were altered or dropped. These packages can be dropped.