Long ago, subfiles became a main tool in 5250 screen design. There are three basic subfile styles:

• Page Equals Size
• Page at a Time
• Load All

Page Equals Size (PES) subfiles are my favorite. They offer more flexibility than the other styles in that you can do positioning as well as paging both forward and backward. Above all else, PES subfiles provide fast response times for end-users because only one record at a time is loaded.

Page at a Time (PAT) or "load-as-you-go" subfiles are nearly as flexible as PES subfiles. PAT subfiles are loaded one page of records at a time. When the user presses the RollUp or PageDown key, the next page of records is written to the subfile. At this point, the subfile has two pages of records and the end-user can roll back and forward very efficiently.

Load All subfiles are great for small sets of data. Data sets that contain a few hundred to about a thousand or so records can be loaded into the subfile. Load All subfiles load the subfile with all the data and let the system handle the rolling/paging.

There is also the subfile-in-a-window technique, which is widely used to provide a prompt for fields on data entry panels.

All styles of subfile have one thing in common: the lack of the ability to sort the subfile records.

Subfile sort has been needed since about 10 minutes after the first subfile was created. The fact that IBM did not provide this capability initially and has failed to provide it at any point in the last 20+ years is yet another sign that IBM is out of touch with software developers in the OS/400 marketplace.

In a recent informal survey, I asked RPG developers if subfile sort was a priority today. The response was an overwhelming yes! So I've designed a simple interface that IBM could use to provide subfile sort to OS/400 developers.

The idea is to add a SFLSORT keyword to DDS for display files. This keyword supports two parameters. The first parameter identifies the function key used to sort the subfile in ascending order by the field over which the cursor is positioned. The optional second parameter indicates the function key used to sort the subfile in descending order by the field over which the cursor is positioned. If descending sorts are not needed, the second parameter is simply omitted.

To use the subfile sort capability, an end-user moves the display cursor into the field over which the subfile will be sorted. When the end-user presses the subfile sort function key, the system then reorders the data in the subfile accordingly.

An example of this SFLSORT keyword being specified on a typical PAT subfile is illustrated in Figure 1.

Figure 1: This is how you'd use the SFLSORT keyword in a display file.

The last statement in Figure 1 contains the SFLSORT keyword. Command keys F5 and F17 are used to sort this subfile in either ascending or descending order, respectively. Obviously, there will be a subsequent requirement of sorting by multiple fields, but this simple support is better than what we've had for the last two decades, and I think it's good enough.

A second keyword, SFLSORTFLD, would be used to control which fields in the subfile detail record support sorting. If no SFLSORTFLD keyword is specified or if SFLSORTFLD(*YES) is specified, then the field is a candidate for sorting. If SFLSORTFLD(*NO) is specified, then that field is not a candidate for sorting.

Subfile Sorting Options

Of course, waiting for the SFLSORT keyword is a little like waiting to win the lottery; you have to do something in the meantime or you'll have a lot of challenges in your day-to-day life.

At least half a dozen techniques are being used today to sort the data in a subfile:

• Load the data to both the subfile and a multiple-occurrence data structure and then sort the data in the data structure based on the desired sort field.
• Read the data from the source data set and then write it to a work file. Then, use OPNQRYF over that work file with the keyfield being the subfile field to sort the data.
• Use multiple logical views. Read the one that contains a keyfield over the field in which to sequence the data.
• Use embedded SQL and dynamically build the SQL statement, including the ORDER BY clause with the PREPARE and DECLARE statements followed by a consistent FETCH statement.
• Call the QLGSORT API to sort and then read the data from the original data set.
• Move the data into a runtime array and use the SORTA over the array.

All of these techniques require reloading the subfile data after sorting it. My assumption with a SFLSORT keyword would be that IBM would sort the data in place so the subfile would not necessarily need to be reloaded, thereby saving time, resources, and thousands upon thousands of redundant coding techniques.

With the exception of the first technique, there really isn't much you can do about the performance or flexibility of these techniques. They all provide about the same level of flexibility; the OPNQRYF and SQL approaches are probably the best choice for an application with thousands of records in a subfile. But really, you do have to question whether a subfile containing thousands of records is really useful to an end-user.

