System i programmers typically spend a lot of time designing, programming, and implementing the databases that constitute the core of the business applications running on the System i. Part of this effort involves the creation and maintenance of logical files, views, and indexes to provide fast and appropriate access to the data in the database, irrespective of the functions, methods, and programming languages being used to perform this access.
So whenever the need for a specific index or subset of a given physical data file arises, the next thing to do is research whether the access path required already exists, or alternatively, whether it could be built using an existing access path as base. The closest that any native i5/OS command will get you is the Display Data Base Relations (DSPDBR) command. The DSPDBR command displays a list of all the currently existing and dependent logical files, views, and indexes for any specified physical file.
This information does not, however, include any access path information, such as the dependent file’s key fields, key sequence, and select/omit criteria. The idea of being able to quickly displaying all existing access path for a given physical file — as well as optionally being able to locate any access paths, including a specified field name — prompted me to create the Display Physical File’s Access Path (DSPPFAP) command.
Access paths, whether in the form of logical files, views, or indexes, all incur the penalty of requiring an update every time the data in the underlying physical file changes. Granted, there are ways to delay this update (e.g., the Access path maintenance — MAINT — attribute of logical files), but at some point, before the dependent file is used and accessed again, the update must be performed to ensure that the retrieved data is correct. And access paths require auxiliary storage space to store them.
Therefore, being able to locate an existing access path meeting your requirements, rather than creating a new similar access path, makes good sense. It reduces system resource requirements and saves disk space. Even if no access path matches exactly, you might be able to arrange the creation of the new dependent file in such a way that it shares the access path with an existing file and thereby reduce the incurred maintenance costs considerably. I have included links at the end of this article to point to the IBM documentation that discusses these issues and possibilities in more detail.
The DSPPFAP command is based on two APIs, first the List Database Relations (QDBLDBR) API which, similar to the DSPDBR command, produces a list of all dependent logical files for a given physical file. The list is written to the user space specified as the API’s first parameter. If you are familiar with the use of user spaces in conjunction with APIs, you’ll find the steps involved in retrieving a list of information from a user space trivial.
Articles on Apis 