Tips for making DB2 database backup and recovery more efficient

The following articles mainly describe the actual operations that greatly increase the efficiency of database backup and recovery in DB2. It takes N hours to back up or restore a DB2 database, therefore, the timely backup and recovery solution is no matter whether it is safe.

Or an inappropriate solution. Therefore, while considering the security of the backup and recovery solution itself, the efficiency of backup and recovery must also be taken into account.

In this article, I will introduce some common techniques to improve the efficiency of data backup and recovery in DB2 databases. The practical application of many cases proves that these skills are of great help in improving the performance of DB2 database backup and recovery.

1. Understand the logical storage structure of data.

If you have an Oracle database administrator, it is easier to understand the logical storage structure of DB2 database backup and recovery, because the two are similar in this regard. For DB2 database backup and recovery, its logical storage structure is divided into three levels: data table, tablespace, and database. The data table is included in the tablespace, And the tablespace is included in the database. In other words, a DB2 database contains multiple tablespaces, and each tablespace contains multiple data tables.

One data entry indicates that it cannot be stored in multiple tablespaces. Similarly, a tablespace cannot be stored in multiple databases. This is similar to the implementation of primary school and class grouping. A class (equivalent to a database) is divided into four groups (each group is equivalent to a tablespace), and each group is composed of students (each student is equivalent to a data table ).

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

With such a logical structure, the database administrator can back up and restore the entire database (manage the entire class ); you can also back up and restore all data tables in the table space in units of table space (managed in groups ); you can even back up and restore individual data tables (manage individual students ). That is, backup and recovery can be performed at the data table, tablespace, and database levels.

This hierarchical management not only enhances the flexibility of operations, but also improves the efficiency of backup and operations. If you need to update a table or adjust the price in the price list. In this case, the Data Administrator only needs to update the price list without backing up the entire database.

Obviously, this can reduce the backup time. If a problem occurs, you only need to restore the content in this table. This can also greatly reduce the recovery time. It takes much less time to restore a table than to restore the entire database.

2. reasonably plan the tablespace to improve database backup and recovery performance.

Since database backup and recovery can be performed at three levels, the database administrator can reasonably plan the tablespace based on this feature to Improve the Performance of database backup and recovery. For example, in the backup and recovery of the DB2 database, large data types such as LOB are well supported. However, after all, its capacity is relatively large, and sometimes its database capacity may reach 1/2 of the total database capacity.

Therefore, these large data types have become the performance bottleneck of database backup and recovery jobs. If the LOB data type is not backed up during database DB2 database backup and recovery, the database backup and recovery time may be reduced by 1/3 or even 1/2.

Therefore, the author suggests that when planning a data table, it is best to divide the data of the LOB data type into two tables for storage. For example, a table in the product information table contains a product image field, which is a LOB data type. Although they can be designed in the same table. However, I suggest you divide them into two tables and connect them with keywords. Finally, store the two tables in different tablespaces.

If you store tables of the LOB data type in the LOB tablespace. So what are the benefits? During database backup, only data in non-LOB tablespaces can be backed up. Some information in the LOB tablespace is generally not updated, so it can be updated once a week. Or, you can back up the database in idle time.

After a period of testing, you can find that the database is idle every day or every week. With this plan, the backup efficiency can be improved. Likewise, data can be restored at the tablespace level during restoration, which can shorten the time for data backup and restoration of the DB2 database.

The core of this solution is to separate large data types such as LOB from common tables and store them in different tablespaces. Then, the LOB tablespace and the non-LOB tablespace are managed separately for separate backup and recovery to improve the efficiency of database backup and recovery. Therefore, it is critical to separate the LOB data type from other data types during table creation.

Iii. Alternative processing methods for LOB data types such as images.

When there are many LOB Data Types in the database, the database performance is often greatly affected. Not only will data DB2 database backup and recovery be affected, but even normal queries will also be affected. For this reason, I often recommend that you do not store images and other information in the database when deploying a DB2 database backup and recovery system for enterprises. What should I do when a user needs to query image information? I suggest that they put images in a shared server on the network.

Then, only the path of the image is stored in the database. At last, you can create a link to the image when designing the client interface. To access the image information of a product, you only need to click the image link on the product management interface to open the image. In this case, the database actually stores only the image path (that is, a common character field), rather than the image itself. Therefore, it can increase the display speed of the client window and improve the efficiency of database backup and recovery.

4. Set proper concurrency and cache for the system to improve database backup and recovery performance.

The performance of database backup and recovery is not only related to the data type, but also related to the degree of parallelism of the system and the buffer. Generally, increasing the degree of parallelism and the size of the buffer can improve the performance of database backup and recovery if the hardware device supports it. Therefore, when backing up and recovering a DB2 database, you can allocate appropriate values to the degree of parallelism, buffer tree, and buffer size to Improve the Performance of database backup and recovery.

For example, we recommend that you set the database buffer size to twice to three times the data memory size. If the DB2 database backup contains a large data type, you can increase the overall proportion as appropriate. Set the number of database buffers to double the number of hard disk I/O buffers. After this setting, the database backup and recovery performance can be greatly improved.

However, it should be noted that this adjustment will be limited by the hardware resources of the server. When the server memory and other hardware devices do not support such a large number of data buffers and data buffers, data backup and recovery may fail due to insufficient memory allocated to the buffer.

For this reason, I suggest that you pay special attention when adjusting the data buffer zone. Generally, after adjusting these parameters, you need to keep track of them for a period of time to ensure that these adjustments do not affect normal DB2 database backup and recovery, and do not conflict with the database hardware configuration.