Wednesday, September 30, 2015

Data Structure arrays are cool, but have some limitations

data structure lookup subarr xfoot

I have given examples of Data Structure arrays in other posts, and while I use them I have found a few frustrating gotchas. Data Structures arrays were introduced in V5R1, back in 2001. In my opinion they are a more mature approach to handling data within a Data Structure than multiple occurrence Data Structures. And it surprised me how few of my colleagues knew about them.

Coding them is simple, I just have to add the DIM keyword on the line that defines the Data Structure with the number of elements I desire:

  dcl-ds Array qualified dim(9999) ;
    SubField1 char(7) ;
    SubField2 packed(9) ;
  end-ds ;

The equivalent in fixed format can be seen here.

I refer to the data structure subfields with the Array/Data Structure name and the array element. For example:

  Array(1).SubField1 = 'FIRST' ;
  Array(1).SubField2 = 213 ;
  Array(2).SubField1 = 'SECOND' ;
  Array(2).SubField2 = 5963 ;

What are the common things I would perform on an array:

  1. Lookup – Find an element that contains a value.
  2. LookupXX – Lookup with a partial string, like using the SETLL operation.
  3. Sort array elements.
  4. Total all of the elements in an array.

Before I go and explain what I have listed above I need to fill my array. To do this I am using a SQL View, as I described in the post Build Views and Views of Views. You will notice that I have used one of the Views mentioned in that post, VIEW1, for the source of the data.

01  dcl-ds Array qualified dim(9999) ;
02    OrderNbr char(7) ;
03    VendorNbr char(6) ;
04    VendorName char(20) ;
05    EnteredDate date(*mdy) ;
06    DueDate date(*mdy) ;
07    OrderStatus char(8) ;
08    TotalQty packed(9) ;
09  end-ds ;

10  dcl-s Array2 char(20) dim(%elem(Array)) ascend ;
11  dcl-s Array3 packed(9) dim(%elem(Array)) ascend ;

12  dcl-s wkMaxSize packed(5) inz(%elem(Array)) ;
13  dcl-s wkRtvRows like(wkMaxSize) ;
14  dcl-s i like(wkMaxSize) ;
15  dcl-s wkTotal packed(11) ;

17  Array(*) = *allx'FF' ;

18  exec sql SET OPTION COMMIT = *NONE ;

19  exec sql DECLARE C0 CURSOR FOR
              SELECT * FROM MYLIB/VIEW1
               ORDER BY ORDER_NBR
                 FOR READ ONLY ;

20  exec sql OPEN C0 ;

21  exec sql FETCH NEXT FROM C0 FOR :wkMaxSize ROWS INTO :Array ;
22  SQLCOD = SQLCOD ;

23  wkRtvRows = SQLER3 ;

24  exec sql CLOSE C0 ;

The fixed format equivalent of the definitions can be seen here.

The Data Structure array, and its subfields, are defined in lines 1 – 9. Followed by two other arrays, on lines 10 and 11, I will describe what I use them for later.

Then come four work variables, lines 12 – 15. Note that the variable wkMaxSize is initialized to contain the number of elements of the Data Structure array. The purpose of the others will become clear later.

On line 17 I set the entire array to be the hexadecimal value 'FF'. I will not say why here, but I will refer you to When %LOOKUP *NE LOOKUP which does explain why.

Then comes the SQL part of this program. When I declare the cursor, line 19, I am saying I want all field columns from VIEW1 in order number sequence, and I will not be updating the cursor.

The Fetch, line 21, takes the number of rows in the variable wkMaxSize, 9999, in one go and puts them into the array named at the end of the statement, Array. This is a lot faster than reading or fetching one record/row at a time.

I also know how many rows have been retrieved from the View thanks to the SQL subfield SQLER3. I move that value to a variable with a more meaningful name, wkRtvRows, on line 23.

In this example the Array only contains few rows:


Order    Vendor  Vendor            Order entered  Order due  Order processing  Total order
number   number  name              date           date       status            quantity   
40605    1176    CONOR ELECTRIC      02/15/15     03/14/15       COMPLETE               15
4121     1146    ANZAC CLASS         07/20/15     01/31/15       PARTIAL             2,000
4811     1147    BOEING              07/14/15     10/01/15       OPEN                1,500
4942     1146    ANZAC CLASS         08/19/14     06/20/15       COMPLETE              750
60401    1112    DAHOMEY             08/02/15     10/31/15       OPEN                  700
60404    1251    EAGLE               07/24/15     08/31/15       PARTIAL                25

