Wednesday, April 8, 2015

SQL blocking fetches, getting more than one row at a time

fetch for rows sql

In an earlier post I described how to perform "record level access" using SQL, i.e. getting data from a table or file one row or record at a time, much like reading a file using RPG. But you can do more with SQL by using blocking fetches, getting a number of rows/records at once. As database I/O is a relative slow process, by using block fetching decreases the number of I/Os needed and this can make your programs faster.

The difference between using the FETCH NEXT for one row/record at a time versus the blocking fetch is adding the FOR x ROWS to the fetch statement:


For this example I have a very simple DDS file. I could have used a SQL table instead. But as most of us are still working with applications that have DDS files, rather SQL tables, I decided to use a file. Regular readers will not be surprised to learn that the file is called TESTFILE, and it contains two fields:

  A          R TESTFILER
  A            FLD1           3P 0
  A            FLD2           5A

The file contains 16 records:

        FLD1   FLD2
     01    0   0
     02    1   1
     03    2   2
     04    3   3
     05    4   4
     06    5   5
     07    6   6
     08    7   7
     09    8   8
     10    9   9
     11   10   10
     12   11   11
     13   12   12
     14   13   13
     15   14   14
     16   15   15

And now for the code:

01  dcl-ds TestFile_ds extname('TESTFILE')
                         qualified dim(10) ;
02  end-ds ;
03  dcl-s NbrOfRows int(5) inz(%elem(TestFile_ds)) ;
04  dcl-s i packed(2) ;

05  exec sql DECLARE C0 CURSOR FOR

06  exec sql OPEN C0 ;

07  dow (1 = 1) ;
08    clear TestFile_ds ;

09    exec sql FETCH C0 FOR :NbrOfRows ROWS
                INTO :TestFile_ds ;
11    if (SQLCOD <> 0) ;
12      leave ;
13    endif ;

14    for i = 1 to 10 by 1 ;
15      dsply ('Position in array ' + %char(i) +
               ' contains ' + TestFile_ds(i).FLD2) ;
16    endfor ;
17  enddo ;

18  *inlr = *on ;
19  exec sql CLOSE C0 ;

When I perform the blocking fetch I place the data into a data structure array, line 1. By using the EXTNAME keyword means that the data structure contains all the subfields that are fields in the file. As I am going to be dealing with so few records I am only going to define the array 10 elements, DIM(10). As this is a free format data structure I need to give the END-DS at the end of its definition, line 2.

Rather than hard code the number of rows to fetch in the FETCH statement I am going to use a variable which is initialized with the number of elements in the data structure array, see line 3. In the future if I decide to increase the size of the array I only need to change the DIM.

Line 4 is a definition of a numeric field I will be using in a FOR loop. If you have not used a FOR before see here.

If I had to use fixed format the definitions I have described above would look like:

    D TestFile_ds   E DS                  extname('TESTFILE')
    D                                       qualified  dim(10)
    D NbrOfRows       S              5I 0 inz(%elem(TestFile_ds))
    D i               S              2  0 inz

The next step is to define the SQL cursor, see line 5. I want to fetch all the columns/fields from TESTFILE and receive them in order by FLD1. Having defined the cursor I have to open it, line 6.

I have placed the FETCH within a DO loop, line 7, to retrieve all the rows/records from the file.

The FETCH statement, line 9, retrieves the number of rows given in the variable NbrOfRows from the file and writes them to the data structure array, TestFile_ds, which is why the number of rows has to be less or equal to the number of elements in the array.

When there are no more rows to retrieve the internal variable SQLCOD is changed to not be zero, line 11, and I can leave the DO loop, line 12.

This brings up the issue of what happens if the last fetch does not fill the array? I would have an array that would contain a mixture of new data that had just been fetched, and data left over from the previous fetch. This why I have a statement to clear the array, line 8, before I perform the fetch, this way if the fetch only retrieved two rows the other eight elements of the array will be the field type's default value.

