Access Path Maintenance (Immediate, Delay, or Rebuild)

Tailoring for Performance › Access Path Maintenance (Immediate, Delay, or Rebuild)

Access Path Maintenance (Immediate, Delay, or Rebuild)

There are three types of access path maintenance options: immediate (IMMED), delay (DLY), and rebuild (REBLD). These options determine how the operating system applies changes to the access paths. While a file is open, the system maintains the access paths as changes are made to the data in the file. However, when the file is closed, the access path maintenance option specifies to i OS how the access path should be maintained. It is important to pay close attention to access paths in the design stage.

IMMED maintenance means that the active index is maintained as changes are made to its associated data regardless of whether the file is open.
DLY maintenance means that any maintenance for the active index is done after the file member is opened and while it remains open. Updates to the access path are collected from the time the access path is closed until it is opened again. When it is opened, only the collected changes are merged into the access path.
REBLD maintenance means that the active index is maintained only when the file is open, not when the file is closed; the access path is rebuilt the next time the file is opened. When the file is opened again, the access path is totally rebuilt. If one or more programs has opened a specific file member with rebuild maintenance specified, the system maintains the access path for that member until the last user closes the file member.

The use of maintenance options applies only to RSQ and SPN access paths. The UPD and RTV access paths are defined with a UNIQUE key; as a result, maintenance has to be IMMED. For QRY access paths, maintenance does not apply because the access path is rebuilt at execution.

There are considerations with each type of maintenance option. When you change a file, indexes with IMMED maintenance are updated. However, when programs that open DLY and REBLD maintenance access paths are invoked, changes are applied that then make the programs slower to load.

Use DLY maintenance with caution. If more than approximately 10% of the number of entries in the access path are changed, the whole index will be rebuilt at the next open. This rebuild could occur during the use of a program that does not use that index.

If the file records are non-volatile, you can always take the default IMMED maintenance. If the data is likely to change for infrequently used access paths, you may use DLY or REBLD maintenance. However, keep in mind that for each different type of maintenance, you will get a separate active index. Specify a REBLD access path if you already have an IMMED maintenance access path with the same set of keys and select/omit sets.

The following is a comparison of IMMED, DLY, and REBLD maintenance as they affect opening and processing files:

Immediate	Delay	Rebuild
Fast open because the access path is current.	Moderately fast open because the access path does not have to be rebuilt, but it must still be changed. Slow open if extensive changes are needed.	Slow open because access path must be rebuilt.
Slower update/output operations when many access paths with immediate maintenance are built over changing data (the system must maintain the access paths).	Moderately fast update/output operations when many access paths with delayed maintenance are built over changing data and are not open (the system records the changes, but the access path itself is not maintained).	Faster update/output operations when many access paths with rebuild maintenance are built over changing data and are not open (the system does not have to maintain the access paths).

For more information:

On the access path maintenance options, see Editing Access Path Details in the Modifying Access Paths" chapter and Identifying the Implementation Attributes in the "Generating and Compiling" chapter.
On the effect of the access path maintenance options on performance, see IBM i Database Guide.