© 2003 International Business Machines Corporation. All rights reserved.

Introduction

When people think of parallel processing in a database engine, they immediately think of improved query response time by having multiple processors working on a single query. What people tend to overlook is that parallel processing can speed up index processing, too, and thus improve the overall performance of your database server. With IBM® DB2® Universal Database^TM (UDB) for iSeries^TM, it is possible to use CPUs for index processing. Specifically DB2 UDB for iSeries can use multiple processors when creating indexes and when maintaining indexes as the underlying data changes. The ability to create and maintain indexes in parallel applies to both the traditional binary radix and encoded vector index structures.

DB2 UDB for iSeries parallel processing is only available on iSeries servers where the DB2 Symmetric MultiProcessing (DB2 SMP) licensed feature has been purchased, installed, and activated, as described in Enabling parallel processing.

Back to top

Parallel index creation

DB2 UDB for iSeries can use parallel processing for the creation of both SQL indexes and keyed-logical files (that is, the CRTLF command). Parallel index processing is accomplished by logically breaking the underlying table into multiple segments of data, and then each process builds the index key values for the assigned table segment. The work performed by each parallel process is then merged together to complete the final index structure.

As you can see in Figure 1, using additional CPU resources can substantially reduce the amount of time it takes to create an index. This ability becomes very important in a very large database environment, or in a recovery scenario where recreating the indexes needs to occur as fast as possible.

Back to top

Parallel index maintenance

Index maintenance is the task of changing the index key to reflect changes (usually caused by insert, update, or delete) to the associated database row. If an order number for a customer is changed or if a new order for a customer is added to the database, all indexes that include the order number column as a key field must be updated. The maintenance (or update) of the indexes factors into the overall transaction time associated with adding or changing an order.

If more than one index isaffected by a database row change, the maintenance of those indexes occurs serially, by default, one index at a time. First, the order number change is propagated to IndexA. After the change to IndexA is completed, the order number is propagated to IndexB, and so on.

Parallel index maintenance involves maintaining multiple indexes concurrently. The order number changes for a customer can be propagated to IndexA and IndexB simultaneously. This concurrent index maintenance processing reduces the total amount of time it takes to get an order number change through the database. Parallel index maintenance trades resources for time, increasing the I/O velocity 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, new rows are being loaded into a table with multiple indexes and parallel index maintenance improves the load time by two to three times.

DB2 UDB for iSeries uses parallel index maintenance only when the application:

• Is performing blocked inserts (or writes) of eight or more rows.
• The associated table has two or more indexes defined over it.

Blocked inserts are commonly found in batch update and data warehouse load processes, so parallel index maintenance will have the biggest performance impact in these environments.

Tip: If a load process is adding more than 20% new rows to a table, it's usually recommended to drop the indexes prior to the load and then use parallel processing to rebuild the indexes after the load process has completed.

Back to top

Enabling parallel processing

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

At a system level

By default, the amount of parallel processing is controlled at a system-wide level via the QQRYDEGREE system value. If that system value is set to a value other than *NONE, DB2 UDB will use parallel processing when maintaining and building indexes. The CHGSYSVAL (Change System Value) commands can be used to change the setting of the QQRYDEGREE system value. Here are the values that you can specify to enable parallel index processing:

• *MAX - The DB2 UDB engine can use all of the CPU processors and all of the memory available to the job (connection) during parallel index maintenance and build.
• *OPTIMIZE - A "good neighbor" setting. The DB2 UDB engine will use its job share of the CPU processors and memory.
• *NBRTASKSn - This sets the maximum number of CPU processors that the DB2 UDB engine can use. This is not a valid value for the QQRYDEGREE system value.
• *NONE - This is the default value. No parallel processing is used for index processing.

At a job or connection level

If you want to restrict parallel processing to an individual job or connection, then use the CHGQRYA (Change Query Attributes) CL system command or a QAQQINI file to enable parallel processing for a job or connection. These interfaces override the system value setting.

The DEGREE parameter on the CHGQRYA CL command accepts the same values as the system value. If the following CHGQRYA command is executed in a job, then the parallel degree is set to *MAX only for that job, regardless of the system value:

CHGQRYA DEGREE(*MAX)

This command can be issued multiple times within a job to turn parallelism on and off.

The QAQQINI file also lets you specify parallel processing for an individual job or connection. Like .INI files found on your PC that are used to store the configuration settings that control the behavior (colors, window size, etc.) of PC tools and applications, the QAQQINI file can be used to influence the behavior of the database engine - in this case, the parallel processing behavior. These QAQQINI configuration settings can be saved and applied dynamically across multiple database requests.

The fact that the QAQQINI file is just a normal database table means you can dynamically change the values of different attributes as required by your environment. It's also very flexible, because normal database interfaces can be used to change the values of an QAQQINI attribute. The following SQL statement demonstrates how to use the QAQQINI file to set the parallel processing degree to *OPTIMIZE:

UPDATE MyLib.QAQQINI SET QQVAL = '*OPTIMIZE' WHERE QQPAM='PARALLEL_DEGREE'

There's no limit on the number of times that an attribute value can be changed during a job or connection. For more information on creating and using the QAQQINI file, see DB2 UDB for iSeries Database Performance and Query Optimization Guide or the following Web site: http://www.iseries.ibm.com/developer/bi/tuner/index.html

Back to top

Conclusion

This article has shown the value of the parallel processing provided by the DB2 SMP feature for non-query workloads. To enable parallel index processing, activate parallel processing via one of the interfaces described in this article before you build indexes, load data, or perform batch processing. By doing so, you can more fully utilize CPU resources on a multi-CPU server and speed up performance.

About the author

Kent Milligan is a DB2 UDB Technology Specialist in PartnerWorld® for Developers, iSeries. Kent spent the first eight years of his IBM career as a member of the DB2 development group in Rochester. He speaks and writes regularly on various iSeries and AS/400e relational database topics. You can reach him at kmill@us.ibm.com.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Parallel index creation
• Parallel index maintenance
• Enabling parallel processing
• Conclusion
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers