by Peter Martin

RPG and Cobol developers spend a lot of time with Display Database Relations (DSPDBR) and Display File Description (DSPFD) trying to work out which access path is the most appropriate to use. Neither of these, though, shows access path details in a straightforward list. If you’re like me, you are a bit frustrated with IBM for not giving us a simple, clean way to see what access paths exist over a file and what their details are. I’ve written a utility called Display Access Paths (DSPACP) to solve this problem. Here, I give you the details you need to use DSPACP quickly and easily.

The Screens at a Glance

Let’s look at an example. Say you have a file called MENUMST with x DDS defined logical files, y SQL views, and z SQL indexes over it. Executing the command DSPACP MENUMST presents the screen in Figure 1.

From here, you can easily see what key fields exist for each of the access paths. In this example, file MENUMST has the key fields shown on the same line as the file. Notice that file MENUMSTL5 shows four key fields with a „+“ to the right; this means that the file has more than four keys. Press F20 to see the next four keys for all the files. If there are still more keys, press F20 until no more keys show. Pressing F19 will move back through the keys. Knowing which key fields are available is only one part of what you need to know.

You might choose a file based on its keys, but what if it also has Select/Omits or has a different record layout that doesn’t contain all the fields you require? Using F11, you can see a number of other details. As Figure 2 shows, the file type tells whether you are looking at a physical file (PF), a standard DDS defined logical file (LF), a DDS defined single-format join logical file (LF-JN), an SQL view (LK-VW), or an SQL index (LF-IX). This view also shows the format name and the key type (e.g., KU ­ Keyed Unique, AR ­ Arrival, EV ­ Encoded Vector Index).

The rest of the columns show whether the file has Select/Omits and/or multiple formats, whether the format is different from the base physical, and by how many fields the access path varies from the base physical file. Using F11 again shows the description associated with the file. Most of the other function keys are pretty self-explanatory: F3 exits, F4 prompts, F5 refreshes, and F9 retrieves commands that you entered either on the command line at the bottom of the screen or via F21 (which displays a command line). The F9 key also retrieves parameter values you might have entered when list options are prompted or executed (more about prompting of options later). F10 executes DSPPFM, F12 cancels, F15 redisplays the list — first in order of key fields, then by format name, then back to the default view by physical file and access path name. F17 and F18 let you move to the beginning and end, respectively, of the list. F22 prints a list of the access paths‘ details.

The F16 key lets you search the list of files via two search screens (Figure 3 and Figure 4). Here, you can enter one or more selections to restrict the list. Most are straightforward, but the key fields selections are worth a closer look. You can enter up to three key field names; entering only the names shows only those files with all of these key fields in any position and in any order. Sometimes, though, you want to see key fields in certain positions. The number to the right of the key field name specifies which position that field must exist in. For example, where I work, most files have a company number as the first key. If, say, I am looking for transaction date, I know that the company number will be first, so I enter a 2 to indicate that I want transaction date as the second key field.

You have several options you can use against an individual access path. Option 5 executes command DSPFD, and option 8 executes DSPOBJD — straightforward enough. Option 9 shows DSPLYT, but what if DSPLYT doesn’t exist on your system or in your library list? In this case, the option text shows 9=Display fields (DSPFFD), and DSPFFD executes. Later, we will see how that is controlled without changing source code. You control option 10 similarly.

Option 12 shows you a full list of the key fields for the access path in a separate screen (Figure 5), as well as other file details. If any of the files have Select/Omits, you can use option 15 to display their details (Figure 6). Option 16 displays the SQL statement used to create the access path (Figure 7). By the way, if you use option 12 on a file that has Select/Omits or SQL statements attached, you can display these with F15 and F16.
One more thing: If you don’t know the physical file that an access path exists over, simply use the access path’s name on the DSPACP command. The program will determine the physical file and process DSPACP appropriately. In this case, the list shows the access path at the top of the screen. Simply use rollback or F17 to go to the top of the list.

Load´n´Go nur für NEWSabonnenten plus:

November Utility2 der NEWSolutions
Load´n´go	Value Added Article: Display Access Paths Utility by Peter Martin Inhalte: Siehe unten

Panel Group Options