What I would like to focus on for subfile sorting is to provide an optimized approach to the first technique: sorting the subfile data that has been mirrored in a multiple-occurrence data structure.

Preparing for Subfile Sort

The first thing you need to do to provide a mirror image of your subfile is to create a multiple-occurrence data structure with the same format as your subfile. This technique is common practice when using embedded SQL and the FETCH statement to fetch multiple records simultaneously.

Data can be loaded directly from the data structure into the subfile by using a loop and the OCCUR opcode. Typically, programmers load both the data structure and the subfile when the subfile is initially loaded and then sort the data in the data structure when requested.

To create a data structure with the same format as a subfile, use the EXTNAME keyword along with its second parameter. The first parameter of EXTNAME is the name of the file on which the data structure is based. The rarely used second parameter of EXTNAME identifies the record format name of the file to use as the data structure's format. In order to format the data structure like the subfile, specify the EXTNAME keyword as follows:

.....DName+++++++++++EUDS.......Length+TDc.Functions++++++++++++++++++ 
     D SFLSORT       E DS                  EXTNAME(WRKCUST : CUSTLIST)

The EXTNAME(WRKCUST : CUSTLIST) identifies WRKCUST as the display file name and CUSTLIST as the format or subfile name. Therefore, the SFLSORT data structure will be of the same format as the CUSTLIST subfile.

Next, you have to accommodate the records that will be stored in the subfile. We all know of the ancient limitation of 9,999 records in a subfile. This limitation has not been relaxed for a number of reasons, including the overhead involved in storing that many records as well as the unrealistic expectation that an end-user can actually use that much data at once. Also, the PES and PAT subfile techniques are much more popular than the Load All technique.

To provide space for all the subfile records, you add the OCCURS keyword to the data structure as follows:

.....DName+++++++++++EUDS.......Length+TDc.Functions++++++++++++++++++ 
     D SFLSORT       E DS                  EXTNAME(WRKCUST : CUSTLIST) 
     D                                     OCCURS(10000) 

You now have space to support up to 10,000 subfile records. Unfortunately, if your subfile is only a few dozen or even a few hundred records, allocating space for 10,000 records is a little like a commuter using a school bus to drive to work each day--lots of space but not very practical.

To avoid unnecessary storage being allocated and unused, you need to add the BASED keyword. This keyword tells the compiler that you will provide the storage for the data structure at runtime, so the compiler avoids generating code that would allocate it automatically for you. Therefore, you use only the storage you actually need.

The following illustrates the use of the BASED keyword on a multiple-occurrence data structure:

 
.....DName+++++++++++EUDS.......Length+TDc.Functions++++++++++++++++++ 
     D SFLSORT       E DS                  EXTNAME(WRKCUST : CUSTLIST) 
     D                                     OCCURS(10000) BASED(pSort) 
 

Now that you've got your data structure set up to mirror the subfile's record format, you need some way to allocate the storage for the data structure.

I've selected the CrtUsrSpcPtr() procedure in the RPG ToolKit because it is the easiest way to do this. This procedure (create user space and return pointer) creates a user space in QTEMP with a random name and returns a pointer to the user space. So, to allocate the space for the data structure, you only need to add one line of code to the Calculation specifications, as follows:

.....C..n01..............OpCode(ex)Extended-factor2+++++++++++++++ 
     C                   eval      pSort = CrtUsrSpcPtr('*') 

The user space created by the CrtUsrSpcPtr() procedure acts as the memory for the multiple-occurrence data structure. It automatically grows as more occurrences are accessed within the data structure.

Sorting the Subfile

Sorting the subfile doesn't occur with today's technology, so the only thing you can do is sort the data structure that you've set up to sort the subfile data. Remember, you would add an occurrence to the data structure in the same way you would write out a subfile detail record, so the data structure mirrors the data in the subfile.

Sorting the data structure occurrences, as it turns out, is remarkably easy. There is a C language runtime procedure called QuickSort (QSORT) that will sort an array of elements. Since a multiple-occurrence data structure is similar to an array, it turns out QSORT can also sort data structure occurrences.

