DB2 9 correct operations on Distributed Management

The following articles mainly describe the Distributed Management of DB2 9. In this column, we will explain the actual operation steps for performing automatic maintenance, it also describes how to use the utility to throttle if maintenance operations need to be performed when the DB2 database is processing the workload.

Preface

The adaptive utility of DB2 9's 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 of offline tables and indexes in the Distributed Management of DB2 9, 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.

The adaptive utility throttling system was introduced in DB2 8.1.2. This system enables maintenance utilities to run during a period of high workload while limiting their impact on the system to an acceptable range. This throttling system also enables the utility to automatically obtain more computing resources during off-peak hours, while reducing resource usage during peak hours. In DB2 9.5, the following maintenance operations can take advantage of the adaptive utility throttling system:

Statistical data collection

Backup operation

Data rescheduling

Asynchronous index cleanup

You may have used the utility for throttling, but are not aware of it. By default, DB2 automatically throws some maintenance utilities that are automatically called, such as automatic RUNSTATS.

Setting and influencing policies

An influence policy must be established to control the utility's throttling. The impact policy refers to the instance range restriction. The influence of all the throttle utilities on the accumulation of productive workloads cannot exceed this restriction. After such a policy is established, the system is responsible for ensuring the implementation of this policy.

For all the utilities that enable throttling in an instance, use DB2 Database Manager to configure the util_impact_lim parameter to control their impact policies. (This parameter is dynamic, so you can modify it without stopping and restarting the instance. You can even set it when the throttling-enabled utility is running .) To define an impact policy for all the throttling utilities, you only need to specify a value between 1 and 100 for the configuration parameter util_impact_lim.

For example, if you want to set the impact of the instance range to 10% (in other words, make sure that the performance impact of all the throttling utilities on the system workload does not exceed 10% ), run the following update database manager Command to set the configuration parameter util_impact_lim to 10:

 
 

  
  
UPDATE DATABASE MANAGER USING UTIL_IMPACT_LIM 10 
 
 

As you might expect, compared with a non-Throttling utility, a throttling utility generally takes longer to complete. If the running time of a utility is too long, you can increase the value of util_impact_lim, or set the configuration parameter util_impact_lim to 100 to completely disable throttling. (If util_impact_lim is set to 100, the utility will not be throttled. In this case, utilities can run as quickly as possible, but they are likely to have a serious impact on workload performance .)

Utility for throttling

Defining an impact policy does not mean that all utilities are throttled. In fact, by default, utilities run in non-Throttling mode even if an influence policy has been defined. To run a utility in throttling mode, you must enable throttling when calling the utility or enable throttling after starting the utility. Some utilities cannot enable throttling when calling, but can only enable throttling after startup, such as REBALANCE.

To enable throttling when calling a utility, you must specify the UTIL_IMPACT_PRIORITY option in the command used to execute the utility. For example, to call the Backup utility and enable throttling, run the following backup database command:

 
 

  
  
BACKUP DATABASE sample UTIL_IMPACT_PRIORITY 
 
 

Currently, only the backup database and RUNSTATS commands can identify the UTIL_IMPACT_PRIORITY clause. In addition, you must first define the impact policy (by setting the configuration parameter util_impact_lim), and then the UTIL_IMPACT_PRIORITY clause can take effect on these commands.

The UTIL_IMPACT_PRIORITY clause has an optional relative priority parameter (value between 0 and 100; 0 indicates disabled). This parameter is used to distinguish the importance of a throttling utility. Compared with a lower-priority throttling utility, a higher-priority utility consumes more resources. IBM recommends that you accept the default priority value of 50 instead of the relative priority value. (Remember that the cumulative impact of all throttling utilities is still limited by the impact policy .) The above content is an introduction to the Distributed Management of DB2 9, and I hope you will gain some benefits.