Behind the scenes, this utility uses a CL program as the command’s CPP. The CL checks for the access path’s existence and sends a message to the caller if it is not found. If the access path is not the underlying physical file, it determines the physical file, which it then passes to the main processing program. An RPGLE program (DSPACPR) provides the main processing, supported by a panel group to display the results and a second RPGLE program (DSPACPR2) to process the panel group options. Two other panel groups provide help text — one for the command and the other for the display panel. Additionally, a DDS defined printer file prints the access path details. In a panel group, options can be processed in one of two ways. You can use only one method in any one list; when you define a list, you must decide which method to use. The choice is defined using the „actor“ keyword — either UIM or CALLER (or NONE if there are no options at all). Actor = UIM means that the panel group processes all the list options; CALLER means that the calling program processes them. When the User Interface Manager (UIM) is in charge, you specify commands to be executed for all of the options available. These can be any iSeries or user-written commands (e.g., DSPFD, DSPLYT) or a call to a separate program that can retrieve and process the option. You can also include substitution values in the command string. So why choose the UIM to process options instead of processing the option yourself? Here are a few reasons; the UIM

• easily allows for and processes commands
• allows prompting of commands
• can easily process options that require substitution values
• allows the user to enter command parameters on the command line at the bottom of the screen (similar to IBM screens)
• can be designed to have confirmation lists
• can be designed to update or remove list entries after confirmation

Although DSPACP processes options the same way as DSPLYTR — through the UIM — some of the details are worth a second look. With DSPLYTR, I used the UIM to call a list-processing program. This utility uses that same method for options 12, 15, and 16. All the other options use a slightly different method of UIM processing where commands are executed directly.

In examining some of these options, you will see lines such as

'CMD  Dspfd  File(&Fil lib./&Filnam.) &cparms.'

As Figure 8 shows, this is used for option 5 (at A). In this case, DSPFD is executed with parameter values &Fillib and &Filnam. The variables Fillib and Filnam exist in the list definition for Listrcd and are identified as variables to the UIM because they start with an ampersand (&) and end with a period (.). Entering option 5 against a list entry executes it with the value for the file and library for that particular entry. Substitute these values into the command string before execution. The variable &cparms. tells the UIM to add any values entered on the command line to the command string. Figure 9 shows how this works. Here, option 5 is entered against file QADSPOBJ in library QSYS. Substituting Filnam = QADSPOBJ, Fillib = QSYS, and cparms = output(*print) into the command string instructs the UIM to execute

DSPFD FILE(QSYS/QADSPOBJ)  OUTPUT(*PRINT).

The code line at B in Figure 8 tells the UIM what to do if the user enters option 5 and presses F4 (Prompt) instead of Enter. The characters „?“ and „?*“ are the standard selective prompting values used in CL programs (for V5R3, see „Selective Prompting for CL Commands“ in the CL Programming Manual (SC41-5721) for a full list of prompting characters). In this case, the code is telling the UIM to prompt the command („?“) and show the FILE parameter as a protected value („?*“). Entering other parameter values on the command line will populate cparms. You substitute cparms into the command string; because it does not have any prompt characters associated with it, you can display and then change or delete it. To allow prompting, you must also set the key definition for F4 in the key list to action = Prompt (Figure 8 at E) to tell the UIM that F4 will act as the prompt key.

Let’s take a closer look at some other options I’ve already mentioned. You use option 9 to display either DSPLYTR or DSPFFD. The existence of the DSPLYTR command object in your library list determines which is used. The panel definition has two entries for this option (Figure 8 at C and D), both of which are conditioned; option 9 for DSPFFD has Cond = notDsplyt, and option 9 for DSPLYT has Cond = Dsplyt. These conditions are defined using UIM built-in functions.

Figure 8 at F shows the syntax for condition notDsplyt. Chkobj is a built-in function that lets you test for the existence of the specified object and for the requested level of authority. In the case of notDsplyt, the condition is true if an object called DSPLYTR does not exist in the library list or you do not have *USE authority over it.

Condition Dsplyt, on the other hand, is true if the object exists and you have *USE authority over it. The option text that is displayed and the command executed depend on which of the conditions are true. When you use Chkobj, if you need to qualify an object with a library, you can do so by specifying the object name as libname/objname.

The options to display Select/Omits and SQL statements are also conditioned, but the main RPGLE program sets these conditions. You see only options 15 and 16 when there are Select/Omits and SQLs, respectively, for any of the access paths. When the RPGLE program is building the list, it sets separate variables to indicate whether it has found a Select/Omit or an SQL statement. The UIM checks these to decide what options to display in the panel.

