Eliminate Unnecessary Access Paths

June 30, 2004 Hey, Ted

One could make the case that computing is of two types: CPU- and I/O-intensive. An example of the former is the generation of graphics for a movie. An example of the latter is information technology. This means that iSeries shops looking to improve performance should concentrate on reducing the amount of I/O that must take place.

Think about what happens when a record is added to, deleted from, or changed in a physical file. The database manager must examine every access path built over the file and adjust any that are affected by changes to the file. The more access paths a file has over it, the longer the examination and adjustment process takes, and the slower the application runs.

One way to improve performance is to reduce the number of access paths over a physical file. DB2 UDB automatically shares access paths where possible. When a logical file is created, the database manager examines the access paths to see if a similar path already exists. A similar access path has the same major key fields, select/omit criteria, and so on. The V5R2 database programming manual explains the criteria that must be met for similarity. If a similar access path exists, DB2 attaches the existing access path to the new file. If a compatible access path is not found, DB2 creates a new access path.

For this reason, the order in which access paths are created is important. Assume two logical files, A and B. A is keyed on department and employee number. B is keyed on department, employee number, and transaction date. If A is created first, DB2 will create two access paths. If B is created first, DB2 creates only one.

The Display File Description (DSPFD) and Display Database Relations (DSPDBR) commands provide only a slight bit of information about shared access paths. If there is an API or a CL command that provides access path information in a concise format, I am not aware of it. But such a utility would be helpful, in that it would show us which access paths could be shared and help us to create them in the proper order. Another good utility would be one that deletes all logical files and sequences their rebuilding in such a way that access paths can be shared.

OS/400 includes a feature similar to the second proposed utility. When a physical file and its logical files are saved and restored, the iSeries rebuilds the logical files in descending order by number of key fields. In the example given above, file B, which has three key fields, would be restored before file A, which has only two key fields. The result is that a save and restore process may reduce the number of access paths over a physical file.

Anyone who undertakes this procedure should be absolutely sure, of course, that the backup is complete and reliable, so that the data can be safely restored. It would be good to take additional precautions, such as creating a separate copy of the physical file in another library and verifying that it is complete as well.

–Dominic Lefevre