Easy-to-Use User Indexes

by Bruce Guetzkow

[The code for this article is available for download.]

Keyed physical and logical files can be useful when organizing data, but sometimes they can't be used. A variance report is typically sorted by a calculated value, which is not part of the database. Data returned from an API is not in a database, so it can't be processed using physical and logical keys. Work files can solve this problem, but an alternative is user indexes. A user index offers the usefulness of a key structure without the typical maintenance issues involved with production physical and logical files. This article explains the basic APIs needed to employ user indexes, and presents procedures and a service program to simplify their use.

General Concepts

These APIs make use of the standard API error data structure:

     d apierrds        ds                  inz qualified
     d  bytesprov                     9b 0 inz(%size(apierrds.msgdata))
     d  bytesaval                     9b 0
     d  msgid                         7a
     d  filler                        1a
     d  msgdata                     100a

I have configured all of the procedures to return the error message ID and message text in the first 107 bytes of the return value. Your application program can then test the message ID for a successful call (blanks). If the message ID is not blank, it can be passed to an error-handling routine, along with the message text, for error processing.

The following procedures have simplified the API calls as much as possible. The version presented uses default values that are appropriate to my own work environment. If you find these defaults are not right for your situation, you can easily customize them.

Create a User Index

To use a user index, you must first create one, using the QUSCRTUI (Create User Index) API. The API has 10 required parameters and five optional parameters. First, the required parameters:

• Qualified user index name (input, 20 bytes, character)--the first 10 bytes contains the user index name, and the last 10 bytes contain the user index library name.
  
  
• Extended attribute (input, 10 bytes, character)--must be a valid *NAME or blanks.
  
  
• Entry length attribute (input, 1 byte, character)--Fixed or Variable length entries are allowed.
  
  
• Entry length (input, 4 bytes, binary)--the length of each user index entry.
  
  
• Key insertion (input, 1 byte, character)--indicates whether entries are inserted by key.
  
  
• Key length (input, 4 bytes, binary)--the length of the key for each user index entry.
  
  
• Immediate update (input, 1 byte, character)--indicates whether updates to the index are written synchronously to auxiliary storage. Entries not written to storage immediately may (in theory) not be immediately available for retrieval.
  
  
• Optimization (input, 1 byte, character)--optimization for random or sequential access.
  
  
• Public authority (input, 10 bytes, character)--the authority given to users who don't have specific authority to the user index.
  
  
• Text description (input, 50 bytes, character)--text description of the user index.

I will only discuss the first two optional parameters, as I have never needed to use the remaining parameters. These are the optional parameters:

• Replace (input, 10 bytes, character)--indicates whether you want to replace a user index with the same name.
  
  
• Error code (input/output, variable size, character)--standard API error data structure.

Procedure #crtusridx simplifies this list to just four parameters: user index name, user index library, user index entry length, and user index key length. Here are the remaining parameters use default values:

• Extended attribute: I have always left this parameter blank.
  
  
• Entry length attribute: "F"ixed length entries.
  
  
• Key insertion: "1" (insertion by key).
  
  
• Immediate update: "1" (update auxiliary storage immediately).
  
  
• Optimization: "1" (optimize for sequential reference).
  
  
• Public authority: *ALL: I've chosen this value since I always create user indexes in QTEMP, so it doesn't really matter.
  
  
• Text description: I have always left this parameter blank.
  
  
• Replace: I always use *YES.

The return value for this procedure is the API error data structure message ID and message text. This can be interrogated to determine the success of the procedure call.

Add User Index Entries

Now that you have created a user index, it is time to load it with entries, using the QUSADDUI (Add User Index Entries) API. This API has nine required parameters:

• Returned library name (output, 10 bytes, character)--contains the name of the library name of the user index.
  
  
• Number of entries added (output, 4 bytes, binary)--the number of entries successfully added with this API call.
  
  
• Qualified user index name (input, 20 bytes, character)--the first 10 bytes contains the user index name, and the last 10 bytes contain the user index library name.
  
  
• Insert type (input, 4 bytes, binary)--identifies how duplicate entries are handled.
  
  
• Index Entries (input, variable size, character)--this is the actual data added to the user index.
  
  
• Length of index entries (input, 4 bytes, binary)--the length of the user index entry being added. This must match the value used when creating the user index.
  
  
• Entry lengths and offsets (input, variable size, character)--this parameter is ignored for fixed-length entries.
  
  
• Number of entries (input, 4 bytes, binary)--the number of entries to be added with this API call.
  
  
• Error code (input/output, variable size, character)--standard API error data structure.