One other thing worth a look is how to handle the headings for the keys. When you use F20 and F19, the key number changes. On the initial screen, we see key numbers 1­4. Pressing F20 presents key numbers 5­8, then 9­12, and so on (F19 moves back through the list). To identify the displayed keys, the headings must change each time you press F19 or F20 — you do this by using heading variables in the UIM. The controlling RPGLE sets the variables through the heading data structure. By including the keyword colhead in the Listcol tag, you tell the UIM to use the variables instead of the normal heading text. When you use standard column text, a Listcol entry looks something like this:

:Listcol   Var = Key1
               Usage = Out
              Maxwidth = 13
               help = 'dspacp/key1'
              .'Key #1'

In this case, the heading text is fixed as ‚Key #1‘. This same column can be defined using variable text such as

:Listcol   Var = Key1
               Usage = Out
               Maxwidth = 13
              Colhead = 'tKey tKey1'
              help = 'dspacp/key1'.

With this method, you change the actual text by moving the current heading text into variables tKey and tKey1. The UIM displays this on two lines because there are two variables. It’s important to remember that if you use colhead for any column, you must use it for all columns in that panel. This is why you see column headings defined as constants in the main RPGLE program.

Under the Hood

Enough about panel groups — on to the programs themselves. Since introducing the DSPLYTR utility, I’ve been converting API calls that I use in DSPACP to procedure definitions. At first, I didn’t feel confident about defining the interfaces, but after a bit of practice, it became relatively straightforward. I’ve now managed to reduce the number of copybooks used in this and other programs substantially.

I made separate subroutines for the API calls redundant by placing all of the procedure definitions into the D-specs for each area of API usage. For example, all the procedure definitions of the User Index APIs are in copybook @UIXDFN. This reduces the number of user index subroutines that I need from eight to two, but it makes the definition copybook a lot bigger. I use the remaining subroutines to perform some functions for which there are no direct APIs. For example, to clear a user index, I might need to call the Remove User Index Entries API a number of times, because each call can remove a maximum of 4,095 entries. By putting a loop around Remove User Index Entries, I can clear any number of index entries.

The program starts with the #INIT subroutine, which calls the procedure definitions to create user spaces and user indexes if they don’t exist. This routine also calls the Retrieve Database Relations API to load the database relations user space, then builds the three indexes for the sort sequences.

Next, open the panel group, tell the UIM the name of the list action program, and build the panel header and list entries. Retrieve the access path name and library for the list entries from one of the user indexes, depending on the sort sequence that is active. You then retrieve the access path’s details using a call to the Retrieve Database File API. So far, this is all relatively simple, but now you have to traverse the access path information returned by the Database File API. The type, length, and position of the information varies between each entry, so you must carefully decide whether it exists, where it is located, and what you want out of it. Most of the hard work for this is done in the Build_Liste subroutine. Here, you set some general information flags about the access path and then get down to the nitty-gritty.

The first task is to build any Select/ Omit statements into lines of text. Then, get all the key fields for the access path. There is a problem here: Physical files don’t have the same key count as logical files. To overcome this, use the generic key count from the API data; doing so gives you the maximum number of keys over all formats, but I check each key found for valid character data. Lastly, any SQL statement used to create the access path is copied to a working variable in the program.

After the panel displays, you can either pass control back to the calling program for some of the function keys or let the UIM handle it. The function key handling in the RPGLE program is not complex — a couple of the keys toggle variables used by the UIM to control the display. For example, F15 determines the sort sequence where its values must be one of 0, 1, or 2. To easily handle this, I use the %rem built-in function.

The line that does this is

Head_SortSeq = %rem(Head_SortSeq 
   + 1: 3);

 

This way, the values for sort sequence will always be between 0 and 2 — you don’t need an If statement to stop the value of Head_SortSeq going beyond 2. The highlighted area in Figure 10 shows full processing for F15. Before you update the sort sequence value, get the current value from the UIM just in case it or other values have changed elsewhere. A call to the Get UIM Panel API in GetPnlHdr does the trick. After changing the value, update the value in the UIM with a call to the Put UIM Panel API. This process of retrieving the header, updating values, and rewriting it is common to a lot of the function key processing.