DB2 9 Distributed Management

DB2 9's adaptive utility throttling system enables maintenance during busy hours. If you want to keep your car in good state, you must perform regular maintenance, such as oil change and spark plugs. This is also true for databases. if you want to maintain good performance, you must perform regular maintenance operations, such as rescheduling data la S, updating database statistics, and reorganizing tables/indexes.

In DB2, these maintenance operations are performed by running various utilities. Because these utilities consume valuable system resources, maintenance operations are usually performed in the "maintenance window" during off-peak hours. However, today's business needs to run continuously, so it is increasingly difficult to make the database offline to execute these important operations. Currently, some maintenance tasks can be executed when the database is online, but many tasks consume a lot of resources, which will affect the normal operation of the database.

IBM provides a solution for this problem to automate routine maintenance activities that should be performed on a regular basis. IBM developers have also created a fine-grained resource control mechanism called 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 will explain how to perform automatic maintenance and how to use the utility to throttle costs if you need to perform maintenance operations when the database is processing workloads.

Maintenance

Automatic maintenance is a new feature introduced in DB2 9. By using this feature, you can specify maintenance targets. DB2 Database Manager determines whether maintenance activities are required to achieve these goals. If Database Manager deems it necessary to perform maintenance operations, it can automatically perform this operation in the next maintenance time window. The maintenance time window is a time period specified by you, all automatic maintenance activities are executed during this period.

You can use automatic maintenance to execute the following tasks:

Generate a database backup image. The automatic database BACKUP solution ensures that you do not always pay attention to the BACKUP time or write the BACKUP command correctly.

Data fragmentation (table or index reorganization ). This maintenance activity can improve the efficiency of accessing tables in DB2 Database Manager. Automatic reorganization manages offline tables and index reorganization. users do not need to worry about when and how to reorganize data.

Optimize Data Access (run RUNSTATS ). DB2 Database Manager automatically updates system catalog statistics about table data, table index data, or both. In response to queries, DB2 Optimizer uses these statistics to determine which path to use to access data. The automatic statistical data collection process maintains the latest Table statistics to improve database performance. The goal is to make DB2 Optimizer always select an access plan based on accurate information.

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

Automatic maintenance is enabled by default when you create a DB2 9 database. The dedicated database configuration parameters (auto_maint, auto_db_backup, auto_tbl_maint, auto_runstats, auto_stats_prof, auto_prof_upd, and auto_reorg) are automatically maintained) enable available automatic maintenance features. These parameters are hierarchical switches and can be set to ON or OFF.

Program-based throttling

Automatic maintenance activities (backup, statistical data collection, statistical data analysis, and table/index reorganization) consume system resources during operation, thus affecting database performance. In addition, offline database backup and table/index reorganization restrict access to tables, indexes, or the entire database. To minimize the impact on the system, you can use the DB2 adaptive utility throttling system to adjust the resource usage for certain automatic maintenance activities.