Procedure #addusridx simplifies this to four parameters: user index name, user index library name, entry, and entry length. The remaining input parameters use default these values:

• Insert type: "2" for Insert with Replacement (more on this later in the article)
  
  
• Entry lengths and offsets: use blanks
  
  
• Number of entries to be added: "1" (Although you can add as many entries as you want with each call of the API, I prefer to add only one to simplify program logic.)

As with the previous procedure, the return value is the API error data structure message ID and message text.

Retrieve User Index Entries

With your user index loaded with data, it's time to retrieve that data, using the QUSRTVUI (Retrieve User Index Entries) API. This API has 14 parameters:

• Receiver variable (output, variable size, character)--this is the data retrieved from the user index.
  
  
• Length of receiver variable (input, 4 bytes, binary)--length of data retrieved from user index.
  
  
• Entry lengths and offsets (output, variable size, character)--a data structure that contains the lengths and offsets for all entries returned.
  
  
• Length of entry lengths and offsets (input, 4 bytes binary)--the length of the previous parameter.
  
  
• Number of entries returned (output, 4 bytes, binary)--actual number of entries returned from this API call.
  
  
• Returned library name (output, 10 bytes, character)--contains the name of the library name of the user index.
  
  
• Qualified user index name (input, 20 bytes, character)--the first 10 bytes contains the user index name, and the last 10 bytes contain the user index library name.
  
  
• Format (input, 8 bytes, character)--the only valid value is IDXE0100.
  
  
• Maximum number of entries (input, 4 bytes, binary)--the maximum number of entries to return from this API call.
  
  
• Search type (input, 4 bytes, binary)--the type of search to perform. Valid values: 1 = equal; 2 = greater than; 3 = less than; 4 = greater than or equal; 5 = less than or equal; 6 = first (the first entry in the user index); 7 = last (the last entry in the user index); 8 = between
  
  
• Search criteria (input, variable size, character)--this is the value you are searching for or are positioning the user index to.
  
  
• Length of search criteria (input, 4 bytes, binary)--the length of the previous parameter.
  
  
• Search criteria offset (input, 4 bytes, binary)--this value is ignored unless search type 8 (between) is used. In this case, the search criteria parameter contains both the "from" and "to" values of the search, and this is the offset from the start of the search criteria field to the beginning of the "to" value.
  
  
• Error code (input/output, variable size, character)--standard API error data structure.

That list may seem daunting, but procedure #rtvusridx trims the list to only six: user index name, user index library, entry length, key length, search type, and search criteria. Default values for the remaining parameters are:

• Length of receiver variable: I have set the receiver variable to 2,000 bytes, so this parameter is defaulted to a value of 2000.
  
  
• Length of entry lengths and offsets: This value is calculated in the procedure, based on the maximum number of entries parameter.
  
  
• Format: There is only one valid value, IDXE0100.
  
  
• Maximum number of entries: This value is calculated in the procedure, based on the size of each index entry.
  
  
• Length of search criteria: This value is calculated in the procedure, based on the size of the index key.
  
  
• Search criteria offset: This value is calculated in the procedure, based on the search type.

For this procedure, the return value is the API error data structure message ID, the message text, the number of entries returned, and the receiver variable.

Delete a User Index

When finished with a user index, it should be deleted, using the QUSDLTUI (Delete User Index) API. There are only two parameters:

• Qualified user index name (input, 20 bytes, character)--the first 10 bytes contain the user index name, and the last 10 bytes contain the user index library name.
  
  
• Error code (input/output, variable size, character)--standard API error data structure.

The #dltusridx procedure also uses two parameters: user index name and user index library. The return value is the API error data structure message ID and the message text.

The Procedures

The source for the service module is available for download.

And here are the prototypes for the procedures. You will need to add these to the calling programs:

     d #addusridx      pr           107a
     d  p_usridxnam                  10a
     d  p_usridxlib                  10a
     d  p_entry                    2000a
     d  p_entlgth                     9b 0

     d #crtusridx      pr           107a
     d  p_usridxnam                  10a
     d  p_usridxlib                  10a
     d  p_entlgth                     9b 0
     d  p_keylgth                     9b 0

     d #dltusridx      pr           107a
     d  p_usridxnam                  10a
     d  p_usridxlib                  10a

     d #rtvusridx      pr          2111a
     d  p_usridxnam                  10a
     d  p_usridxlib                  10a
     d  p_entlgth                     9b 0
     d  p_keylgth                     9b 0
     d  p_srchtype                    9b 0
     d  p_srchcrit                  200a

