Update
This is a better way to sort a data structure array using an update to the SORTA operation code.
Any of you who have followed this blog for any amount of time are aware that I am proponent of using multiple row fetches in SQL to load data into a data structure array, which I will then use to load something like a subfile. In all the examples I have given in the past have been for files/tables that could contain many records/rows more than could be loaded into a load all subfile.
The slowest part of any program is loading data from the file/table. Using a multiple row fetch makes this many times faster by reducing the number of disk I/O to get my desired amount of data, versus getting one record at a time as I would need to do if I was using RPG to read the file/table. Every time I reload the subfile it is slow as I have to get the data from disk. This is especially true if I have a "Position to" field on my display file. If I am using a large file I have to reload the subfile every time the position to field is changed.
If I am using a file/table that will always be less than 9,999 records/rows how can I prevent the need to reload the subfile when I use the position to field?
For this example I create a table of two columns.
01 CREATE TABLE QTEMP.TESTFILE ( 02 KEYVALUE INTEGER, 03 COLUMN1 CHARACTER(30) NOT NULL 04 ) 05 RCDFMT TESTFILER ; |
And loaded it with only odd numbered rows. Why? You will see later.
I am going to take the contents of this table, load it using a multiple row fetch into a data structure array, and then load a subfile from the data structure array.
The display file for the subfile is very simple. I wanted something simple as this article is not about subfiles.
01 A DSPSIZ(24 80 *DS3)
02 A PRINT
03 A ERRSFL
04 A INDARA
05 A CA03(03 'F3=Exit')
*-------------------------------------------------------
06 A R SFL01 SFL
07 A SFLRRN 4S 0H
08 A KEYFIELD 3S 0O 4 4
09 A DESCRIPTN 30A O 4 11
*-------------------------------------------------------
10 A R CTL01 SFLCTL(SFL01)
11 A SFLSIZ(0999)
12 A SFLPAG(0018)
13 A OVERLAY
14 A 31 SFLDSP
15 A 30 SFLDSPCTL
16 A N30 SFLCLR
17 A 30 SFLEND(*MORE)
18 A SFLRCDNBR 4S 0H SFLRCDNBR(CURSOR *TOP)
19 A SEARCHSAVER H REFFLD(KEYFIELD *SRC)
20 A 1 4'Search .'
21 A SEARCHKEY R B 1 13REFFLD(KEYFIELD *SRC)
22 A 3 4'Key Description'
23 A DSPATR(UL)
|
As you can see it just contains two record formats:
- SFL01 the subfile
- CTRL01 the subfile control
It also has a position to field in the subfile control, lines 20-21. I always put a field to contain the last position to field's value in the same record format as the position to field, see line 19.
I am going to break the RPG code into sections to make it easier for me to explain, and, I hope, for you to understand. I start with the definitions.
01 **free
02 ctl-opt main(Simons_Main_Procedure)
option(*nodebugio:*srcstmt:*nounref)
dftactgrp(*no) ;
03 dcl-f TESTDSPF workstn indds(Dspf) sfile(SFL01:SFLRRN) usropn ;
04 dcl-ds Dspf qualified ;
05 Exit ind pos(3) ;
06 SflDspCtl ind pos(30) ;
07 SflDsp ind pos(31) ;
08 end-ds ;
09 dcl-ds Data qualified dim(999) ;
10 Key int(10) ;
11 Col1 char(30) ;
12 end-ds ;
13 dcl-s ArrayKey int(10) dim(%elem(Data)) ascend ;
14 dcl-s Rows uns(10) ;
|
Line 1: I am sure we all love free format RPG.
Line 2: I have broken this into three lines to make it easier to read. As I am using a main procedure I need to have the MAIN keyword. In all my previous examples I have always called the main procedure "Main" for no other reason that I am just lazy. In this example I wanted to give an example of using something different. You will also notice that there is no procedure prototype (DCL-PR) for the main procedure. I have found that unless I am passing parameters to this program/procedure I don't need one. The second part are my favorite control options. As I have procedures in this program I need not to use the default activation group.
Line 3: Definition of my display file. The name should come as no surprise. It has a subfile, I will be opening and closing the file myself, and the indicators will be in a indicator data area.
Lines 4 – 8: My indicator data area.
Lines 9 – 12: This is the data structure array I will be loading from the multiple row fetch. It contains two subfields, like the table, and contains 999 elements.
Line 13: One of the limitations of using data structure arrays is that I can only do a straight look up on the subfields. This doesn't work for what I want. I want the equivalent of a SETLL to the key field subfield. The only way I know to accomplish this is to create a second array that contains just the "key" I will be using to look up. This array's elements are the same size as those of the data structure array's key field subfield, and the array has the same number of elements as the data structures array. I must give the ASCEND keyword so that the compiler knows the values contained within will be in ascending order.
Line 14: I need to define a variable to contain the number of elements in the data structure array, and then the number of rows fetched from the table.
Then there is the main procedure.
15 dcl-proc Simons_Main_Procedure ; 16 exec sql SET OPTION COMMIT = *NONE, CLOSQLCSR = *ENDMOD ; 17 open TESTDSPF ; 18 SFLRCDNBR = 1 ; 19 GetData() ; 20 LoadSfl() ; 21 dow (1 = 1) ; 22 exfmt CTL01 ; 23 if (Dspf.Exit) ; 24 leave ; 25 elseif (SEARCHKEY <> SEARCHSAVE) ; 26 PositionSfl() ; 27 endif ; 28 enddo ; 29 close *all ; 30 end-proc ; |
Line 15: As I mentioned above I wanted to show that the main procedure could have any name, including this one.
Line 16: I add these options for the SQL precompiler to ensure that they are not forgotten when creating the program.
Line 18: The display file field SFLRCDNBR is used when the subfile is displayed. It has to contain a number that is greater than zero and less or equal to the number of records in the subfile. As I will be loading the subfile I want it to start displaying with the first subfile record.
Line 19: I have put the various "parts" of the program into subprocedures. This first subprocedure will get the data I need from the table.
Line 20: Once I have the data the content of this subprocedure will load it into the subfile.
Lines 21: Start of the Do loop.
Line 22: The subfile control record is executed, displayed.
Lines 23 – 24: If F3 is pressed the Do loop is exited.
Lines 25 – 26: This is the part we are interested. If the value in the displayed position to field, SEARCHKEY, is not the same as what is in the saved version, SEARCHSAVE, I execute the subprocedure to do the position to logic.
Line 29: When I exit the Do loop I close all the open files with this one operation. It is a bit unnecessary as there is only one file open, but I am making this a habit.
Line 30: End of the main procedure and program. No indicator LR or return needed.
First step is to fetch the data from the table into the data structure array.
31 dcl-proc GetData ;
32 Rows = %elem(Data) ;
33 exec sql DECLARE C0 CURSOR FOR
SELECT * FROM TESTFILE
ORDER BY KEYVALUE
FOR READ ONLY ;
34 exec sql OPEN C0 ;
35 exec sql FETCH C0 FOR :Rows ROWS INTO :Data ;
36 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ;
37 exec sql CLOSE C0 ;
38 end-proc ;
|
Line 31: All of the subprocedures I use in this program are what I call open subprocedures. There are no procedure interfaces. I can use any of the globally defined variables, those defined before the main procedures. But any variables I define within this subprocedure stays local, only available within this subprocedure.
Line 32: This time I need to set the value of the variable Rows to be the same as the number of elements in the data structure array. This will be used in the fetch statement.
Line 33: This is the definition for the cursor I will be using to get data from the file. In this case I want to fetch all rows from the table TESTFILE, sorted by the values in the column KEYVALUE. And this cursor is used for input/read only.
Line 34: The cursor is opened.
Line 35: This is the multiple row fetch that gets the number of rows given in the variable Rows, which is the same number as elements in the data structure array, and this is moved into the data structure array Data.
Line 36: I retrieve the number of rows that was fetched as there might not have been 999 rows in the table.
Line 37: The cursor is closed.
Line 38: This subprocedure ends.
The next subprocedure loads the subfile.
39 dcl-proc LoadSfl ; 40 Dspf.SflDspCtl = *off ; 41 Dspf.SflDsp = *off ; 42 write CTL01 ; 43 Dspf.SflDspCtl = *on ; 44 for SFLRRN = 1 to Rows ; 45 KEYFIELD = Data(SFLRRN).Key ; 46 DESCRIPTN = Data(SFLRRN).Col1 ; 47 write SFL01 ; 48 ArrayKey(SFLRRN) = Data(SFLRRN).Key ; 49 endfor ; 50 if (SFLRRN > 1) ; 51 Dspf.SflDsp = *on ; 52 endif ; 53 end-proc ; |
Lines 40 – 43: The subfile is initialized.
Line 44: I am using this For group to load the subroutine. This is where I need the number of rows that were fetched from the SQL cursor as I only want to perform this the same number of times of rows fetched.
Lines 45 – 47: Move the values in the data structure subfields to the fields in the subfile, and write the subfile record.
Line 48: I decided it made most sense to load the array I will be using to look up here, as I was already "reading" all of the elements in the data structure array here too. Therefore, I only need to "read" the data structure array once.
Lines 50 – 52: If there was data "read" from the data structure array and written to the subfile then the subfile field SFLRRN will be greater than one, and I want to display the contents of the subfile. If no records were written to the subfile I do not, display the subfile as I will get an error.
The last subprocedure is the focus of this article: how to position the subfile just using the data structure.
54 dcl-proc PositionSfl ; 55 SFLRCDNBR = %lookupge(SEARCHKEY:ArrayKey:1:Rows) ; 56 if (SFLRCDNBR = 0) ; 57 SFLRCDNBR = 1 ; 58 endif ; 59 SEARCHSAVE = SEARCHKEY ; 60 end-proc ; |
Five lines of code is all it takes.
Line 55: Using the look up array the program searches for the value in the display file field SEARCHKEY, in the array, starting at position 1, and going no further that the maximum number of rows loaded into it. If you notice I have used a %LOOKUPGE built in function, this will find the array element that is either equal or greater than the value in SEARCHKEY. Alas, I cannot do this using a data structure array. With that I can only perform a %LOOKUP and find an exact match, which is not what I want. Thus, I use a non-data structure array, ArrayKey that allows me to use the %LOOKUPXX built in functions.
The result of the lookup is moved to the display file field SFLRCDNBR, which means that the subfile will start displaying at the record number contained in that field.
Lines 56 – 58: The %LOOKUPXX should return a number greater than zero, if it does not I will position the subfile to its first record.
Line 59: I "save" the value that was used in the position to, so in the main procedure I can check if the position to field has changed.
What does this all look like?
Here is what the subfile looks like when it is first displayed.
Search . Key Description 001 Counter = 1 003 Counter = 3 005 Counter = 5 007 Counter = 7 009 Counter = 9 011 Counter = 11 013 Counter = 13 015 Counter = 15 017 Counter = 17 019 Counter = 19 021 Counter = 21 023 Counter = 23 025 Counter = 25 027 Counter = 27 029 Counter = 29 031 Counter = 31 033 Counter = 33 035 Counter = 35 |
Only odd numbers were moved to KEYVALUE and inserted into the table. Therefore, the subfile only displays odd numbers.
Now let me enter the value of 200 into the "Search", position to, field and press Enter.
Search . 200 Key Description 201 Counter = 201 203 Counter = 203 205 Counter = 205 207 Counter = 207 209 Counter = 209 |
As I used the %LOOKUPGE the subfile is position to the record next greatest to the value I entered, as there is no 200 row in the table.
The other type of lookup I could have used for this is the %LOOKUPLE built in function. This one will find the array element before the value searched if that value is not in the array. A quick change to the program...
55 SFLRCDNBR = %lookuple(SEARCHKEY:ArrayKey:1:Rows) ; |
And now when I enter 200 into the search I see:
Search . 200 Key Description 199 Counter = 199 201 Counter = 201 203 Counter = 203 205 Counter = 205 207 Counter = 207 |
All of this is really fast as I am not getting data from the table, nor am I reloading the subfile. All I am doing is telling the subfile which record to show at the top of my screen. No subfile records have been deleted, therefore, if I Page Up I can see all the records that have a value of less than 200.
This really shows the benefit of a multiple row fetch and a load all subfile.
This article was written for IBM i 7.3, and should work for some earlier releases too.
I think it far better, and for the sake of performance, to load just one SFL page at a time. When declaring the SELECT, one should use LIMIT and OFFSET, so that it knows (1) the number of rows per SFL page, and (2) what page to present. I think in general (and with few exceptions) its bad form to load many pages or rows in a SFL. And if this is done right, the same Stored Procedure and wrapping sub-proc can be shared and even present JSON'd data to a GUI client. I would investigate LIMIT and OFFSET and also how to keep GUI's (green screen too) as stateless, and all services as stateless too.
ReplyDeleteI think you and I will have to disagree with only getting a range of rows as opposed to getting the lot.
DeleteIt will take a lot to disavow me of my preference for "load-all".
Simon, what if your data access components are to support a green screen SFL and a GUI too? Now you need two code sets? You could use your load-all logic for a remote GUI too but imagine the amount of data forced across the internet, and imagine the hit on performance too. Another problem with the load-all is that rows get stall...you load 20 pages into your SFL and those rows represent data which could be changed while those rows are in your SFL memory. I think the best ways to go are for stateless data services to serve up just nibbles of data as required, and to assume that callers could be remote, and across the internet. In other words, go for speed and economy of data. Its about making services as fast as possible, nimble and snappy. You cannot do that if you load all, and in most cases.
Delete