Processing DB2 for iseries index in parallel mode

Brief introduction

When people consider parallel processing in the database engine, they immediately think of an improved query response time by using multiple processors to process a single query. It is often not noticed that parallel processing can also speed up index processing, thereby improving the overall performance of the database server. Using the Ibm®db2®universal database™ (UDB) for iseries™, it is possible to use multiple CPUs for index processing. In particular, DB2 UDB for ISeries can use multiple processors when creating indexes and maintaining indexes due to underlying data changes. The ability to create and maintain indexes in parallel can be applied to traditional binary cardinality and coded vector indexing structures.

DB2 UDB for iSeries parallel processing can only be used on iSeries servers, which have purchased, installed, and activated DB2 symmetric multi-processing (DB2 symmetric multiprocessing,db2 SMP) concession features, such as enabling parallel processing described.

Parallel index Creation

DB2 UDB for iSeries can be used to create SQL indexes and logical files identified with key values (that is, CRTLF commands). Parallel index processing is accomplished by logically dividing the base table into multiple data segments, followed by each process building an index key value for the specified table segment. Then, the tasks performed by each parallel process are merged together to complete the final index structure.

As you can see in Figure 1, using additional CPU resources is a good way to reduce the time it takes to create an index. This capability is important in a very large database environment or in a recovery scenario where indexes need to be recreated as quickly as possible.

Figure 1. Enabling parallelism greatly improves index build time

Parallel index maintenance

The task of index maintenance is to change the index key to reflect changes to the related database rows (usually by INSERT, update, or delete). If you change the customer's order number or if a new customer order is added to the database, you must update all indexes that contain the order number column as the key field. The maintenance (or update) of an index affects the entire transaction time associated with adding or changing orders.

If database row changes affect multiple indexes, maintenance of these indexes will occur serially, by default, an index is maintained at a time. First, the order number changes are propagated to Indexa. When the changes to Indexa are complete, the order number is propagated to INDEXB, and so on.

Parallel index maintenance involves maintaining multiple indexes at the same time. Customer order number changes can be propagated to both Indexa and Indexb at the same time. This concurrent index maintenance process reduces the total time spent changing order numbers throughout the database. Parallel index maintenance uses resources to exchange time, increasing the I/O speed of a given application or transaction.

Figure 2 reflects the time savings that can be achieved by maintaining multiple indexes in parallel. In Figure 2, a new row is mounted in a table with multiple indexes, and parallel index maintenance shortens the loading time to two to One-third.

Figure 2. When the index maintenance parallel occurs the fashion time gets improved

DB2 UDB for iSeries uses parallel index maintenance only if the application is in the following situations:

Executing block inserts (or writes) for 8 or more rows.

Two or more than two indexes are defined on the related table.

Block inserts can generally be found during batch update and data warehouse loading, so parallel index maintenance will produce the most significant performance effects in these environments.

Tip: If a load process is added to a table with more than 20% new rows, it is usually recommended that you delete the index before loading, and then use parallel processing to refactor the index after the loading process completes.

Enable parallel processing

After installing the DB2 SMP feature, there are several different ways to activate parallel processing so that parallel index construction and maintenance can be performed.