Cursor and subfile
Home > IT Answers > AS/400 > Cursor and subfile
Bogeybetsy
325 pts.
0
Q:
Cursor and subfile
Cursor, iSeries, AS/400 Subfiles
I am using a cursor to get records for display through a per-page-loading subfile.  I am using a host structure to get the records.  My problem is how to "position to" a specific record.  Cursors do not support functionality similar to what a SetLL and ReadE can do. When I close, prepare a new query, and re-open the cursor, the time to retrieve the new set of records with the record I am searching for on top of the subfile take more than a minute to display.  Is there a better way to do this without going back to RLA (SetLL and ReadE)?  Also, the maximum number of records that fit on the screen is 19.  So I retrieve only 19 records per close-&-then-open of the cursor.  When I do not fetch for 19 records only but load all file records in the cursor and then just fetch for PAGE UP and PAGE DN, wouldn't I be eating up a lot of memory - I mean what happens if I have multiple users?

Software/Hardware used:
iSeries V5R4
ASKED: Aug 25 2009  4:32 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Philpl1jb
24530 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
I think this is slowing you down
So I retrieve only 19 records per close-&-then-open

I believe you loop & fetch next 19 times .. loading a page
on page down repeat process loading the next page

If you need to move up in the cursor you will need to open cursor as "scrolable" and then you can
fetch relative :myRecNo;


Hopefully an RPGSQL guy/gal will chime in with corrections/more details.

Phil


--------------------------
you say "the time to retrieve the new set of records with the record I am searching for on top of the subfile take more than a minute" and that makes me wonder if you have an index on the search criteria.


-------------------------

Since you know your sub-file will only display 19 rows at a time, you could fetch 19 rows into an array and include the statements "OPTIMIZED FOR 19 ROWS" on the DECLARE and "FETCH FIRST 19 ROWS" on the FETCH statement . That should improve the performance. If you try to fetch past the 19 rows, you will get and SQLSTATE of 02000.

I would guess that your largest performance issue is not around the fetch though. It's likely around the OPEN statement.

-----------------------
Last Answered: Aug 27 2009  2:24 PM GMT by Philpl1jb   24530 pts.
Latest Contributors: NullFields   395 pts., Meandyou   1840 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Bogeybetsy   325 pts.  |   Aug 27 2009  7:00AM GMT

Hi Phil,

Thanks for the answer. I have the proper indexes. When I do the SQL statements interactively, they’re fast. It’s the cursor I’m having problems with. If I do the “scrollable” cursor thing, won’t I be loading all records in main memory? Wouldn’t that be a problem if I have multiple users? And besides, when I use a scrollable cursor, how do I implement a “Position To” function?

Allan

 

Philpl1jb   24530 pts.  |   Aug 27 2009  10:35AM GMT

Don’t know why you want to go back to those records if you’re creating a growing subfile
As I understand it, the fetch is somewhat equlivant to a read. Create the cursor once over the file .. that’s about the same as opening the file and then use it throughout the process.

 

Vatchy   670 pts.  |   Aug 28 2009  2:38PM GMT

When you create the cursor you are, in effect, creating a logical view over the file. Actual records are not retrieved until the fetch statement runs.

Don’t close the cursor after you have loaded your each page. When a page key is pressed, either read the next 19 records (PgDn) or read the previous 38 (PgUp) and then load your subfile again.

You only need to close and re-open the cursor if you need to position to a specific record.

 
0