Performing a Lookup is simple:

  i = %lookup('4811':Array(*).OrderNbr:1:wkRtvRows) ;

  i = %lookup('EAGLE':Array(*).VendorName:1:wkRtvRows) ;

The first lookup finds the order number '4811' in the third element of the Data Structure array subfield OrderNbr. Notice that the format for the subfield is: Array name, (*) which means the entire array, and the subfield name. I have to use (*) whenever I want to use a subfield of the array.

The second lookup finds 'EAGLE' in the sixth element of the VendorName subfield.

Now I want to rearrange the array into Vendor Name order. Use SORTA I hear you say, yes I will but there is a nice Built in Function called %SUBARR allows me to choose which part of the array to sort. The obvious part is the part we loaded from the View. My SORTA will look like:

  sorta %subarr(Array(*).VendorName:1:wkRtvRows) ;

This means that I am sorting the array subfield VendorName, from the first to the number of rows I retrieved from the view. It is not just the VendorName subfield is sorted, the whole array is sorted.

When I now look for 'BOEING':

  i = %lookup('BOEING':Array(*).VendorName:1:wkRtvRow) ;

I find it in the third element of the array:

ARRAY.ORDERNBR(3) = '4811   '
ARRAY.VENDORNBR(3) = '1147  '
ARRAY.VENDORNAME(3) = 'BOEING              '
ARRAY.ENTEREDDATE(3) = '07/14/15'
ARRAY.DUEDATE(3) = '10/01/15'
ARRAY.ORDERSTATUS(3) = 'OPEN    '
ARRAY.TOTALQTY(3) = 000001500.

Using a LookupXX works great in an array that is not a Data Structure array, as I showed in When %LOOKUP *NE LOOKUP. But I cannot use a %LOOKUPXX unless the array is defined with either the ASCEND or DESCEND keyword. This is where Array2 comes into play.

I want to use a %LOOKUPGE on the array and find the element that first matches the pattern I use. This will be the letter 'C'.

I cannot perform the %LOOKUPGE on the array subfield as I cannot put the ASCEND keyword on that line. I have to define another array, Array2, and move the data from the subfield to other array. Below is the only way I could think to move the values from the Vendor name subfield into Array2:

    dcl-s Array2 char(20) dim(%elem(Array)) ascend ;

01  Array2(*) = x'FF' ;

02  for i = 1 to wkRtvRows ;
03    Array2(i) = Array(i).VendorName ;
04  endfor ;

05  i = %lookupge('C':Array2:1:wkRtvRows) ;
06  i = %lookuple('C':Array2:1:wkRtvRows) ;

I have defined Array2 to be 20 characters as that is the size of the largest character field in the Data Structure array, therefore, it can be used for any of the character subfields. It has the same number of elements as the Data Structure array.

Before moving data from one array to another I populate all the elements of Array2 with hexadecimal 'FF', line 1. Without this the lookup can give unexpected results. See here.

I use a FOR group to move the data from the subfield to Array2, lines 2 – 4. I only have to perform this the number of times as the number of rows retrieved from the View. If you have not used a FOR group see FOR replaces DO in RPGLE

On lines 5 and 6 I have two lookups: %LOOKUPGE and %LOOKUPLE. In both case my search is for 'C' in Array2 in only those elements I populated in the FOR group, which is from the first element to the element of the same number as contained in wkRtvRows.

The value of i for the %LOOUPGE is 4 as the first element that is greater or equal to 'C' is the fourth element that contains 'CONOR'.

The value of i for the %LOOUPLE is 3 as the first element that is less or equal to 'C' is the third element that contains 'BOEING'.

If you know of a better way of moving the data from the array subfield to another array please let me know. You can either use the Contact Form, which is in the right column, or post a comment below.

Having sorted one of the subfields in ascending order I can do the opposite, sort in descending order, to a different subfield:

  sorta(d) %subarr(Array(*).TotalQty:1:wkRtvRows) ;

The operation extender 'D' is used to denote that the subfield (and with it the rest of the Data Structure) should be sorted in descending order. Again I have used the %SUBARR as I only care to sort the elements I added to the array.

  ARRAY.TOTALQTY(1) = 000002000.
  ARRAY.TOTALQTY(2) = 000001500.
  ARRAY.TOTALQTY(3) = 000000750.
  ARRAY.TOTALQTY(4) = 000000700.
  ARRAY.TOTALQTY(5) = 000000025.
  ARRAY.TOTALQTY(6) = 000000015.

