DB2 utility performance optimization

BKJIA Summary: in the previous article, we read the classification and analysis of DB2 performance problems. This time, we will focus on the Performance Optimization of DB2 utilities.

Let's take a look at how to improve the performance of backup operations:

Increase the size of the Database Configuration Parameter UTIL_HEAP_SZ, which is used to buffer backup and recovery operations.

Reduce full-database backup and use the tablespace required for tablespace backup.

Reduce full backup and use Incremental backup or DELTA backup.

Add the PARALLELISM parameter in the BACKUP command to increase the backup concurrency (adding threads or processes ).

Add the BUFFER parameter value in the BACKUP command.

To add the target directory for backup, it is best to place multiple directories on different hard disks to increase the parallel degree of backup.

Let's take a look at how to improve the recovery performance:

As with the backup operation, you need to increase the size of the Database Configuration Parameter UTIL_HEAP_SZ.

Increase the BUFFERS parameter value in the recovery command.

Add the PARALLELISM parameter in the recovery command to increase the backup concurrency (adding threads or processes ).

Containers distributed on different hard disks can also accelerate recovery (improve concurrency ).

The SMP mode is used to activate multiple proxies to increase the Restoration Operation concurrency.

Improve the import performance:

The import operation is similar to the insert operation. Therefore, you can refer to the optimization steps of insert for many methods.

You can add the compound = x option to batch import operations to reduce network traffic.

Increasing the COMMITCOUNT value has reduced the number of log I/O times.

Enable buffer INSERT and re-bind the db2uimpm package to the database using the insert buf option. Run the command before import: db2 bind db2uimpm. bnd insert buf

Improve the performance of the export operation (export:

Export operations are similar to select operations. Therefore, you can refer to select optimization steps for many methods.

Place the files exported from the export operation on a hard disk that is different from the data and logs to reduce I/O competition.

Improve the load performance: the write operation of logs in the load operation is less than the import operation, so the load performance is much better than the import operation, next we will look at how to better improve the load performance.

In a multi-partition environment, db2 load performs parallel loading, greatly improving the performance.

Adding the buffer parameter can increase the cache space during the loading process and improve the performance.