Problem solve Get help with specific problems with your technologies, process and projects.

Speed up the access path rebuild process

Member Murtuza Jadli provides a couple methods to speed up the access path rebuild process.

If the file is a uniquely keyed physical file (or SQL table), there is little or nothing you can do to speed up the access path rebuild process. However, if the physical or logical file is not uniquely keyed, you may be able to use the Maint parameter of the CrtPF (Create Physical File), CrtLF (Create Logical File), ChgPF (Change Physical File), and ChgLF (Change Logical File) commands to gain an advantage with respect to access path rebuild.

Specifying the default parameter Maint(*Immed) will rebuild the access path immediately, which may slow your ability to proceed with the use of that physical file or any of its logical files. However, you can specify Maint(*ReBld) to rebuild the access path completely when the file is opened. This might be an effective option if you seldom use a file (e.g., once per month). In this case, you might schedule the rebuild of the access path during off-hours. For example, you could schedule the rebuild during the evening processing the night before users would need the file. Note that you can simulate this situation with a logical file or by removing the member via the RmvM (Remove Member) command. Doing so rebuilds the access path when you add the member back via the AddLFM (Add Logical File Member) command. You can use this technique with logical files, even if they're uniquely keyed. However, I don't recommend removing the member of a uniquely keyed logical file because the system will no longer check for uniqueness until you add the member back to the file.

Another option to consider with non-uniquely keyed files is to use Maint(*Dly). Using this option changes each DB2 record, which affects the access path (e.g., record insert, delete, update) in a system data space but doesn't rebuild the access path until the file is opened. When the file is opened, instead of completely rebuilding the access path, as with Maint(*ReBld), Maint(*Dly) uses the recorded changes to apply each modification to the access path. If you have few changes affecting the access path, this method can be much faster than completely rebuilding the access path. However, if you have numerous changes, this method can be slower than simply rebuilding the access path from scratch. The threshold where Maint(*Dly) becomes slower than Maint(*ReBld) will vary, depending on the distribution and the size of the keys. You need to test these two methods yourself to determine which one to use.


The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.

Dig Deeper on iSeries CL programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.