The remaining common function I have not mentioned yet is the totaling of the values in an array. I am sure you all said XFOOT. The operation code XFOOT has been replaced by the built in function %XFOOT, which does the same. Alas, we cannot %XFOOT an array subfield, so I would have to copy the data from the TotalQty subfield to Array3:

  dcl-s Array3 packed(9) dim(%elem(Array)) ascend ;

  for i = 1 to wkRtvRows ;
    Array3(i) = Array(i).TotalQty ;
  endfor ;

  wkTotal = %xfoot(%subarr(Array3:1:wkRtvRows)) ;

For me that is extra processing. In my opinion this is a simpler way to achieve the same outcome:

  wkTotal = 0 ;

  for i = 1 to wkRtvRows ;
    wkTotal += Array(i).TotalQty ;
  endfor ;

I am sure you will find Data Structures arrays a useful addition to your programming. The examples I have given show how I sort the data in many ways without the need for file I/O, making my programs more versatile and faster.

 

You can learn more about these on the IBM website:

 

This article was written for IBM i 7.2, and should work for earlier releases too.


Example of Data Structure array in fixed format

D Array           DS                  qualified dim(9999)
D  Field1                        7
D  Field2                        9P 0

Return


Fixed format definitions

01  D Array           DS                  qualified dim(9999)
02  D  OrderNbr                      7
03  D  VendorNbr                     6
04  D  VendorName                   20
05  D  EnteredDate                    D   datfmt(*mdy)
06  D  DueDate                        D   datfmt(*mdy)
07  D  OrderStatus                   8
08  D  TotalQty                      9P 0

09  D Array2          S             20    dim(%elem(Array)) ascend
10  D Array3          S              9P 0 dim(%elem(Array)) ascend

11  D wkMaxSize       S              5P 0 inz(%elem(Array))
12  D wkRtvRows       S                   like(wkMaxSize)
13  D i               S                   like(wkMaxSize)
14  D wkTotal         S             11P 0
     /free

Return

9 comments:

  1. arrays make me nervy cos you never know when data will be too big to fit. So there is always the user index. runs quicker than a keyed file in QTEMP

    ReplyDelete
  2. Anytime I see reference to SQLER3 to retrieve the number of row I always feel compelled to encourage the use of "get diagnostics" command instead. I believe it is IBM's preferred method (from a COMMON session) and it's much more readable:
    exec sql get diagnostics :wkRtvRows = row_count;
    vs
    wkRtvRows = SQLER3;

    SQLER3 is too cryptic.

    Thanks and love the blog!

    ReplyDelete
  3. Instead of selecting all columns (fields) from MYLIB/VIEW1, what if i have to select, say 100 fields? In this scenario, is there a better way to define subfields for DS or should I define 100 subfields?

    DECLARE C0 CURSOR FOR
    SELECT * FROM MYLIB/VIEW1
    ORDER BY ORDER_NBR
    FOR READ ONLY ;

    ReplyDelete
    Replies
    1. Build a View of only the columns you want to use, then use that View to be the external definition for the data structure.

      Delete
  4. Hi Simon
    I am looking to know how to pass an array data struct to a string

    ReplyDelete
    Replies
    1. I think this should work:

      Variable = DataStructureName ;

      You will, of course, have to give your data strucutre a name when you define it.

      Delete
  5. Part of the problem can be solved by using OVERLAY :
    dcl-ds PBV qualified static;
    recordPBV Char(77) Dim(maxCountPBV) descend; // Inz(*Blanks);
    IDR Char(7) overlay(recordPBV:1);
    NMS Char(70) overlay(recordPBV:8);
    END-DS;
    Then
    SortA %subarr(PBV.recordPBV:1:countRec);
    And further
    recn = %lookup(iIDR: PBV.IDR: 1: countRec);
    Of course, this can not be called a full-fledged replacement, but still ...
    Of course, I would like something like this:
    dcl-ds DS qualified dim(100) ascend(DS.key);
    key char(10);
    value char(10);
    end-ds;

    ReplyDelete
  6. Hi.. can I use a data structure array inside an SQL where clause ? For example I have a table which has a column of department. I have a data structure array with a field department. I want to select all records from the table with department in the list of departments in the array

    ReplyDelete
    Replies
    1. I have been working on this, and talking to IBM-ers on what is the best approach. It is such a good question I will be making my answer its own post.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.