Data Structure arrays are cool, but have some limitations

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:

• Array data structures
• %LOOKUPxx Look up an array element
• SORTA Sort an array
• %SUBARR Get portion of an array
• %XFOOT Sum array expression elements

 

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