DB2 UDB for iSeries indexes can be processed in parallel

This article mainly tells you about the actual operations that DB2 UDB for iSeries indexes can process in parallel, the following describes the actual operations that DB2 UDB for iSeries indexes can process in parallel. We hope this will help you in your future studies.

When people consider parallel processing in the database engine, they immediately think of Improving the query response time by using multiple processors to process a single query. People often do not notice that parallel processing can also accelerate index processing to improve the overall performance of database servers.

Introduction

When people consider parallel processing in the database engine, they immediately think of Improving the query response time by using multiple processors to process a single query. People often do not notice that parallel processing can also accelerate index processing to improve the overall performance of database servers. Use IBM®DB2®Universal Database™(UDB) for iSeries™Multiple CPUs may be used for index processing. In particular, DB2 UDB for iSeries can use multiple processors to create indexes and maintain indexes due to underlying data changes. The ability to create and maintain indexes in parallel can be applied to traditional binary bases and encoded vector index structures.

DB2 UDB for iSeries parallel processing can only be used on the iSeries server. The DB2 Symmetric Multi-processing (DB2 distributed Ric MultiProcessing, DB2 SMP) feature has been purchased, installed, and activated, as described in enabling parallel processing.

Concurrent index creation

DB2 UDB for iSeries allows parallel processing of logical files (CRTLF commands) used to create SQL indexes and identify them with key values ). Parallel Indexing is done in the following way: logically, the basic table is divided into multiple data segments, and each process creates an index key value for the specified table segment. Then, the tasks executed by each parallel process are merged to complete the final index structure.

As you can see in Figure 1, using additional CPU resources can greatly reduce the time it takes to create an index. This capability is very important in a very large database environment or in a recovery solution that requires index re-creation as soon as possible.

Figure 1. Enabling parallelism greatly improves index building time

Concurrent index Maintenance

The index maintenance task is to change the index key to reflect changes to the relevant database rows (usually caused by insert, update, or delete ). If you have changed the customer's order number or added a new customer order to the database, you must update all indexes containing the order number column as the key field. The maintenance (or update) of indexes affects the entire transaction time related to adding or changing orders.

If the database row changes affect multiple indexes, maintenance of these indexes will occur serially. By default, one index is maintained each time. First, the order number change is propagated to IndexA. After the IndexA is changed, the order number is propagated to IndexB, and so on.

Concurrent index maintenance involves maintaining multiple indexes at the same time. The customer's order number change can be propagated to both IndexA and IndexB. This concurrent index maintenance reduces the total time it takes to change the order number throughout the database. Parallel Index maintenance exchanges resources for time, improving the I/O speed of a given application or transaction.

Figure 2 shows how much time can be saved by maintaining multiple indexes in parallel. In Figure 2, a new row is loaded into a table with Multiple indexes. Parallel Index maintenance shortens the loading time to 2 to 1/3.

Figure 2. When index maintenance occurs in parallel, the import time is improved.

DB2 UDB for iSeries uses Parallel Index maintenance only when the application is in the following conditions:

Eight or more rows of multipart insert (or write) are being executed ).

The related table defines two or more indexes.

Generally, block insertion can be found during batch update and data warehouse loading. Therefore, Parallel Index maintenance will produce the most significant performance in these environments.

Tip: If the loading process adds more new rows to the table than 20% of the number of rows in the table, we recommend that you delete the index before loading, and then use parallel processing to reconstruct the index after the loading process is complete.

Enable Parallel Processing

After installing the DB2 SMP feature, there are several different ways to activate parallel processing, so that you can perform Parallel Index building and maintenance.

At the system level

By default, the parallel processing capacity is controlled at the system level by using the QQRYDEGREE system value. If the system value is set to a value other than * NONE, DB2 UDB uses parallel processing during maintenance and indexing. The CHGSYSVAL command can be used to change the settings of the QQRYDEGREE system value. You can specify the following values to enable Parallel Indexing:

Figure 2 shows how much time can be saved by maintaining multiple indexes in parallel. In Figure 2, a new row is loaded into a table with Multiple indexes. Parallel Index maintenance shortens the loading time to 2 to 1/3.

Figure 2. When index maintenance occurs in parallel, the import time is improved.

 

DB2 UDB for iSeries uses Parallel Index maintenance only when the application is in the following conditions:

Eight or more rows of multipart insert (or write) are being executed ).

The related table defines two or more indexes.

Generally, block insertion can be found during batch update and data warehouse loading. Therefore, Parallel Index maintenance will produce the most significant performance in these environments.

Tip: If the loading process adds more new rows to the table than 20% of the number of rows in the table, we recommend that you delete the index before loading, and then use parallel processing to reconstruct the index after the loading process is complete.

Enable Parallel Processing

After installing the DB2 SMP feature, there are several different ways to activate parallel processing, so that you can perform Parallel Index building and maintenance.

At the system level

By default, the parallel processing capacity is controlled at the system level by using the QQRYDEGREE system value. If the system value is set to a value other than * NONE, DB2 UDB uses parallel processing during maintenance and indexing. The CHGSYSVAL command can be used to change the settings of the QQRYDEGREE system value. You can specify the following values to enable Parallel Indexing: