Wednesday, February 6, 2019

Screen at a time subfile using SQL

screen at a time subfile written using sql

In my last post I gave an example of how to write a "screen at a time" subfile in RPG. When I finished writing that post I thought to myself how would it be possible to do the same using SQL.

There are two ways I could read, Fetch, data from the input file:

  1. Single row fetch
  2. Multiple row fetch

The single row Fetch is almost as inefficient as a RPG read operation, retrieving one record at a time from the file. The multiple row fetch is more efficient and the method I am using in this example.

The data file this program uses, PERSONP, is the same as in my last post, therefore, I am not going to describe it. If you care to refresh your memory then you should go to that post.

I made three changes to the display.

01 A                                      DSPSIZ(24 80 *DS3)
02 A                                      PRINT
03 A                                      INDARA
04 A                                      ERRSFL
05 A                                      REF(PERSONP)
06 A                                      CA03(03 'F3=Exit')
07 A                                      CA05(05 'F5=Refresh')

08 A          R SFL01                     SFL
09 A            ZRRN           2S 0H
10 A            NAME          40A  O  5  3
11 A            LASTNAME  R        H
12 A            FIRSTNAME R        H
13 A            ARRAYELEM      8S 0H

14 A          R CTL01                     SFLCTL(SFL01)
15 A                                      SFLSIZ(0010)
16 A                                      SFLPAG(0010)
17 A                                      OVERLAY
18 A                                      PAGEDOWN(25)
19 A                                      PAGEUP(26)
20 A  30                                  SFLDSPCTL
21 A  31                                  SFLDSP
22 A  32                                  SFLEND(*MORE)
23 A  33                                  SFLCLR
24 A                                  1  8'R P G P G M . C O M'
25 A                                  2  2'Screen at a time subfile +
26 A                                      'example'
27 A                                  3  2'Position to'
28 A            ZPOSITION R        B  3 14REFFLD(LASTNAME)
29 A                                  4  3'Person name'

30 A          R FOOT01
31 A                                 23  3'F3=Exit   F5=Refresh'

Line 7: The F5 key will be used by the RPG program to say that the user wants to reload the data from the file.

Line 13: I added a hidden field in the subfile record format to contain the array element the subfile record was loaded from.

Line 31: As I added another function key I added it to the FOOTER record format so that the users would know that it is available.

If you have heard my previous post this program will look familiar. Let me start by describing the definitions.

001  **free
002  ctl-opt main(Main)
003            option(*nodebugio:*srcstmt:*nounref)
004            dftactgrp(*no) ;

005  dcl-f DSPFILE workstn indds(Dspf)
006                  sfile(SFL01:ZRRN) usropn ;

007  dcl-ds Dspf qualified ;
008    Exit ind pos(3) ;
009    Reload ind pos(5) ;
010    PageDown ind pos(25) ;
011    PageUp ind pos(26) ;

012    SflInds char(4) pos(30) ;
013      SflDspCtl ind pos(30) ;
014      SflDsp ind pos(31) ;
015      SflEnd ind pos(32) ;
016      SflClr ind pos(33) ;
017  end-ds ;

018  dcl-ds Data qualified dim(32766) ;
019    Last char(30) ;
020    First char(25) ;
021    Name char(40) ;
022  end-ds ;

023  dcl-s DataLookup char(30) dim(%elem(Data)) ascend ;

024  dcl-s SflSize like(ZRRN) inz(10) ;
025  dcl-s LastRecord packed(8) ;
026  dcl-s Pos packed(8) ;
027  dcl-s SavedPosition like(ZPOSITION) ;

Lines 1: In 2019 why would I not write in free format RPG?

Lines 2 – 4: In my control options I am "flagging" that this program uses a Main procedure, line 2. I add the control options on line 3 to all my RPG as they debugging easier. And I need line 4 as this program contains subprocedures.

Lines 5 - 6: This is the only file I need to define, the display file. Notice that it has an indicator data structure, INDDS, on line 5. And that the USROPN on line 6 means I will be opening and closing this file.

Lines 7 – 17: This is the indicator data structure, which allows me to map the number indicators used in the display file to meaningful names. The only change to this data structure, when compared to the one I used in the previous post, is the addition of indicator 5, line 9, which is the indicator used by the F5 key in the display file.

Lines 18 – 22: This data structure array will be used by the SQL Fetch statement to contain the rows retrieved from the file. In this example having 32,766 elements is excessive, but I wanted to show what the largest size this data structure array can be. In IBM i 7.3 an array can have up to 16,773,104 elements (and you thought 32,766 was excessive!), the size limit is from the SQL Fetch. The documentation states that a Fetch can retrieve up to 32,767 rows, but I found I was getting SQL compiler errors with that number. When I reduced the number by one, the code compiles.

Line 23: As this subfile program is going to have a "position to" field on the display file I need to have a way to perform a lookup in the data structure array for the closest matching pattern. To be able to achieve this standalone array needs to be sorted in either ascending or descending order. I have not found how to do that with a data structure array, therefore, this separate array will contain the "position to" key, last name. This array can then be searched for the closest matching element.

Lines 24 – 27: Definitions of various variables I will be using in this program.

Line 24: This variable contains the same number as there are elements in the data structure array. If I ever change the number of records in the subfile record format I only need to change the number here, rather than have to search the source.

Line 25 and 26: PACKED(8) is excessive for the array I have coded here. In the future if I change this program so the data structure array has the maximum allowable number of elements then that number can be accommodated in these fields.

In the control option, line 2, I said there was going to be a Main procedure, so here it is:

028  dcl-proc Main ;
029    exec sql SET OPTION CLOSQLCSR = *ENDMOD ;

030    open DSPFILE ;
031    GetData() ;
032    SubfileDown() ;

033    dow (1 = 1) ;
034      write FOOT01 ;
035      exfmt CTL01 ;

036      if (Dspf.Exit) ;
037        leave ;
038      elseif (Dspf.Reload) ;
039        GetData() ;
040        SubfileDown() ;
041      elseif (Dspf.PageDown) ;
042        if not(Dspf.SflEnd) ;
043          SubfileDown() ;
044        endif ;
045      elseif (Dspf.PageUp) ;
046        SubfileUp() ;
047      elseif (ZPOSITION <> SavedPosition) ;
048        Position() ;
049      endif ;
050    enddo ;

051    on-exit ;
052      close *all ;
053  end-proc ;

Line 29: I always put SQL options into my source code to make sure they are not "forgotten" at compile time. This time I only need the option that says that any open cursors will be closed at the end of the module (program).

Line 30: I open the display file.

Line 31: This is the subprocedure that fetches the data from the file.

Line 32: I think the name of the subprocedure is descriptive of its function. It loads the subfile with the first set of records.

Lines 33 – 50: This is the "guts" of the program where all the hard work is done and called from. The only way to leave the Do-loop is to press the F3 key.

Lines 34 and 35: The bottom part of the subfile, which displays the function keys text, is written. Followed by the subfile control record being EXFMT-ed, which shows the subfile on the screen.

Lines 36 and 37: When F3 is pressed the Do-loop is left.

Lines 38 – 40: If the F5 key is pressed the data structure array is reloaded, by GetData, and the subfile is reloaded too, by SubfileDown.

Lines 41 – 44: When the Page Down key is pressed if the subfile end indicator is on there are no more array elements to display, therefore, the SubfileDown subprocedure is not called. Of course if the end of subfile has not been encountered the subprocedure is called.

Lines 45 and 46: When the Page Up key is pressed the SubfileUp subprocedure is called.

Lines 47 and 48: If a value is entered into the "position to" field on the display file then the Position subprocedure is called.

Lines 51 and 52: I am using the ON-EXIT as it is performed at the end of procedure even if there is an error occurs within the procedure, or it is cancelled. This way the open display file is always closed, none of that mess of having files left open when the program ends in error.

The first subprocedure I need to show gets the data from the file and into the data structure array.

054  dcl-proc GetData ;
055    dcl-s Elements packed(8) inz(%elem(Data)) ;

056    exec sql DECLARE C0 CURSOR FOR
058                    RTRIM(LASTNAME) || ', ' || FIRSTNAME
059               FROM PERSONP
061                FOR READ ONLY ;

062    exec sql OPEN C0 ;

063    exec sql FETCH C0 FOR :Elements ROWS INTO :Data ;
064    exec sql GET DIAGNOSTICS :LastRecord = ROW_COUNT ;

065    exec sql CLOSE C0 ;

066    DataLookup(*) = Data(*) ;
067    Pos = 0 ;
068    ZPOSITION = ' ' ;
069    SavedPosition = ' ' ;
070  end-proc ;

Line 55: I need this variable when I fetch the rows/records from the file. This variable is initialized with the number of elements in the data structure array, therefore, if I ever change the number of elements in the array I know I don't have to change anything here.

Lines 56 – 61: Definition of my SQL cursor.

Lines 57 and 58: The results will consist of three columns/fields:

  1. Last name
  2. First name
  3. Full name. I have used the RTRIM to remove trailing blanks from the string.

I am sure some SQL programmers are going to criticize me for using the double pipes ( || ) as the concatenation characters.

Line 60: I want the results to be fetched in last name and first name order.

Line 61: FOR READ ONLY is the equivalent of defining a file as input only.

Line 62: Once the cursor is defined it has to be opened before it can be used.

Line 63: Now the rows/records can be fetched from the cursor. This is where I am using the Elements variable for the number of rows I want to fetch from the cursor. The fetched rows are placed in the data structure array.

Line 64: I need to know how many rows were fetched from the cursor. The Get diagnostics allows me to do that in a simple SQL statement.

Line 65: I am done with fetching data from the cursor, therefore, I close it.

Line 66: I am moving the 30 left most characters, the last name subfield, from the data structure array into the standalone array I will be using for "position to".

Lines 67 – 69: Initializing the variables I will use later in the program to control the subfile, etc.

The subprocedure performed when the Page Down key is pressed is just three lines of code.

071  dcl-proc SubfileDown ;
072    LoadSubfile() ;
073  end-proc ;

The subprocedure executed when the Page Up keyed is pressed has just one more line of code. This line determines where in the data structure array the next subfile screen should start at, line 75. The start position is all relative to the last array element added to the subfile. As the subfile may not have all its records filled I use the subfile's relative record field, ZRRN, minus 1, and the size of the subfile variable.

074  dcl-proc SubfileUp ;
075    Pos -= (SflSize + (ZRRN - 1)) ;
076    LoadSubfile() ;
077  end-proc ;

Now I am at the place in the data structure array I need to be I can load the subfile.

078  dcl-proc LoadSubfile ;
079    Dspf.SflInds = '0001' ;
080    write CTL01 ;
081    Dspf.SflInds = '1000' ;

082    if (Pos < 1) ;
083      Pos = 1 ;
084    elseif (Pos > LastRecord) ;
085      Pos = LastRecord ;
086    endif ;

087    for ZRRN = 1 to SflSize ;
088      NAME = Data(Pos).Name ;
089      LASTNAME = Data(Pos).Last ;
090      FIRSTNAME = Data(Pos).First ;
091      ARRAYELEM = Pos ;
092      write SFL01 ;

093      if (Pos = LastRecord) ;
094        leave ;
095      else ;
096        Pos += 1 ;
097      endif ;
098    endfor ;

099    if (ZRRN > 0) ;
100      Dspf.SflDsp = *on ;
101    endif ;

102    if (ARRAYELEM = LastRecord) ;
103      Dspf.SflEnd = *on ;
104    endif ;
105  end-proc ;

Lines 79 – 81: This initialized the subfile, and sets the indicators that the subfile control will be displayed.

Lines 82 – 86: This ensures that the variable I will be using for the data structure array element has a valid value in it. If it is less than 1, then it is set to 1. If its value is greater than the number of the elements fetched into the array it is set to the value of the maximum number of the elements.

Lines 87 – 98: I am using a For-loop to be performed the same number of times as number of records in the subfile, the value in the variable SflSize.

Lines 88 – 92: The values from the data structure array element is moved to the subfile's fields, and then written to the subfile.

Lines 93 and 94: If the array element variable is equal to the number of rows fetched from the cursor then it is time to leave the For-group.

Lines 95 and 96: If it has not I increment the array element variable.

Lines 99 – 101: When the subfile has been load the subfile display indicator is turned on if the subfile contains at least one record.

Lines 102 – 104: If the array element number in the last of the subfile's records is the same as the number of rows fetched from the cursor turn of the end of subfile indicator.

The last subprocedure is executed when the "position to" field on the display file is changed.

106  dcl-proc Position ;
107    Pos = %lookupge(ZPOSITION:DataLookup:1:LastRecord) ;
108    LoadSubfile() ;
109    SavedPosition = ZPOSITION ;
110 end-proc ;

Line 107: This is where the stand alone array is used. I am using the %LOOKUPGE built in function. It acts like a the SETLL operation code in RPG. In this case it returns the number of the first element where the search string is either or equal or greater.

Line 108: The subfile can be loaded from that point.

Line 109: The value in the "position to" field is moved to another variable, therefore, I can tell when the "position to" has been changed.

After compiled the program when I call it the screens look exactly the same as the ones in the previous post, but I am sure I can notice a difference in speed with the subfile being re-displayed faster in this program that it was in the previous post's program.

      R P G P G M . C O M
Screen at a time subfile example
Position to                               
 Person name
      R P G P G M . C O M
Screen at a time subfile example
Position to                               
 Person name
      R P G P G M . C O M
Screen at a time subfile example
Position to                               
 Person name
      R P G P G M . C O M
Screen at a time subfile example
Position to                               
 Person name


When I enter a value in the "position to" I get to see the closest match at the top of the subfile.

      R P G P G M . C O M
Screen at a time subfile example
Position to SIL                           
 Person name


I can even page up to before the "position to" I entered.

      R P G P G M . C O M
Screen at a time subfile example
Position to SIL                           
 Person name

I am still stumped why this is a good idea.


After showing written these two examples if I ever had to use a "screen at a time" subfile I would use the method described in this post, rather than the purely RPG approach shown in the previous post.


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


  1. If I read you right, additional memory required to hold Data element when the program loaded. Assuming 1000 sessions/users open the program same time, will this depreciate the memory allocated and eventually poor down performance?

  2. I guess if you have a low-end machine you might see a performance degradation.

    1. Do remember these are example programs. Most of the code here is very simple I do not use it as is in my work.

  3. Simon,
    What if in addition to the block fetch, no doubt a good approach, also the cursor is defined as and scrollable cursor?
    In this way the cursor can also manage page up/down without continuously closing/opening it.

    1. Other example of how to SQL and subfiles will be coming in the future (when I get some time to write them).

  4. Hey Simon,
    I really like this way of processing a screen at a time using SQL. But I am running into record locks and the program bombs which result in mad users.
    Have you or anybody else run into this problem. If so, how does one get around it. Thank you in advance.

    1. Try using FOR READ ONLY in your cursor's definition, then the cursor is only opened in "input only mode".

  5. Hi Simon,

    Did what you recommended. It didn't bomb, but no data displayed on the screen either.

    Making progress believe it or not.


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.