Report programs are a necessary evil. If your AS/400 has data on it, you probably have people continuously asking you for new ways to slice and dice it. And, even though there are some really slick query tools that have come out in the last few years, RPG is often the fastest and most cost-effective way of extracting and formatting the data. (You probably don't want to tell your end users this, but RPG stands for report program generator.)

The next step after accepting the fact that reports are a part of your daily grind is to look for more efficient ways to do them. That is what this article is all about. If you could figure out a way to gather the desired data and somehow sequence it into the preferred processing sequence without writing it to a work file first, you would reduce disk I/O and minimize the number of objects required to produce your report. Obviously, fewer objects would mean less room for error. Given that I/O is one of the primary bottlenecks on your AS/400, reducing I/O means improved performance.

This wonderful technique is called building and maintaining a user index. It requires the working knowledge of a few APIs. We will provide an example of how you might want to put such a technique to work for you.

Instead of building a work file for your report and then using sort or OPNQRYF to sequence the data, APIs may be used to build indexes dynamically, eliminating the need for the work file altogether. By reducing the number of objects required by the report's job stream, you are reducing the number of objects to be maintained.

This user index technique will also allow you to determine whether the index should be placed in a permanent library on disk or not. If you elect to maintain the user index in library QTEMP (as we have done in our example), the system will store the index in a temporary area on disk, and it will be removed when you sign off.

You may also build multiple indexes over your data in a single pass (we elected not to include this technique in our example in order to keep the article brief). This ability can come in handy if you need to show data in two different sequences within a single job. The technique is also helpful if your data belongs in either one index or another depending upon conditions within the data.

To employ the user index technique, you will need to learn about three APIs: Create User Index (QUSCRTUI), Add User Index Entries (QUSADDUI), and Retrieve User Index Entries (QUSRTVUI).

User indexes have a few limitations that, for the most part, are insignificant. The maximum size of a user index is 4GB, unless your operating system is prior to V2R2M0, in which case the maximum size of an index is 1GB. The maximum size of a user index entry (both index and the associated data) is 2,000 bytes.

To demonstrate how to code user indexes, we have put together a sample program that will allow you to produce a list of objects that will be sequenced either by object size or owner. We use user indexes to sequence the data that will be included in our report.

Our example utilizes the Retrieve User Space (QUSRTVUS) API to extract the required information for our report.

To illustrate how user indexes work, we created an example program that produces a report showing information about system objects. The Submit Object Report (SBMOBJRPT) command (shown in 1) lets you select the objects you want to show on the report. It also has a parameter that lets you sort the report by object size or by object owner. The command processing program (OBJ015CL), shown in 2, submits the RPG program OBJ015RG (shown in 3) to batch.

To illustrate how user indexes work, we created an example program that produces a report showing information about system objects. The Submit Object Report (SBMOBJRPT) command (shown in Figure 1) lets you select the objects you want to show on the report. It also has a parameter that lets you sort the report by object size or by object owner. The command processing program (OBJ015CL), shown in Figure 2, submits the RPG program OBJ015RG (shown in Figure 3) to batch.

The OBJ015RG program first builds a user space called SIZSPC in library QTEMP. It then uses the List Objects (QUSLOBJ) API to list all of the requested objects into the user space. If the user enters invalid information, such as a library name that doesn't exist, the program will print an error message and end.

If all goes well, we use the Create User Index (QUSCRTUI) API to create a user index to sort the data before we print it. You can think of a user index as an array in which you store an index and the data associated with that index. Later, when we retrieve the index entry, both the index and the associated fields are returned in the sequence of the index (IENTRY data structure in our example).

Parameters for the QUSCRTUI API include the name and library of the index, the key length, and the length of each entry (the key length plus the length of the associated fields). Two other parameters utilized by this API help control the performance of the index: immediate update (the seventh parameter) and optimization (the eighth parameter). These two parameters allow you to trade off performance for CPU overhead depending on your environment.

The immediate update parameter controls when the entries are to be recorded in auxiliary storage (1=immediately, 0=later). If you choose to update the index immediately, the update will be written to auxiliary storage (disk) on each update to the index. On the other hand, you can choose to defer the I/O, and the changes will be buffered before they are written to disk.

The optimize parameter controls whether the index is optimized for random references (value 0) or for sequential references (value 1). The setting of this parameter would depend upon whether you are going to process the data in arrival sequence or not. Since our objective was merely to sequence our data, sequential reference was sufficient for our program example. 4 depicts the index being maintained in our example.

The optimize parameter controls whether the index is optimized for random references (value 0) or for sequential references (value 1). The setting of this parameter would depend upon whether you are going to process the data in arrival sequence or not. Since our objective was merely to sequence our data, sequential reference was sufficient for our program example. Figure 4 depicts the index being maintained in our example.

Once our user index is built in OBJ015RG, we walk through the data in the user space holding the object information we retrieved with the QUSLOBJ API (using the QUSRTVUS API), and we add to our user index using the QUSADDUI API for each entry that we extract.

The QUSADDUI API allows you to get very creative. It allows you to enter more than one entry with each call to the API, and the entries can be either fixed or variable lengths. Each entry is placed in the index based on its binary value. If you use variable length or have multiple entries, you also need to give the API an array of entry lengths and offsets. This API can get rather complex, but it doesn't have to. In our example, we have coded single entries with fixed lengths, as shown in 4.

The QUSADDUI API allows you to get very creative. It allows you to enter more than one entry with each call to the API, and the entries can be either fixed or variable lengths. Each entry is placed in the index based on its binary value. If you use variable length or have multiple entries, you also need to give the API an array of entry lengths and offsets. This API can get rather complex, but it doesn't have to. In our example, we have coded single entries with fixed lengths, as shown in Figure 4.

Once we have "walked" through all the entries in the user space, our pseudo-work file has been built.

The next step in our program is to retrieve entries from our index using the QUSRTVUI API. This API retrieves the entries that match the criteria specified in the search parameters into a variable. Even though our particular example only uses the search functions to walk through our index, other functions of the search method warrant an explanation.

In regard to user indexes, the search function of this API is probably comparable to a number of RPG op codes you are already familiar with (e.g., SETLL, READE, and REDPE) that are used with randomly accessed files. The difference, of course, is that instead of working with an established path over a database file, you are working with an index path that you built at run time.

The search parameters of the QUSRTVUI API consist of three principle components: the type of search to be performed, the search argument, and the size of the search argument. In our example, SEARTY represents the type of search to be performed, SEARCH is the search argument, and SERLEN is the length of the search argument.

You must fill the search type field (SEARTY) with the type of search you wish to perform. Valid search type entries include 1 (equal), 2 (greater than), 3 (less than), 4 (greater than or equal to), 5 (less than or equal to), 6 (first), 7 (last), and 8 (between). The search type will be used in conjunction with the search argument field (SEARCH) in order to determine which entries to retrieve. Each entry will be retrieved based on the binary value of the search parameter, and no other collating sequence is allowed.

Before you perform the search, however, you must specify the length of the receiver variable to store the results of the call to the API. The length (RCVVRL) of the index receiver variable (RCVVR1) should be no less than the sum of all of the entries plus 8-i.e., (entry size * maximum number of entries) + 8. The receiver variable itself (RCVVR1) will be the actual recipient of the data.

You can also control how many entries are returned each time the API is executed. In our example, we decided to retrieve 50 entries at a time. The MAXLEN parameter of the API was used to indicate this to the system. If there are more entries to process, the last index key processed is moved into the search field (SEARCH) and a greater than retrieve (indicated by the SEARTY parameter) is executed in order to read the next 50 entries. In our example, we began with the search field initialized with blanks, which caused the search to begin at the start of our index. We then parsed through the variable, using the entry length (ENTLLN) and offset (ENTLOF) parameters to extract each individual entry. The format of this parameter is shown in 5. In our example, we elected to print each entry as we extracted it.

You can also control how many entries are returned each time the API is executed. In our example, we decided to retrieve 50 entries at a time. The MAXLEN parameter of the API was used to indicate this to the system. If there are more entries to process, the last index key processed is moved into the search field (SEARCH) and a greater than retrieve (indicated by the SEARTY parameter) is executed in order to read the next 50 entries. In our example, we began with the search field initialized with blanks, which caused the search to begin at the start of our index. We then parsed through the variable, using the entry length (ENTLLN) and offset (ENTLOF) parameters to extract each individual entry. The format of this parameter is shown in Figure 5. In our example, we elected to print each entry as we extracted it.

The QUSRTVUI API also tells us the number of entries actually returned (NBRRTN). When this value is less than the requested value, there are no more entries to retrieve.

User indexes are a viable alternative to work files. If you master this technique, you can develop report programs that run faster and require fewer objects to maintain.

Doug Pence is the founder and Ron Hawkins is the research and development manager of Computer Processing Unlimited, Inc., in San Diego, California. They are the authors of Power RPG III, published by Midrange Computing. Doug Pence can be reached by E-mail at This email address is being protected from spambots. You need JavaScript enabled to view it..

Applying User Index APIs

Figure 1: The Submit Object Report Command

 /*==================================================================*/ /* To compile: */ /* */ /* CRTCMD CMD(XXX/SBMOBJRPT) PGM(XXX/OBJ015CL) + */ /* SRCFILE(XXX/QCMDSRC) */ /* */ /*==================================================================*/ CMD PROMPT('Submit Object Report') PARM KWD(OBJ) TYPE(QUAL) PROMPT('Object') PARM KWD(OBJTYP) TYPE(*CHAR) LEN(10) DFT(*ALL) + PROMPT('Object type') PARM KWD(ORDER) TYPE(*CHAR) LEN(6) RSTD(*YES) + DFT(*SIZE) VALUES(*SIZE *OWNER) + PROMPT('Order') QUAL: QUAL TYPE(*GENERIC) DFT(*ALL) SPCVAL((*ALL)) QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) + SPCVAL((*LIBL) (*CURLIB) (*USRLIBL)) + PROMPT('Library') 
 
