DB2.9 Distributed Management

Objective

DB2 9 's Adaptive Utility throttling system enables maintenance to take place during peak periods.

If you want to keep your car in good condition, you must maintain it regularly, such as replacing oil and spark plugs. This is also true for databases, and maintenance operations must be performed regularly, such as rearranging data layouts, updating database statistics, and reorganizing tables/indexes if you want to maintain good performance.

In DB2, these maintenance operations are performed by running a variety of utilities. Because these utilities consume valuable system resources, maintenance operations are typically performed in the Maintenance window during off-peak periods. However, today's business increasingly needs to run uninterrupted, so it becomes increasingly difficult to get the database offline to perform these important operations. Now, some maintenance tasks can be performed while the database is online, but many tasks are still consuming a lot of resources and can affect the normal functioning of the database.

IBM provides a solution to this problem by automating certain routine maintenance activities that should be performed on a regular basis. IBM developers also created a fine-grained resource control mechanism called Utility throttling (utility throttling). It allows you to select utilities that consume more resources at some time and consume less resources at other times.

In this column, I'll explain how to perform automatic maintenance and explain how to use utility throttling if you need to perform maintenance operations while the database is working on the workload.

Automatic Maintenance

Automatic maintenance is a new feature introduced in DB2 9. By using this feature, you can specify a maintenance target, DB2 Database Manager to decide whether or not to perform maintenance activities to achieve these goals. If Database Manager deems it necessary to perform a maintenance operation, this can be done automatically in the next Maintenance window, a time period specified by you, and all automatic maintenance activities are performed during this time period.

You can use automatic maintenance to perform the following tasks:

Generate a database backup image. Automated database backup solutions ensure that databases are backed up appropriately and regularly, and that users do not need to always focus on backup times or how to properly write backup commands.

Data defragmentation (table or index reorganization). This maintenance activity can improve the efficiency of DB2 Database Manager Access tables. Automated reorganization Management Offline table and index reorganization, users do not need to care about when and how to reorganize data.

Data access Optimization (run RUNSTATS). DB2 database Manager automatically updates the system catalog statistics about table data, table index data, or both. When responding to queries, DB2 Optimizer uses these statistics to determine which path to use to access data. The automated statistical data collection process maintains the latest table statistics to improve database performance. The goal is to have DB2 Optimizer always choose the access plan based on accurate information.

Statistical data analysis. The automated statistical data analysis process detects outdated, missing, or inaccurate statistical data, generates statistical analysis based on query feedback, and suggests when and how to collect table statistics.

When you create a DB2 9 database, automatic maintenance is enabled by default, and the dedicated database configuration parameters are automatically maintained (Auto_maint, Auto_db_backup, Auto_tbl_maint, Auto_runstats, Auto_stats_prof, AUTO_PROF_UPD and auto_reorg) enable the available automatic maintenance features. These parameters are a set of hierarchical switches that can be set to on or off.

Adaptive Utility Throttling

Automatic maintenance activities (backup, statistical data collection, statistical analysis, and table/index reorganization) consume system resources at run time, which can affect database performance. In addition, offline database backups and table/Index reorganization operations restrict access to tables, indexes, or the entire database. To minimize the impact on the system, you can use the DB2 Adaptive utility throttle System to adjust the resource usage of some automatic maintenance activities.

The Adaptive Utility throttle system is introduced in the DB2 8.1.2. This system enables the maintenance utility to run at a time when the workload is high, while limiting its impact on the system to an acceptable range. This throttling system also enables the utility to automatically obtain more computational resources during off-peak periods, while reducing resource usage during peak periods. In DB2 9.5, the following maintenance operations can take advantage of the adaptive utility Throttle System:

Statistical data collection

Backup operations

Data re-layout operations

Asynchronous Index Cleanup

You may have used the utility to throttle, but you just don't realize it; By default, DB2 automatically throttles some of the maintenance utilities that are automatically invoked, such as automatic RUNSTATS.