SMP as Related to Index Building

Resolving The Problem

How many parallel database tasks will be used to build or rebuild an index?

Indexes associated with a reorganized physical file (RGZPFM) and those delayed maintenance indexes where the delayed maintenance log grows greater than 25% of the index associated with it must be rebuilt.

The number of parallel database tasks that will be used by the OS when building an index in a given job running on system with the SMP OS option installed is determined by multiplying the number of virtual processors by 2 if the value of QQRYDEGREE is *MAX or *OPTIMIZE. If the value is *NONE or *IO then only 1 task is used. It does not matter the number of actual processors. So, if we have an LPAR with 0.7 actual processors that is configured as a 4-way (4 virtual processors) then we would have 8 DBL3BASE* jobs employed by the job performing the index build.

So why would a customer not see some benefit when building an index on an LPAR with less than a full processor with multiple virtual processors configured? It could be that they are pushing the 0.7 processor to the maximum and would be CPU constrained or where the disk drives are slow and would probably be I/O bound. The customer will have to look and see if they have either of these conditions by watching the CPU utilization and/or the I/O activity.

There is a much better chance of seeing a benefit using SMP when an LPAR uses multiple actual processors with the recommended number of virtual processors. For QDBSRVnn jobs, when the 'number of processors' is checked during IPL, it is the number of virtual processors that are checked. With SI46082 at R710, users can specify a different number of QDBSRVxx tasks. For details on how to do this, see APAR SE51094

One should also note that while it is possible to dynamically increase and decrease the processing capacity and number of virtual processors in a partition without an IPL, the operating system only starts DBL3* system tasks during an IPL. So, while the partition resources can be dynamically changed, it may be necessary to IPL the partition with the extra resources for SMP to take full advantage of them.

Additional Keywords: Access Path