The following data definitions are used in calling the procedures:

     d uientds               ds             inz
     d  ui_keyflds
     d    ui_field01                  3a    overlay(ui_keyflds)
     d    ui_field02                  1a    overlay(ui_keyflds:*next)
     d    ui_field03                  3s 0  overlay(ui_keyflds:*next)
     d  ui_field04                    3s 0
     d  ui_field05                    5s 0
     d  ui_field06                   12a

     d uientlgth        s             9b 0  inz(%size(uientds))
     d uikeylgth       s              9b 0  inz(%size(ui_keyflds))

     d entry              s        2000a    inz
     d strpos          s              9b 0  inz(9)

     d usridxnam       s             10a   inz('YOURUSRIDX')
     d usridxlib       s             10a   inz('QTEMP     ')

     d srchcrit        s            200a   inz
     d srchtype        s              9b 0 inz(2)

     d rtnds01         ds                  inz qualified
     d  msgid                         7a
     d  msgdata                     100a

     d rtnds02         ds                  inz qualified
     d  msgid                         7a
     d  msgdata                     100a
     d  nbrentrtn                     9b 0
     d  rcvvar                     2000a

Using the Procedures

To use the user indexes, first create the user index:

                   rtnds01 = #crtusridx(usridxnam:
                                        usridxlib:
                                        uientlgth:
                                        uikeylgth);

Next, in a loop, add the entries:

                    entry     = uientds;
                    rtnds01 = #addusridx(usridxnam:
                                         usridxlib:
                                         entry:
                                         uientlgth);

When retrieving the user index entries, remember that the data is returned in a receiver variable that may contain multiple entries. Each entry must be parsed out. The first eight bytes of the receiver variable do not contain data, so by resetting field strpos, you begin retrieving data from the receiver variable from the ninth position. The following logic retrieves all entries in the user index sequentially by key. The for-loop will process all index entries retrieved with each execution of the API:

                   reset srchcrit;

                    dou rtnds02.nbrentrtn <= *zeros;
                     rtnds02 = #rtvusridx(usridxnam:
                                          usridxlib:
                                          uientlgth:
                                          uikeylgth:
                                          srchtype:
                                          srchcrit);

                     if rtnds02.nbrentrtn > *zeros;
                       reset strpos;

                       for nbrentrtn = 1
                         to rtnds02.nbrentrtn;

                         uientds = %subst(rtnds02.rcvvar:
                                          strpos:
                                          %size(uientds));

                         strpos   = strpos + uientlgth;
                         srchcrit = %subst(uientds:1:uikeylgth);

                       endfor;
                     endif;
                   enddo;

Finally, you can delete the user index:

                   rtnds01 = #dltusridx(usridxnam:
                                        usridxlib);

For the create, add, and delete procedures, the following code can be used to test for error conditions:

                   if rtnds01.msgid <> *blanks;
		error logic
                   endif;

For the retreive procedure, the only change is to reference the message ID from the corresponding return data structure:

                   if rtnds02.msgid <> *blanks;
		error logic
                   endif;

Quirks

I have noticed a couple of interesting quirks associated with user indexes. First, numeric values in a user index key are unsigned; therefore, a variance of $17.21- is the same as $17.21+. This can be useful if you want to list positive and negative variances intermingled on the same report. However, if you wish to have separate "negative" and "positive" variance reports, you may need to create separate user indexes for each.

Second, user indexes do not accept duplicate entries. You must make your entries unique to accept all values. That's because of the Add User Index Entries API Insert Type parameter: Duplicate entries are either dropped or replaced based on the value you specify. If you want a variance report sorted by the variance amount, several items may have the exact same variance amount. In this instance you will want to create a user index with a key consisting of the variance amount and one or more values that make the entry unique, probably the item number (and warehouse location, if the variance is by location).

Final Word

User indexes are a valuable tool. And with the APIs available as procedures, using them should be a snap.

Bruce Guetzkow has programmed on the AS/400 and iSeries since 1990, in manufacturing, distribution and other industries. He is currently the IS director at United Credit Service in Elkhorn, Wisconsin. E-mail: bruceg@unitedcreditservice.com

Sponsored By T.L. ASHFORD

BARCODE400 by T.L. Ashford is the easiest and fastest way to create and print Compliance Labels directly from the AS/400 and iSeries. Ashford's comprehensive library of Compliance formats is available to Barcode400 users. AIAG labels for Ford and Motorcraft, GM, and many more are available. BARCODE400 is backed by the best Technical Support Team in the industry. FREE Guide to Bar Code Labeling www.tlashford.com or call 800.541.4893