Applying User Index APIs
 
Figure 2: CL Program OBJ015CL
 
 /*==================================================================*/ /* To compile: */ /* */ /* CRTCLPGM PGM(XXX/OBJ015CL) SRCFILE(XXX/QCLSRC) */ /* */ /*==================================================================*/ PGM PARM(&OBJ &OBJTYP &ORDER) DCL VAR(&OBJ) TYPE(*CHAR) LEN(20) DCL VAR(&OBJTYP) TYPE(*CHAR) LEN(10) DCL VAR(&ORDER) TYPE(*CHAR) LEN(6) SBMJOB CMD(CALL PGM(OBJ015RG) PARM(&OBJ &OBJTYP + &ORDER)) ENDPGM 
 
Applying User Index APIs
 
Figure 3: RPG Program OBJ015RG
 
 *=============================================================== * To compile: * * CRTRPGPGM PGM(XXX/OBJ015RG) SRCFILE(XXX/QRPGSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 FQSYSPRT O F 132 OF PRINTER E ARC 7000 1 E AOF 501 8 IGENDS DS I B 113 1160SIZINP I B 125 1280OFFLST I B 133 1360NUMLST I B 137 1400SIZENT IINPUT DS I 1 20 USRSPC I 1 10 SPCNAM I 11 20 SPCLIB ILIST DS I 1 10 OBJNML I 11 20 LIBNML I 21 30 OBJTYL I 42 91 TXTDSC I 113 122 OBJOWN I B 577 5800OBJSIZ I B 581 5840OJSZMT IERROR IDS I B 1 40BYTPRV I B 5 80BYTAVA I 9 15 MSGID I 16 16 ERR### I 17 116 MSGDTA ILAO DS 300 I B 1 40EL I B 5 80EO IRCVVR1 DS 7000 IENTLOF DS 408 ISFINDX IDS 96 I 1 10 SFNAME I 11 20 SFTYPE I 21 300SFSIZE I 31 76 SFDESC I 77 86 SFOWNR I 87 96 SFLIB IIENTRY IDS 126 I 1 30 KEYFLD I 1 10 KEY10 I 11 30 LIBOBJ I 31 126 DSINDX I DS I B 1 40STRPOS I B 5 80STRLEN I B 9 120LENSPC I B 13 160ENTLEN I B 17 200KEYLEN I B 21 240NBRADD I B 25 280INSTYP I B 29 320NBRENT I B 33 360RCVVRL I B 37 400ENTLLN I B 41 440NBRRTN I B 45 480MAXENT I B 49 520SEARTY I B 53 560SERLEN I B 57 600SEROFF I 61 80 INDEXN I 61 70 INDNAM I 71 80 INDLIB * C *ENTRY PLIST C PARM OBJLIB 20 C PARM PASTYP 10 C PARM ORDER 6 * C MOVEL'SIZSPC' SPCNAM C MOVEL'QTEMP' SPCLIB C MOVEL'USEDINDX'INDNAM C MOVEL'QTEMP' INDLIB C Z-ADD116 BYTPRV C Z-ADD30 KEYLEN C KEYLEN ADD 96 ENTLEN C EXCPTHEADNG * Create user space C CALL 'QUSCRTUS' C PARM USRSPC C PARM *BLANKS ATRSPC 10 C PARM 2048 LENSPC C PARM *BLANKS VALSPC 1 C PARM '*CHANGE' AUTSPC 10 C PARM *BLANKS TXTSPC 50 C PARM '*YES' RPLSPC 10 C PARM ERROR * List objects to user space C CALL 'QUSLOBJ' C PARM USRSPC C PARM 'OBJL0700'OUTFMT 8 C PARM OBJLIB 20 C PARM PASTYP OBJTYP 10 C PARM ERROR C MSGID IFEQ 'CPF9810' C MSGID OREQ 'CPF5715' C MSGID OREQ 'CPF3C31' C EXCPTPRTERR C ELSE * Create user index C CALL 'QUSCRTUI' C PARM INDEXN C PARM EXTEND 10 C PARM 'F' ENTATR 1 C PARM ENTLEN C PARM '1' KEYINS 1 C PARM KEYLEN C PARM '1' IMEUPD 1 C PARM '1' OPTIMZ 1 C PARM '*ALL' AUTHOR 10 C PARM *BLANKS TEXTD 50 C Z-ADD1 STRPOS C Z-ADD140 STRLEN * Retrieve user space general information C CALL 'QUSRTVUS' C PARM USRSPC C PARM STRPOS C PARM STRLEN C PARM GENDS C Z-ADD1 STRPOS C Z-ADDSIZINP STRLEN * Retrieve user space detail information C CALL 'QUSRTVUS' C PARM USRSPC C PARM STRPOS C PARM STRLEN C PARM INPUT C MOVEL'SIZSPC' SPCNAM C MOVEL'QTEMP' SPCLIB C OFFLST ADD 1 STRPOS C Z-ADDSIZENT STRLEN * Retrieve the list by walking through the user space C DO NUMLST C CALL 'QUSRTVUS' C PARM USRSPC C PARM STRPOS C PARM STRLEN C PARM LIST C MOVELOBJNML SFNAME C MOVELLIBNML SFLIB C MOVE OBJTYL SFTYPE C MOVELTXTDSC SFDESC C MOVE OBJOWN SFOWNR C OBJSIZ MULT OJSZMT SFSIZE C ADD SFSIZE TOTSIZ 100 C ORDER IFEQ '*OWNER' C MOVE SFOWNR KEY10 10 P C ELSE C MOVE SFSIZE KEY10 P C ENDIF C MOVELLIBNML LIBOBJ C MOVE OBJNML LIBOBJ C MOVE SFINDX DSINDX * Add to user index C CALL 'QUSADDUI' C PARM RTNLIB 10 C PARM NBRADD C PARM INDEXN C PARM 2 INSTYP C PARM IENTRY C PARM ENTLEN C PARM PASS16 16 C PARM 1 NBRENT C PARM ERROR C ADD SIZENT STRPOS C ENDDO C NBRRTN DOULTMAXENT * Retrieve user index C CALL 'QUSRTVUI' C PARM RCVVR1 C PARM 7000 RCVVRL C PARM ENTLOF C PARM 408 ENTLLN C PARM NBRRTN C PARM RTNLIB 10 C PARM INDEXN C PARM 'IDXE0100'FORMAT 8 C PARM 50 MAXENT C PARM 2 SEARTY C PARM SEARCH 30 C PARM 30 SERLEN C PARM 0 SEROFF C PARM ERROR C MOVEARCVVR1 ARC C MOVEAENTLOF AOF C KEYLEN ADD 1 UI * Do for the number of entries returned from QUSRTVUI C 2 DO NBRRTN X 70 * Move the lengths and offsets array to binary length and offset fields C MOVEAAOF,X LAO * Add the offset field to index field C ADD EO UI 70 C MOVEAARC,UI SFINDX C EXCPTDETAIL C ENDDO C UI SUB KEYLEN LK 70 * Set up search request for next 50 entries from QUSRTVUI C MOVEAARC,LK SEARCH C ENDDO C RTNLIB IFNE *BLANKS C EXCPTTOTAL C ENDIF C ENDIF C SETON LR OQSYSPRT E 202 HEADNG O OR OF O 5 'DATE:' O UDATE Y 14 O 67 'OBJECTS REPORT' O 121 'PAGE:' O PAGE Z 127 O EF 2 HEADNG O OR OF O 8 'LIBRARY' O 18 'OBJECT' O 27 'TYPE' O 45 'DESCRIPTION' O 90 'SIZE' O 106 'OWNER' O EF 1 DETAIL O SFLIB 11 O SFNAME 22 O SFTYPE 33 O SFDESC 79 O SFSIZEZ 90 O SFOWNR 111 O EF 1 PRTERR O 24 'ERROR OCCURRED - MESSAGE' O MSGID 32 O EF 1 TOTAL O 79 'SIZE OF ALL OBJECTS:' O TOTSIZZ 90 
Applying User Index APIs
 
Figure 4: Index Entries
  
 
Applying User Index APIs
 
Figure 5: Entries/Offsets Format Table