Guru: Load A Varying-Dimension Array With One SQL Fetch

May 11, 2026 Ted Holt

One thing I really like about working in different shops is the vast amount of source code I’m exposed to. But that’s not the best thing. The best thing is the people I meet and get to know. The more clients, the more teachers. Combine source code and people, and my life, personally and professionally, is enriched.

Today I’m pleased to pass along an SQL technique I picked up from the IT shop of a manufacturer. The programmers use this technique to load subfiles from SQL cursors in RPG programs. It works in client-server applications as well. It combines a multi-row fetch with a varying-dimension array. The result is an easy, powerful way to retrieve and manipulate data. Let me show you what I mean.

A multi-row fetch retrieves more than one row from the database into memory in one operation. This type of fetch requires that the for n rows clause be added to the fetch statement, like this:

 Exec Sql
    Fetch C1 for 250 ROWS INTO :ResultSet;

ResultSet is an array of data structures.

In this shop, the programmers had found a way to load a varying-dimension array with one fetch. It goes like this:

Declare and open a cursor.
Use Get Diagnostics to retrieve the number of rows in the result set.
Use the %elem built-in function to set the dimension of the result set array.

And that’s all there is to it. Almost. There is one little wrinkle that can derail the entire process. The cursor must be insensitive.

This story contains code, which you can download here.

Sometimes we forget about the sensitivity options: sensitive, insensitive, and asensitive. This option specifies how current the data needs to be. If you need to see changes as users insert, update, and delete against the underlying tables, you need a sensitive cursor.

For most of the SQL queries I’ve embedded in programs, sensitivity didn’t matter. However, in this case it matters greatly. If the cursor is not insensitive, Get Diagnostics will not return the correct row count.

Once the data has been copied into the array, you have the full set of array options, such as lookup and sorting, at your fingertips.

I’ve prepared a little example program to illustrate how this works.

**free
ctl-opt actgrp(*new) main(PCTL0013R)
        option(*srcstmt: *nounref: *nodebugio)
        debug(*RetVal);

exec sql  set option datfmt=*iso;

dcl-ds  ResultSet  ExtName('PRODUC01') qualified alias dim(*var: 100);
end-ds;

dcl-s  ResultSet_Rows    uns(10) ;

// Sort sequences
dcl-enum SortBy   qualified;
   Order                    'ORDER';
   Customer                 'CUSTOMER';
   DueDate                  'DUEDATE';
end-enum SortBy;

dcl-proc PCTL0013R;
   dcl-pi *n;
      inPartNumber      char(3)     const;
   end-pi;

   MainRoutine (inPartNumber);

end-proc PCTL0013R;

dcl-proc MainRoutine;
   dcl-pi *n;
      inPartNumber      char(3)     const;
   end-pi;

   LoadResultSet (inPartNumber);
   SortResultSet (SortBy.Order);
   SortResultSet (SortBy.Customer);

end-proc MainRoutine;

dcl-proc LoadResultSet;
   dcl-pi *n;
      inPartNumber      char(3)     const;
   end-pi;

   Exec Sql Declare PCTL0013R_1 insensitive Cursor for

      select p.*
        from ProductionView as p
       where p.PartNumber = :inPartNumber
       order by p.DueDate, p.OrderNumber
         for read only;

   Exec Sql  Open PCTL0013R_1 ;

   Exec Sql  Get Diagnostics :ResultSet_Rows = DB2_NUMBER_ROWS ;

   %Elem(ResultSet) = ResultSet_Rows ;

   If ResultSet_Rows > *zero ;
      Exec Sql
         Fetch PCTL0013R_1 for :ResultSet_Rows ROWS INTO :ResultSet;
   EndIf ;

   Exec Sql  Close PCTL0013R_1 ;

end-proc LoadResultSet;

dcl-proc SortResultSet;
   dcl-pi *n;
      inSequence        char(12)   const;
   end-pi;

   select inSequence;
      when-is SortBy.Order;
         SortA ResultSet %Fields(CustomerNumber: DueDate);
      when-is SortBy.Customer;
         SortA ResultSet %Fields(OrderNumber);
      other;
         SortA ResultSet %Fields(DueDate);
   EndSl;

end-proc SortResultSet;

