Wednesday, October 25, 2017

Fetching more rows when using multiple row Fetch

getting more rows using a 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

7 comments:

  1. Great example article, I love to see different ways to skin a cat. I do a similar thing using a unique column to order by and then holding the value to use as a position to field. So my first time through the position to is blank or 0 and I grab whatever number records and then I use the value of the last record in the data set to position to for the next fetch when my row count is less than my fetch number then I am at the end and I do the reverse for page back order by desc. I like my way ...

    ReplyDelete
    Replies
    1. You have some code you could post of your technique? I'd like to see an example. I've been using two separate cursors - one for pageup and one for pagedown.

      Delete
    2. I assume you are using a "screen at time" subfile.

      As it is so easy to fetch large amounts of data in one "go" I would consider why you are using the "screen at a time".

      In my experiece, and I have seen others write the same, using the "load all" subfile is faster and more efficient. I can grab up to 9,999 rows in one "go", load them quickly into the subfile, and then have the subfile handle the page up/down with no extra work needed in the program.

      Delete
    3. Simon

      It's for the user experience. When providing a 'position to' field, you need to provide the user the ability to roll back to data prior to the position to selection. The only way to do this is with page-at-a-time.

      Delete
  2. I have never understood why people want that. If I position a subfile to "SMITH" and they want to see "ROBERTSON" they should enter "ROBERTSON" in the search field, not roll back to it.

    ReplyDelete
    Replies
    1. I agree that is the easier way to code it. But when positioned to "SMITH" and the user requests a roll back, it seems very wrong to give them an error stating they are at the top of the list when there is obviously more data to roll back to.

      Delete
    2. We will have to disagree on this point.

      Delete

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.