Steps required to optimize the backup performance of the DB2 database

The following articles mainly describe how to correctly optimize the backup performance of the DB2 database, the DB2 database automatically selects the optimal value for the number of buffers, buffer size, and parallelism settings. These values depend on the number of available utility heap memory, number of available processors, and database configuration.

Therefore, based on the available storage capacity on the system, you should consider allocating more memory by increasing the UTIL_HEAP_SZ configuration parameter. The goal is to minimize the time required to complete the backup operation. Unless you explicitly enter the values of the following backup database command parameters, DB2 selects a value for them:

 
 

  
  
WITH num-buffers BUFFERS  
  
  
PARALLELISM n  
  
  
BUFFER buffer-size 
 
 

If you do not specify the number of buffers and the size of the buffer cause DB2 to set these values, the impact on large databases should be the lowest. However, for small databases, the backup image will increase significantly. Even if the last data buffer to be written to the disk contains only a small amount of data, the entire buffer is written to the image. In a small database, this indicates that a considerable number of images may be empty.

You can also perform any of the following operations to shorten the time required to complete a backup operation:

Specify tablespace backup.

You can use the backup database command with the TABLESPACE option specified to back up and recover some databases. This facilitates the management of table data, indexes, long fields in individual tablespaces, or large object LOB) data.

Increase the value of the PARALLELISM parameter on the backup database command to reflect the number of tablespaces being backed up.

The PARALLELISM parameter defines the number of processes or threads started when data is read from the database and compressed during the compression backup operation. Assign each process or thread to a specific tablespace. Therefore, it is no benefit to specify a value greater than the number of tablespaces to be backed up for the PARALLELISM parameter. After optimizing the DB2 database to back up the tablespace, it will request another tablespace. However, note that each process or thread requires memory and CPU overhead.

Increase the backup buffer size.

The ideal backup buffer size is a multiple of the extended data block size of the tablespace plus one page. If there are multiple tablespaces with different sizes of extended data blocks, specify the value as a public multiple of the extended data block size and add one page.

Increase the number of buffers.

The buffer used must be at least twice that of the backup target or session to ensure that the backup target device does not have to wait for data.

Use multiple target devices.

The above content describes how to optimize the backup performance of the DB2 database. We hope to help you in this regard.