DB2 V9 Automatic maintenance, part 1th

Introduction and creating a database The default enable automatic collection of statistics features

IBM's DB2 V9 introduces automatic maintenance, automatic database backup, automatic statistics update, and reorganization of tables and indexes when necessary. DB2 V9 has an automated backup feature that provides users with a solution that helps users ensure that their databases are regularly backed up without worrying about when to back up, or having any knowledge about backup commands. In addition, DB2 V9 's automatic maintenance features include data access optimization (run statistics), Data defragmentation (table or index reorganization), and statistical profiling, which, based on these new features, greatly simplifies the amount of work we do in the actual maintenance of the database. Since automatic maintenance is a new feature of DB2 V9 and many customers don't know how to use it, this article will focus on the new features of DB2 V9 on automatic maintenance and help you understand and improve it with practical examples.

Brief introduction

IBM's DB2 V9 introduces automatic maintenance, automatic database backup, automatic statistics update, and reorganization of tables and indexes when necessary. It can be quite time-consuming for users to determine whether and when to run maintenance activities. The automatic maintenance function relieves the user of the burden. With the automatic maintenance feature, you can specify maintenance objectives, including the uptime of the automatic maintenance feature. DB2 uses the target you specify to determine whether maintenance activity is required, and the next available Maintenance window, which is defined by the user, will run only the necessary maintenance activities.

We used to have three ways to back up our databases, the first is to use the DB2 Dispatch center to schedule backup as a task, the second is to write the backup command into a Shell script, put it into the UNIX CRONTAB for scheduling automatic operation, the third is based on Third-party software management, such as using Tivo Li Storage Management (TSM) for backup management. Now that DB2 V9 has an automated backup feature, automated database backup provides a solution for users to help ensure that their databases are regularly backed up without worrying about when to back up and not having any knowledge of the backup commands. In the past, it's always been a DBA. It is recommended that you run RUNSTATS after reorganizing a table, for example, by recommending a RUNSTATS of a table that is frequently updated, inserted, or deleted. Now that DB2 V9 has introduced data access optimizations (run statistics), DB2 updates the system catalog statistics about table data, table index data, or table data and its indexed data. The optimizer uses these statistics to determine the path used to access the data. The automatic collection of statistics tries to improve the performance of the database by maintaining the latest table statistics. The goal is to allow the optimizer to select access scenarios based on accurate statistics. In addition, DB2 V9 provides features such as data defragmentation (table or index reorganization) and statistical information profiling. Data defragmentation (table or index reorganization) improves the efficiency of DB2 Database Manager access to tables. Automated reorganization functions manage offline table and index restructurings, and users don't have to worry about when and how to reorganize their data. The Automated Statistics profiling feature advises when and how to collect table statistics by detecting outdated, missing, and incorrectly specified statistics, and by generating statistical profiles based on query feedback information.

The Automatic maintenance feature is enabled by using the automatic maintenance database configuration parameters. A set of layered switches are used to easily and flexibly manage the activation of these functional parts. You can use the Configure Automatic Maintenance Wizard to make database maintenance activities run automatically only when they are needed. The DB2 Database Manager uses the targets that have been specified using the Configure Automatic Maintenance Wizard to determine whether maintenance activities need to be performed. Then, in the next available Maintenance window, DB2 only runs the necessary maintenance activities. The Maintenance window is the running time period for the automatic maintenance activity that you define.

Since automatic maintenance is a new feature of DB2 V9, many customers do not know how to use it, this article focuses on the new features of DB2 V9 on automatic maintenance, and the actual examples to help you understand and improve.

We will introduce automatic self tuning memory in the following order:

Default-enabled automatic collection statistics feature during database creation

Enable automatic backup feature

Enable automatic table and index reassembly