Fetching more rows when using multiple row Fetch

In an earlier post I gave an example of how to fill a "load all" subfile using a SQL multiple row Fetch and a data structure. A "load all" subfile can have a maximum of 9,999 records, therefore, what happens if I have more than 9,999 records/rows in my file/table?

I would need to load the next 9,999 records from the point where the first multiple row Fetch finished. Fortunately this is simple using OFFSET in the declaration of the cursor.

Just as a quick reminder: Why would I use a multiple row Fetch to get the data for a "load all" subfile. By using the multiple rows Fetch I would fetch 9,999 records in one file I/O. If I used the RPG read operation code I would need to perform 9,999 I/O operations.

In this example I am not going to load 9,999 records/rows as that would have been an enormous amount of data to create. I have a SQL table that contains 41 rows, and I will be fetching 10 rows at a time. The principal is the same if I was dealing with a file with over 9,999 rows. When I perform a multiple row Fetch the data is fetched from the table and place into a data structure array. The data within the data structure array is then read to load the subfile. As this is just an example I am not going to load a subfile, I am just going to fetch the rows into the array. If you want to see how to load the subfile from the data structure array see here.

Let me start with my very simple table. The first column, PERSON_ID is an identity column that Db2 for i creates for me.

CREATE TABLE MYLIB.PERSON ( PERSON_ID FOR "PID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NOCYCLE), FIRST_NAME FOR "FNAME" VARCHAR(20), LAST_NAME FOR "LNAME" VARCHAR(30) ) ;

I am not going to show all 41 of the Insert statements I used to insert data into this table, these three are the same as the others.

INSERT INTO QTEMP.PERSON VALUES(DEFAULT,'REG','ALLEN') ; INSERT INTO QTEMP.PERSON VALUES(DEFAULT,'JACK','CROMPTON') ; INSERT INTO QTEMP.PERSON VALUES(DEFAULT,'ROGER','BYRNE') ;

The list of all the rows in the table can be seen at the bottom of this post here.

By using DEFAULT Db2 for i calculates the identity column's value.

The "mainline" part of this program is short and sweet.

01 **free 02 ctl-opt option(*nodebugio:*srcstmt) dftactgrp(*no) ; 03 dcl-ds DataDs extname('PERSON') qualified dim(10) ; 04 end-ds ; 05 dcl-s RowsFetched uns(5) ; 06 dcl-s Offset uns(10) ; 07 dow (1 = 1) ; 08 GetData() ; 09 Offset += %elem(DataDs) ; 10 if (RowsFetched < %elem(DataDs)) ; 11 leave ; 12 endif ; 13 enddo ; 14 *inlr = *on ;

Line 1: I cannot remember the last time I wrote a program that was not totally free RPG.

Line 2: My favorite control options. As I have a subprocedure in this program I need to state that this program will not run in the default activation group.

Line 3 and 4: This is the data structure array. When I perform the multiple row fetch I will be moving the rows into here. By using the EXTNAME the compiler defines the data structure's subfields to be the same as the file's fields.

Line 5 and 6: By defining these variables in the "mainline" they will be available to be used in "mailline" and in any subprocedures in this member.

Lines 7 – 13: This Do-loop allows me to retrieve all the rows from the table.

Line 8: This is the subprocedure I am using to fetch the data from the table into the data strcture array.

Line 9: I need to increment the offset by the number of elements in the data structure array. The offset needs to be zero to fetch the rows 1 – 10, then 10 to fetch 11 – 20, etc.

Line 10 – 12: If I have fetched fewer rows than the number of elements in the array then I have fetched the last group of rows.

My subprocedure is just an easy way to define the SQL cursor and fetch the data. I have code this, as what I call an "open subprocedure". I do not have a procedure definition or interface for it. What this does it is makes all the variables, files, etc defined in the "mainline" available with the subprocedure too. Any variables I define in the subprocedure remain local to the be used only in this subprocedure. If you want to you could make this a subroutine.

15 dcl-proc GetData ; 16 dcl-s NbrOfRows uns(5) inz(%elem(DataDs)) ; 17 RowsFetched = 0 ; 18 clear DataDs ; 19 exec sql DECLARE C0 CURSOR FOR SELECT * FROM PERSON ORDER BY LNAME,PID OFFSET :Offset ROWS ; 20 exec sql OPEN C0 ; 21 exec sql FETCH C0 FOR :NbrOfRows ROWS INTO :DataDs ; 22 exec sql GET DIAGNOSTICS :RowsFetched = ROW_COUNT ; 23 exec sql CLOSE C0 ; 24 end-proc ;

Line 15: This marks the start of the subprocedure.

Line 16: This variable can only be used within this subprocedure.

Line 17 and 18: I am initializing these variables to ensure if no, or partial data, is retrieved from the table there is no values left over from the previous Fetch.

Line 19: This is the definition of the cursor that is used for fetching the rows from the table. Notice the OFFSET, I am using the value from the variable Offset to inform the cursor where to start its fetch. Any program variable I use in a SQL statement with a RPG program must be prefix with a colon ( : ).

Line 20: I have to open the cursor before I can use it.

Line 21: I am fetching in the next batch of records into the data structure array. I am fetching the number of rows in the variable NbrOfRows, which has been initialized with the number of elements the array has.

Line 22: I am getting the number of rows that were fetched.

Line 23: If you open a cursor you have to close it.

Line 24: Marks the end of the subprocedure.

I start debug, put a breakpoint on line 9, and call the program. First time through I can see I have the first ten rows as the offset was zero.

DATADS.PID(1) = 000000001. DATADS.FNAME(1) = 'REG DATADS.LNAME(1) = 'ALLEN DATADS.PID(10) = 000000004. DATADS.FNAME(10) = 'JOHNNY DATADS.LNAME(10) = 'CAREY ROWSFETCHED = 10

Second time in the loop the offset is 10, therefore I will fetch the 11th – 20th rows.

DATADS.PID(1) = 000000018. DATADS.FNAME(1) = 'LAURENCE DATADS.LNAME(1) = 'CASSIDY DATADS.PID(10) = 000000010. DATADS.FNAME(10) = 'DONALD DATADS.LNAME(10) = 'GIBSON ROWSFETCHED = 10

Third time rows 21 – 30.

DATADS.PID(1) = 000000032. DATADS.FNAME(1) = 'LAL DATADS.LNAME(1) = 'HILDITCH DATADS.PID(10) = 000000028. DATADS.FNAME(10) = 'JACK DATADS.LNAME(10) = 'MELLOR ROWSFETCHED = 10

Fourth time 31 – 40.

DATADS.PID(1) = 000000035. DATADS.FNAME(1) = 'THOMAS DATADS.LNAME(1) = 'PARKER DATADS.PID(10) = 000000036. DATADS.FNAME(10) = 'FRANK DATADS.LNAME(10) = 'WILLIAMS ROWSFETCHED = 10

Fifth time there is only one row to retrieve.

DATADS.PID(1) = 000000037. DATADS.FNAME(1) = 'JACK DATADS.LNAME(1) = 'WILSON DATADS.PID(2) = 000000000. DATADS.FNAME(2) = ' DATADS.LNAME(2) = ' ROWSFETCHED = 1

As the RowsFetched is less than ten I exit the Do loop.

You could easily change this to retrieve 9,999 rows from your table/file for your "load all" subfile. Or use this to load a "screen at a time" subfile if you chose to use one. It is a nice simple way to "page" through a table using multiple row Fetch.

 

You can learn more about using OFFSET within a cursor from the IBM website here.

 

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

 

List of rows in table PERSON

SELECT * FROM PERSON ORDER BY LNAME,PID Person First Last id name name 01 1 REG ALLEN 02 14 JOHN ASTON JR 03 30 RAYMOND BENNION 04 15 JOHNNY BERRY 05 16 BRIAN BIRCH 06 7 JACKIE BLANCHFLOWER 07 17 ERNEST BOND 08 38 JAMES BULLOCK 09 3 ROGER BYRNE 10 4 JOHNNY CAREY 11 18 LAURENCE CASSIDY 12 25 ARTHUR CHESTERS 13 8 ALLENBY CHILTON 14 19 FRANK CLEMPSON 16 9 HENRY COCKBURN 16 2 JACK CROMPTON 17 31 WILLIAM DALE 18 20 JOHN DOWNIE 19 39 STANLEY GALLIMORE 20 10 DONALD GIBSON 21 32 LAL HILDITCH 22 40 SAMUEL HOPKINSON 23 11 MARK JONES 24 27 THOMAS JONES 25 33 GEORGE LYDON 26 12 WILLIAM MCGLEN 27 34 HUGH MCLENAHAN 28 5 THOMAS MCNULTY 29 21 HAROLD MCSHANE 30 28 JACK MELLOR 31 35 THOMAS PARKER 32 22 STAN PEARSON 33 41 CHARLES RAMSDEN 34 6 BILLY REDMAN 35 23 JACK ROWLEY 36 29 JACK SILCOCK 37 26 ALFRED STEWARD 38 24 JOHN WALTON 39 13 JEFFREY WHITEFOOT 4o 36 FRANK WILLIAMS 41 37 JACK WILSON

Return