The prototype for the QSORT procedure is illustrated below:

     D QuickSort       PR                  ExtProc('qsort') 
     D  szSortDS                       *   VALUE 
     D  NbrElems                     10U 0 VALUE 
     D  SizeOfElem                   10U 0 VALUE 
     D  CompProc                       *   VALUE ProcPtr  

QSORT works by arranging the elements of an array based on the value returned from a user-written procedure. That procedure is specified on the fourth parameter of the call to qsort().

QSORT Parameters

The first parameter is the array or multiple-occurrence data structure that will be sorted. Since this is a C language procedure, you have to pass in the address of the array or data structure using the %ADDR() built-in function.

The second and third parameters indicate the number of elements in the array or data structure and the length of a single element, respectively.

The fourth and final parameter accepts a procedure name (actually the address of a procedure) that is called to do the actual comparing for the QSORT procedure.

This procedure must accept two parameters. Both are pointer data types, passed by value. The procedure must return a 10i0 (int4) value that indicates the results of the comparison. The prototype for this procedure is illustrated below:

 
     D myCompare       PR            10I 0 
     D  pValue1                        *   Value 
     D  pValue2                        *   Value 

The procedure and parameter names are names you create. When the procedure is called, it is passed two parameters. The first parameter is an element of the array or data structure that should be compared with the second parameter, which is also an element of the array or data structure.

The procedure must return 1, -1, or 0. A return value of 1 means the value on the first parameter is greater than the value of the second parameter. A return value of -1 means the value of the first parameter is less than the value of the second parameter. A return value of zero indicates that the two values are equal.

The procedure can be very simple (do the comparison and return the result) or very complex (map the input parameters to data structures and compare individual subfields of the data structure and return the result).

To sort the subfile, you call qsort() and pass to it the data structure and the compare procedure's names, as follows:

C                   callp     QuickSort(%addr(SFLSORT): nElems : %size(SFLSORT) : 
C                                       %paddr('MYCOMPARE')) 

Note that the name of the compare procedure is in all uppercase and enclosed in quotes within the %PADDR() built-in function. The %PARRD() built-in function returns the address to the specified procedure name. Since RPG procedures are, by default, converted to all uppercase when the source is compiled, you need to specify the procedure name in all capital letters.

The only thing you need to keep track of is the current number of elements in the array or data structure. In the example above, you're using the nElems variable to store the current number of elements. After all, if you declare a multiple-occurrence data structure with 10,000 elements and use just 50 of them, sorting the entire data structure would not only return bad results, it would be inefficient.

By specifying the number of current elements, you get a more granular level of control than you do with native RPG IV interfaces, such as the SORTA operation code, which can only sort the entire array.

The final component is the compare procedure. A simple version of this procedure is illustrated in Figure 2.

Figure 2: This is the Compare procedure for qsort().

In Figure 2, the MYCOMPARE procedure maps the input parameters to two local data structures, COMPAREDS1 and COMPAREDS2. By mapping the input parameters to these data structures, you can easily access the subfields of the data structures to do the comparison. Note the use of the LIKEDS keyword to create new local data structures with the same format as the SFLSORT data structure. The BASED keywords cause the storage for the two data structures to be that of the data passed to the program as pValue1 and pValue2.

Subfile Sorting: Final Touches

To allow sorting by an individual field within a subfile, you simply store the field name over which you would like to sort the subfile in a global variable. Then, in the MYCOMPARE procedure, you check the value of that field and select the appropriate field for comparison. For example, Figure 3 contains a complete RPG IV program for doing subfile sort. This code illustrates sample routines for sorting a subfile. There's no need to illustrate a complete working program.

Figure 3: This RPG IV code does subfile sorting.

Bob Cozzi has been programming in RPG since 1978. Since then, he has written many articles and several books, including The Modern RPG Language--the most widely used RPG reference manual in the world. Bob is also a very popular speaker at industry events such as RPG World and is the author of his own Web site and of the RPG ToolKit, an add-on library for RPG IV programmers.