Procedure for creating and maintaining DB2 parallel Indexes

Today we will mainly talk about the creation and maintenance of DB2 parallel indexes. DB2 UDB for iSeries can use parallel processing to create SQL indexes and logical files marked with key values (that is, CRTLF command ). DB2 parallel indexing is mainly completed in the following ways:

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

DB2 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. DB2 Parallel Index maintenance uses resources in exchange 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 DB2 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.