If you’ve ever needed to display the same information in several different ways but don’t want the overhead of multiple logical files, user indexes may be the answer. This simple program demonstrates how user indexes can be used to sort information any number of ways, yet minimize file I/O. Use this program as a shell and create powerful inquiries for your users quickly.

A subfile is one of the most powerful tools available to AS/400 programmers. With a subfile, you can display common information in a single line, in multiple lines, or in multiple columns. A subfile can be for display only or for maintenance. What more could we want?

Well, how about a key that is more meaningful than relative record number? How about two or more sequencing options for a list? How about being able to insert a record in the middle of a subfile?

Until these features are added to subfile processing, how can we accomplish these tasks? User indexes!

Indexes? Indeed!

A user index is a type of object that is designed for indexing (sorting) and searching data. User indexes hold data, as do physical files, and can be saved and restored, even among different systems. As you insert data into a user index, the data is sorted. This feature of user indexes lets you sort without having to create work files.

User indexes are manipulated through APIs, not CL commands. I’ve provided a list of the user index APIs in Figure 1. See the Object APIs manual for more information about them.

A Sample Application of User Indexes

To illustrate how easy it is to use user indexes, I created a simple application consisting of a physical file of sales representatives, a display file, and an RPG IV inquiry program. I can’t include the source code here, but you can download it from Midrange Computing’s World Wide Web site at http://www. midrangecomputing.com/mc/98/10.

Figure 2 shows you the display that my program presents.

The physical file is keyed on sales rep number. Building a simple inquiry program to display this file in key sequence would be easy, but users might benefit more by seeing the data in other sentences.

A typical solution would be to determine all possible sort sequences needed and create logical files with those keys. Instead, I’ve chosen not to create logical files, but to load user indexes for each view. To keep it simple, the physical file contains three fields: Sales Rep Number, Name, and Territory. I created views indexed by rep number; rep name and number; and territory, rep name, and rep number. I designed my program to let users enter a 1, 2, or 3 to select the sequence in which they wish to view the data.

One of the first tasks my program does is to create the three user indexes in QTEMP. To do this, I use the API QUSCRTUI (Create User Index), which is similar in function to the Create Physical File (CRTPF) command. The parameters needed are described in Figure 3.

Each user index has a different key length, depending on the lengths of the fields I use to load the index. The first user index has a key length of five since it contains only a sales rep number. The last user index’s key is 65 bytes long because it is indexed over all three fields. All three user indexes have the same entry length because each user index will contain the same three data fields.

After the user indexes are created, I read the Sales Rep file sequentially , loading each record into all three user indexes.

Each user index will contain exactly the same data, but since each has a different index (key), they will be retrieved in different sequences. To write entries into a user index, I used the API QUSADDUI (Add User Index Entries). The parameters needed are listed in Figure 4.

With the user indexes loaded, it is time to load the subfile according to the view the user selected. To retrieve entries from a user index, I use the API QUSRTVUI (Retrieve User Index Entries). Any number of entries can be retrieved at a time, but I prefer to retrieve a number equal to the subfile page size (DDS keyword SFLPAG) so that I can load a page with each retrieval. My program could easily be modified to build the subfile with each press of the Page Down (Roll Up) key, as each retrieval would return the next page of data. API QUSRTVUI needs the parameters listed in Figure 5.

After each retrieval of entries into the receiver variable, the data returned must be divided into individual entries. For each entry returned, a subfile record is written.

To change views, simply press the number of the view desired. The subfile is immediately reordered, according to the sequence requested.

Impress the Users!

From here, the program could easily be modified to use any file. A selection field could be added to allow the user to do database maintenance or to display sales figures or any other feature needed. Additional views could be added simply by creating additional user indexes, adding entries to the user indexes, and retrieving them to build the subfile when selected.

Try this program and see how easy user indexes are to work with. Then, take my example program and turn it into something useful for your users!

Reference

System API Reference OS/400 Object APIs (SC41-5865, CD-ROM QB3AMQ01)

API Name Function

QUSCRTUI Create User Index QUSADDUI Add User Index Entries QUSRTVUI Retrieve User Index Entries QUSRMVUI Remove User Index Entries QUSRUIAT Retrieve User Index Attributes QUSDLTUI Delete User Index

Figure 1: User index APIs

Figure 2: The inquiry program can display sales reps in three sequences

Qualified user index name The first 10 characters contain the user index name; the last 10 characters contain the library name. Extended attribute Objects of type *FILE have an extended attribute of PF (physical file), LF (logical file), etc. I always specify *Blanks. Entry length attribute F = fixed-length entries; V = variable-length entries. This example uses fixed-length entries.