Lines 14 to 16 are just there to display the contents of the array, which would be:

  DSPLY  Position in array 1 contains 0
  DSPLY  Position in array 2 contains 1
  DSPLY  Position in array 3 contains 2
  DSPLY  Position in array 4 contains 3
  DSPLY  Position in array 5 contains 4
  DSPLY  Position in array 6 contains 5
  DSPLY  Position in array 7 contains 6
  DSPLY  Position in array 8 contains 7
  DSPLY  Position in array 9 contains 8
  DSPLY  Position in array 10 contains 9

  DSPLY  Position in array 1 contains 10
  DSPLY  Position in array 2 contains 11
  DSPLY  Position in array 3 contains 12
  DSPLY  Position in array 4 contains 13
  DSPLY  Position in array 5 contains 14
  DSPLY  Position in array 6 contains 15
  DSPLY  Position in array 7 contains
  DSPLY  Position in array 8 contains
  DSPLY  Position in array 9 contains
  DSPLY  Position in array 10 contains

Having exited the DO loop I set on *INLR to flag that this is the end of the program, line 18, and then close the cursor, line 19. You could easily reverse the order of these two lines. It is just my personal preference to do it this way.

With a larger file I would increase the number of elements of the array, but the rest of this example program's logic could be used with little or no change.

I would like to thank Buck Calabro for his message, as it was the seed from which this post grew.


You can learn more about these on the IBM website:


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


  1. It has been said by someone on the Midrange boards regarding embedded SQL: 'If you're using a cursor, you're doing it wrong.' Why, what's wrong with using a cursor? Maybe a topic for your next post.

  2. I think the quote is misremembered. Nevertheless, the point is that if we're using SQL to emulate SETLL, READE that is a red flag. This is because SQL is set oriented, and the more we operate on sets of data at a time, the better SQL will perform.

  3. One more point SQL cursors gives the advantage to fetch records based on any flelds in a table , it is not necessary they are key fields unlike in SETLL, READE statements..

  4. Data sets is the first step, I like to do a SFL app where I grab a subfile set of data at a time and then you mix in position to field and then you add in page up page down and then you add in various fun RPG style.

  5. Muchas gracias por compartir. Me queda la duda con la siguiente sentencia :07 dow (1 = 1) ;. Esto siempre se va a cumplir, ¿está bien así?

    1. DOW (1 = 1) significa que el bucle nunca terminará. Saldré del circuito por alguna condición dentro del lazo de do usando una operación LEAVE.

  6. Can we simulate the same with a where clause?

  7. What is optimal number of rows to be fetched in one block ? It depends on what parameters ?

    1. Don't worry the Db2 for i engine does all that stuff for you.

    2. I think that is quite a vague answer. Though if you wanted to say that in most cases it probably does not matter then you might be right.

      I think the optimal size of a data structure for fetching multiple records was around 32k. So if one entry in your data structure has 100 bytes your best bet is around 320 elements for your ds.

  8. Hi Simon
    I must say that your sample codes are very helpful as always.

    I'm stuck with an issue, and I'm wondering if anyone every faces issue like this before:-

    I'm trying to fetch records using the SQL in to a Data structure that's nested and when I write the the below Code it gives me an error.

    My line that fails:-

    "Exec sql FETCH C0 FOR :NbrOfRows ROWS INTO :Resp.M1;"

    What is Resp.M1?:-
    Resp.M1 is a DS inside a DSDefines as below:-
    DCL-DS Resp;
    DCL-DS M1 DIM(10);
    Field1 Packed(2:0);
    Field2 Char(5);

    Error that I get:-
    SQL5011 30 22 Position 56 Host structure array RESP not defined or not usable. Reason: A member of the host structure array is a structure.
    SQL0104 30 22 Position 60 Token . was not valid. Valid tokens:

    Any Solution for this?

    Thanks in advance!


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.