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

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:

FETCH C0 FOR 10 ROWS

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 SELECT * FROM TESTFILE ORDER BY FLD1 ; 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:

• SQL FETCH
• Use FETCH FOR n ROWS

 

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