Entry length This is the total length of the data to be stored in the user index, including the index. In this example, all three user indexes have a length of 65, the sum of the lengths of the three fields in the physical file.

Key insertion This specifies whether or not the inserts to the index are by key. 0 = no insertion by key; 1 = insertion by key. To retrieve entries in key sequence, specify 1.

Key length This is the key (index) length for the user index. In this example, each user index has a different value, corresponding to the length of the field(s) that make up the key/index.

Immediate update This stipulates whether or not to update auxiliary storage on each update to the user index. 0 = no immediate update;

1 = immediate update. I specify 1 to ensure that the index is always correct. This option functions much like the MAINT(*IMMED) parameter of the CPYF command.

Optimization 0 = optimize for random references; 1 = optimize for sequential references. I specified 1 in this example because I want to load all entries from the user index in key/index sequence.

Public authority Any valid authority value will work here. I normally specify *ALL to keep things simple. Text description I specify either a simple description or *Blanks.

Replace This specifies *YES/*NO. I normally specify *YES. When creating a user index, if *YES is specified and the user index already exists, the user index is replaced.

Error code This is the standard error data structure used by most IBM APIs. By checking the error message ID portion of the data structure, you can trap any errors generated by the APIs.

Figure 3: Parameters of the QUSCRTUI API

Returned library name If you specify *LIBL or *CURLIB as the library in the qualified user index name parameter, this field will tell which library the user index was found in.

Number of entries added This output parameter indicates the number of entries successfully added to the user index. Qualified user index name The first 10 characters contain the object name; the last 10 characters contain the library name. Insert type 1 = insert unique argument; 2 = insert with replacement; 3 = insert without replacement. 1 is only valid for non-keyed user indexes. Since user index entries cannot have duplicate keys, options 2 and 3 determine what happens when an entry with a duplicate key is added to the user index. When 2 is specified, adding an entry with a duplicate key replaces the previous entry. When 3 is specified, adding an entry with a duplicate key causes an error message to be returned in the APIErrDS data structure. I always ensure that the entries I add have unique keys before I attempt to add them to the user index. I have included the sales rep number field as part of the key to each user index. This field is a unique key on the sales rep file, so every entry added to each user index will have a unique key.

Index entries This is the data structure containing the actual entry to be added to the user index. Because the key structure is different for each user index, I created three different data structures (U1DS, U2DS, U3DS) to contain the entries to be added to each user index.

Length of index entries This is the length of the previous field. Entry lengths and offsets This is used only if the user index contains variable-length entries. This example uses fixed-length entries, so it is ignored.

Number of entries This is the number of entries to be added to the user index at one time. I always specify 1 to keep things simple. Error code This is the error data structure used by most APIs.

Figure 4: Parameters of the QUSADDUI API

Receiver variable When entries are retrieved, they are first placed into the receiver variable. From here, they must be divided into individual entries. This field must be large enough to hold all entries being retrieved, plus 8 bytes. In this example, I am retrieving 15 entries of 65 bytes ((15 * 65) + 8) = minimum of 983 bytes. Length of receiver variable This specifies the length of previous parameter.

Entry lengths and entry offsets This must be ((maximum number of entries * 8) + 8), but it is normally needed only for variable-length entries.

In this example, I specified the field with a length of 128 = ((15 * 8) + 8). Length of entry lengths and entry offsets This specifies the length of previous parameter. Number of entries returned This specifies the number of entries returned to my program. This number will be less than or equal to the

Maximum number of entries parameter. Returned library name If you specify *LIBL or *CURLIB as the library in the qualified user index name, this field will tell which library the user index was found in.

Qualified user index name The first 10 characters contain the object name; the last 10 characters contain the library name. Format This is the format of the receiver variable. Only one value is currently supported: IDXE0100. Maximum number of entries I specified 15 to match a single page of values.

Search type This is used with the Search Criteria to determine what to retrieve. 1 = equal; 2 = greater than; 3 = less than;

4 = greater than or equal; 5 = less than or equal; 6 = first; 7 = last; 8 = between. I specified 2 = greater than to indicate that I wanted the next entry after the last retrieved.

Search criteria This specifies the key/index to use when retrieving entries. Length of search criteria This specifies the length of previous parameter. Search criteria offset If Search Type 8 = between is specified, this parameter indicates the offset from the “from” value to the “to” value in the search criteria parameter. Since I did not use Search Type 8, this parameter is ignored. Error code This is the error data structure used by most APIs.

Figure 5: Parameters of the QUSRTVUI API

Also Read

The Power of Writing Code in a Low-Code Solution

TechTip: Let’s Have a Date, Part 2

Going off the Rails