DB2 data Backup and recovery procedures

This paper mainly describes how to improve the DB2 data backup and recovery efficiency of practical steps, in the design of data backup and recovery scenarios, we mainly consider two aspects of the problem.

The first is whether the backup and recovery strategy itself is safe, that is, the timely and accurate completion of the database backup work.

The second is to consider the implementation of data backup and recovery scheme is not high.

If it takes a few hours to back up or restore a DB2 database, it doesn't help that the backup and recovery scenario is safe and secure. Or, it's an inappropriate solution. To this end, consider the security of the backup recovery scheme itself, but also need to take into account the efficiency of backup and recovery. In this article, I will introduce some common techniques to improve the efficiency of DB2 data backup and recovery.

Through the practical application of several cases, it proves that these techniques are helpful to improve the performance of DB2 database backup and recovery.

First, understand the logical storage structure of data.

It is easier to understand the logical storage structure of a DB2 database if you have previously had an administrator with an Oracle database experience, because the two are similar in this respect. For DB2 database, its logical storage structure is divided into three levels of data table, table space and database. The data table is included in the table space, and the table space is included in the database. In other words, a DB2 database contains more than one table space, and each table space contains more than one data table.

A data representation cannot be saved in more than one table space. Similarly, a tablespace cannot be saved in multiple databases. This is similar to the implementation of primary schools, classes in groups. A class (equivalent to a database) is divided into four groups (each of which is equivalent to a tablespace), and each group is then made up of students (each student is the equivalent of a data sheet).

What are the benefits of this organizational structure? At this time the school principals (equivalent to the database administrator) can manage the data according to the class, group, or one-to-one interviews with students, thus improving the flexibility of database management.

In the case of DB2 data backup and recovery, this logical structure allows the database administrator to backup and restore the entire database (management of the entire class), or to perform backup and restore operations (in Group management) for all tables in the tablespace, in table space. You can even perform backup and recovery operations on individual data tables (managing individual students). That is, you can back up and restore operations from the data table, table space, database three levels respectively.

This level of management not only enhances operational flexibility, but also improves the efficiency of backup and operations. If you now need to update a table, you need to adjust the price in the price list. At this point, the database administrator only needs to update the price list, without having to back up the entire database. Obviously this can reduce the time of the backup. If there is a problem, you only need to restore the contents of this table. This can also greatly reduce the time to recover. Recovering a table is certainly a lot less time than recovering an entire database.

Second, reasonable planning table space to improve database backup recovery performance.

Now that database backup and recovery can be performed at three levels, database administrators can rationally plan tablespaces based on this feature to improve the performance of database backup and recovery. As in the DB2 database, there is good support for high-volume data types such as lobs.

However, after all, its capacity is larger, and sometimes its database capacity may reach one-second of the entire database. For this reason, these large-capacity data types are a performance bottleneck for database backup and recovery job jobs. If data for these LOB data types are not backed up when the database is backed up and restored, the time for database backup and recovery may be reduced by one-third or even one-second.

For this reason, it is recommended that when planning a data table, it is best to separate the data of the LOB data type from the other data into two tables. In a table in a product information table, a field that contains a product picture, which is a LOB data type. Although they can be designed in the same table. However, I recommend that they be divided into two tables to store, and then connect them by keyword.

Finally, the two tables are stored in separate table spaces. If a table with LOB data types is uniformly saved in the LOB table space. What good is that? When a database is backed up, you can usually back up only data in non-LOB tablespaces. Because some of the information in the LOB table space is not generally updated, it can be updated one weeks. or let it be backed up at a time when the database is relatively idle.

After a period of testing, you can find that the daily or weekly database is idle. If you plan to do so, you can improve the efficiency of your usual backups. Similarly, recovery can be done at the table-space level at the time of recovery, thus reducing database recovery times.

The core of this scheme is to separate the data of large-capacity data types such as lob from ordinary tables and store them in different table spaces. The LOB table space is then managed separately from the non-LOB tablespace, backed up and restored separately, improving the efficiency of database backup and recovery. It is critical to keep LOB data types separate from other data types when creating tables.

Third, for the image and other LOB data types of alternative processing methods.

When there are more LOB data types in the database, the performance of the database is often affected to a large extent. Not only will the DB2 data backup and recovery be affected, but even the usual queries will be implicated. To this end, I sometimes give enterprises to deploy DB2 database system, often recommend them, do not store pictures and other information in the database. So when users need to query the picture information, how to deal with it? The author suggests that they put the picture in a shared server on the network.

Then only the path to the image is stored in the database. Finally, when the client interface is designed, you can create a link to the picture. When the user needs to access the picture information of a product, it is only necessary to click on the image link on the Product management interface to open the image. In this case, the database actually stores only the path to the picture (that is, a normal character field), not the picture itself. It can improve the speed of client window display, but also can improve the efficiency of database backup and recovery.

To improve the performance of database backup and recovery by setting appropriate degree of parallelism and caching for the system.

The performance of database backup and recovery, in addition to data types, is also related to the degree of parallelism of the system with the buffer. In general, if the hardware device supports it, increasing the degree of parallelism and increasing the size of the buffer can improve the performance of database backup and recovery. To do this, you can improve the performance of database backup and recovery by assigning appropriate values to parallelism, buffer trees, and buffer sizes when backing up the DB2 database.

As a general rule, I recommend that you set the size of the database buffer to twice times three times times the amount of data memory. If the backup contains a large data type, you can increase the overall scale appropriately. and set the number of database buffers to twice times the number of hard disk I/O buffers. Once this is set up, the backup and recovery performance of the database can be greatly improved.

However, it is important to note that this adjustment is limited by the server hardware resources. When hardware devices such as server memory do not support such a large number of data buffers and data buffers, instead of having enough memory allocated to the buffers, the DB2 data backup and recovery failures occur. For this reason, the author suggests that special attention should be paid to adjusting the data buffers.

Multiple backups think: After adjusting these parameters, you need to keep track of the time to ensure that these adjustments do not affect the normal backup and recovery of the database and do not conflict with the hardware configuration of the database.

Www.dbfen.com

DB2 data Backup and recovery procedures