DB2 V9 Automatic Maintenance, part 3rd: Enable automatic table and index reorganization

Enable automatic table and index reassembly

DB2 V9 can be automatically reorganized on tables and so on. It is critical that you have well-organized table data for efficient data access and optimal workload performance. After many changes have been made to the table data, logically contiguous data may reside on discontinuous physical data pages, especially when many inserts create overflow records. When you organize your data in this way, the database manager must perform additional read operations to access the sequential data. Also, after you delete a large number of rows, you need to perform additional read operations. Table reorganization Operations defragment data fragments to reduce wasted space and reorder rows to merge overflow records, speeding data access and ultimately improving query performance. You can also specify that the data be reordered according to a specific index so that the query can access the data through a minimum of data read operations. You can either reorganize the system catalog tables or reorganize the database tables. The statistics collected by RUNSTATS, together with other information, show the distribution of data in the table. In particular, by analyzing these statistics, you can know when to perform which type of reorganization. Automatic reorganization uses the REORGCHK formula to determine when a table needs to be reorganized. It periodically evaluates the tables that have updated the statistics to see if they need to be reorganized. If a reorganization is required, it will perform a traditional reorganization of the table in the internal dispatch. This will require application functionality to be performed without write access to the table being reorganized. You can use the auto_reorg, Auto_tbl_maint, and auto_maint database configuration parameters to enable or disable automatic reorganization of the feature parts. In a partitioned database environment, it is done on the directory partition to determine that automatic reorganization is performed and automatic reorganization is initiated. You only need to enable database configuration parameters on the directory partition. The reorganization will be run on all the database partitions on which the target table is located. If you are unsure when and how to reorganize tables and indexes, you can use automatic reassembly as part of your overall database maintenance scenario.

The statistics profile is provided by the RUNSTATS utility with an option to register and use, which is a set of options that specify the statistics to be collected for a particular table, such as table statistics, index statistics, or distribution statistics. This feature simplifies the collection of statistics, allowing you to store some of the options you specify when you emit the RUNSTATS command, so that you can repeatedly collect the same statistics on the table without having to re-enter the command options. The statistics profile can be registered or updated, regardless of whether statistics are actually being collected. For example, to register the profile and collect statistics at the same time, you can issue a RUNSTATS command with the SET profile option. To register only the profile without actually collecting statistics, you can issue a RUNSTATS command with the Set profile only option. To use the registered statistics profile to collect statistics, issue the RUNSTATS command and specify only the table name and the use-profile option.

The statistical information profile can also be automatically generated by DB2 automatic statistical information profiling. When this feature is enabled, information about the activity of the database is collected and stored in the query feedback warehouse. You can generate a statistical profile based on this data. Enabling this feature mitigates the uncertainty associated with a particular workload and can collect minimal statistical information sets to provide optimal database workload performance. This feature can be used in conjunction with the automatic collection of statistics, which automatically arranges the maintenance of statistics based on information contained in the automatically generated statistics profile. To enable this feature, automatic maintenance of the table is enabled by setting the appropriate configuration parameters. The auto_stats_prof configuration parameter activates the collection of query feedback data, and the AOTO_PROF_UPD configuration parameter activates the build statistics profile for automatic collection of statistical information.

It is important to note that the build statistics profile is not suitable for an OLTP environment and is used in an environment that is more appropriate for OLAP (large and complex queries that run with many predicates, which are often associated with data in predicate columns, and where several tables are connected and grouped). In addition, automatic generation of statistics profiles can only be activated in DB2 serial mode, which is disabled for queries in federated environments, multiple-partition MPP environments, and environments in which the partition is enabled for parallelism.