We have been writing monthly articles for MC for five years now. We decided to celebrate this momentous occasion by revisiting one of our favorite utilities, one we published way back in May 1995 (“The Display Access Path Utility”). What the utility does is find all keyed access paths over a physical file and present them in a subfile like the one seen in Figure 1. Although the original version of this utility was written in RPG III, we have updated the version in this article to RPG IV.

One of the first steps in writing any new program on the AS/400 is to determine which paths are available for the data you need to process. Finding available access paths is a simple task, but it seems to take more steps than it should when you sit down to write a new program. To find the existing data paths that are available over a physical file on the AS/400, you generally perform the following steps:

1. Run the Display Database Relations (DSPDBR) command over the physical file in question.

2. Write down the file and library names of each logical file found on the DSPDBR display.

3. Run the Display File Description (DSPFD) command over each logical file to see if the data path you need already exists.

These three steps can be time-consuming and cut into your productivity as a programmer. Many times, programmers circumvent this process by simply creating the data path they desire with no consideration to those that already exist. This can result in duplication of data paths and unnecessary overhead for the system.

A few creative programmers have developed tools that perform these steps automatically and save much of the time required. Most of these tools write the results of the DSPDBR command to an outfile and then either print them or bring them up on a display. This, too, can be a little cumbersome, however. Our Display Path (DSPPTH) command goes one step further than other tools by using the system APIs to bring the information to the screen much more quickly.

As previously stated, the DSPPTH command displays all available access paths for a given database file in a subfile on your screen. You can either key DSPPTH followed by the qualified file name or key DSPPTH and press F4 to see the prompt screen reflected in Figure 2. When you key a valid file name, you will see a subfile similar to the one seen in Figure 1.

The top of the screen in Figure 1 reflects the name and library of the physical file specified. It also shows the number of logical files that were found over the physical file in question. The keyed path for the physical file is always listed first. The key fields for physical and logical files alike are shown. The key fields are followed by any Select or Omit statements if they are used. The sequence (ascending or descending) is reflected under the column labeled Seq. The information path for each logical file built over the physical appears even if the logical file is built in a different library.

Finding Your Way

Usually, the first thing any program does is grunt work. The program in this tool is no exception. First, we see if the file exists by using the Retrieve Object Description (QUSROBJD) API and then send a message by using the Send Program Message (QMHSNDPM) API to the caller of the program if it doesn’t. Next, we know that two of the APIs we are going to use need a user space in order to work, so we create two user spaces using the Create User Space (CRTUSRSPC) API.

Now, we need some information about the file. We use the GETFIL subroutine to accomplish this. This routine uses the Retrieve File Description (QDBRTVFD) API, which returns information about the file in one long variable. This variable contains a series of data structures that we can walk through to extract the information we are looking for. The information we need from this API is the number of formats in the file, the number of key fields, and any Select or Omit statements used to select records for the access path.

Next, we need to determine the name of all the logical files built over the physical file. To get that information, we run the List Database Relations (QDBLDBR) API. This API returns the requested information in a user space. The information that is returned comprises a series of data structures, and we use the Retrieve User Space (QUSRTVUS) API to retrieve each data structure of information. For each logical file found, we call the GETFIL subroutine to retrieve the same kind of information we retrieved for the physical file.

If a logical file has more than one format, we want to show all of the keys for each format in the file, so we condition a loop based on the number of formats in the file. Then, within this loop, we set up another loop based on the number of key fields in the file. For each key field that we find, we write a subfile record. If Select or Omit statements exist, we call the SELOMT subroutine to parse them out of the user space and write a subfile record for each statement.

QDBRTVFD is one of the most complex APIs around. The amount of information returned by this API is staggering and can be overwhelming. But once you learn the techniques for getting at the data, it’s really not that hard. And the results are definitely worth the trouble.

Follow the Path to Riches

We consider this tool one of our favorite gems, and we hope that you will use and enjoy it as much as we have.

Many decisions in life, both large and small, are about deciding which path to follow. We think that the tool in this article may make some of the small decisions a little easier.

<>
<>
<>

Figure 1: The DSPPTH command produces this output.

Figure 2: This is the prompt screen for DSPPTH.

<>
<>
<>

Also Read

Advanced/Free - Function Calls and Return Values

PRACTICAL ILE - ILE Service Programs

Data-Manipulation Operations Using Free Format, Part Two