Imagine an end user whose job is to schedule production in a factory. You use this program, PCTL0013R, to retrieve the data for a part that the scheduler wishes to have built. The data is in four tables – sales order header, sales order detail, customer master, and item master – over which the database engineer has built a view, ProductionView, which has the system name PRODUC01.

This is the varying-dimension array of data structures for the SQL result set.

dcl-ds  ResultSet  ExtName('PRODUC01') qualified alias dim(*var: 100);
end-ds;

The alias keyword makes the array have the same fields as the column names in ProductionView. Unless I’m working on an old program that requires the system names in order to function correctly, I typically use the alias keyword to take advantage of longer field names.

Notice that I’ve defined ResultSet as a module-level (global) variable. I have written numerous times about my hatred for global variables, but in some cases it makes sense to declare a variable globally, especially when the entire program centers around that variable.

Here’s the cursor definition.

Exec Sql Declare PCTL0013R_1 insensitive Cursor for
   select p.*
     from ProductionView as p
    where p.PartNumber = :inPartNumber
    order by p.DueDate, p.OrderNumber
      for read only;

Exec Sql  Open PCTL0013R_1 ;

The cursor is defined as insensitive. If you wish, you can also add scroll, but I’ve never felt that I needed a scrollable cursor, as I only access the data once. I’ve added the for read only clause to the select. I don’t know if that’s necessary or not, but the programmers in that shop say that it helps. I think it may give the SQL engine more information to make a better decision. Better safe than sorry.

Now that the insensitive cursor is open, Get Diagnostics knows how many rows will be in the result set. Set the dimension of the array.

%Elem(ResultSet) = ResultSet_Rows ;

Now you can use the built-in %ELEM function as needed and won’t be needing %SUBARR quite as often.

By the way, whenever you need to see the current dimension of the array in the debugger, look at the _QRNU_VARDIM_ELEMS_RESULTSET variable. Replace RESULTSET with the name of your array.

Fetch the rows.

If ResultSet_Rows > *zero ;
   Exec Sql
      Fetch PCTL0013R_1 for :ResultSet_Rows ROWS INTO :ResultSet;
EndIf ;

You’re finished using the cursor. Close it. The data is in the array and you can do whatever you wish with it. Since the shop in question uses a green-screen interface, their programs load the array into a subfile, sometimes after sorting the array. This example program has no user interface. Such a program can easily pass data to a calling program that has no database I/O.

I mentioned lookup and sorting. You can use any array operation. I added a sort routine to my example program.

// Sort sequences
dcl-enum SortBy   qualified;
   Order                    'ORDER';
   Customer                 'CUSTOMER';
   DueDate                  'DUEDATE';
end-enum SortBy;

SortResultSet (SortBy.Order);
SortResultSet (SortBy.Customer);

dcl-proc SortResultSet;
   dcl-pi *n;
      inSequence        char(12)   const;
   end-pi;

   select inSequence;
      when-is SortBy.Customer;
         SortA ResultSet %Fields(CustomerNumber: DueDate);
      when-is SortBy.Order;
         SortA ResultSet %Fields(OrderNumber);
      other;
         SortA ResultSet %Fields(DueDate);
   EndSl;

end-proc SortResultSet;

I used an enumeration to define and group the sort sequence constants. Using named constants instead of quoted literals means the compiler will catch any spelling errors. I continue to look for effective ways to use enumerations.

The downloadable code file contains this program plus some SQL to build the example data, in case you’d like to play with this technique. The RPG program includes a DumpResultSet subprocedure, which I often include for development, testing, and debugging, but which the client programs don’t usually call. Also, be aware that I omitted error-handling code. In a production program, I would check the SQLSTATE variable as required.

I am grateful to Mike Odom for introducing me to this method of data retrieval.

As always, please share with me your experiences, especially improvements to what I’ve published or mistakes I’ve made. You can reach me through the IT Jungle Contact page.

Ted Holt is the original, the one and only, chief of the Four Hundred Gurus. We are glad he is back with us writing technical material that helps IBM i programmers. He is a self-employed, independent programmer living near Tupelo, Mississippi, who is old enough to retire but is not ready to do so. He still enjoys programming and is available to help others as needed. He welcomes your comments, questions, and suggestions.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search