DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
The runstats utility provides the option to register and use a statistics profile, which specifies the type of statistics that are to be collected for a particular table; for example, table statistics, index statistics, or distribution statistics. This feature simplifies statistics collection by enabling you to store runstats options for convenient future use.
To register a profile and collect statistics at the same time, issue the RUNSTATS command with the SET PROFILE option. To register a profile only, issue the RUNSTATS command with the SET PROFILE ONLY option. To collect statistics using a profile that has already been registered, issue the RUNSTATS command with the USE PROFILE option.
SELECT STATISTICS_PROFILE FROM SYSCAT.TABLES WHERE TABNAME = 'EMPLOYEE'Statistics profiles can also be generated automatically with the DB2® automatic statistics profiling feature. When this feature is enabled, information about database activity is collected and stored in the query feedback warehouse. A statistics profile is then generated on the basis of this data. Enabling this feature can alleviate the uncertainty about which statistics are relevant to a particular workload.
Automatic statistics profiling can be used with automatic statistics collection, which schedules statistics maintenance operations based on information contained in the automatically generated statistics profile.
To enable automatic statistics profiling, ensure that automatic table maintenance has already been enabled by setting the appropriate database configuration parameters. For more information, see "auto_maint - Automatic maintenance configuration parameter". The auto_stats_prof configuration parameter activates the collection of query feedback data, and the auto_prof_upd configuration parameter activates the generation of a statistics profile for use by automatic statistics collection.
Automatic statistics profile generation is not supported in partitioned database environments, in certain federated database environments, and when intra-partition parallelism is enabled. Automatic statistics profile generation cannot be enabled if section actuals are enabled on the database (SQLCODE -5153).
Automatic statistics profiling is best suited to systems running large complex queries that have many predicates, use large joins, or specify extensive grouping. It is less suited to systems with primarily transactional workloads.
In a development environment, where the performance overhead of runtime monitoring can easily be tolerated, set the auto_stats_prof and auto_prof_updconfiguration parameters to ON. When a test system uses realistic data and queries, appropriate statistics profiles can be transferred to the production system, where queries can benefit without incurring additional monitoring overhead.
In a production environment, if performance problems with a particular set of queries (problems that can be attributed to faulty statistics) are detected, you can set theauto_stats_prof configuration parameter to ON and execute the target workload for a period of time. Automatic statistics profiling will analyze the query feedback and create recommendations in the SYSTOOLS.OPT_FEEDBACK_RANKING tables. You can inspect these recommendations and refine the statistics profiles manually, as appropriate. To have the DB2 server automatically update the statistics profiles based on these recommendations, enable auto_prof_upd when you enable auto_stats_prof.
Use the SYSINSTALLOBJECTS procedure to create the query feedback warehouse. For more information about this procedure, which is used to create or drop objects in the SYSTOOLS schema, see "SYSINSTALLOBJECTS".