Cursor and subfile

560 pts.
Tags:
AS/400 Subfiles
Cursor
IBM iSeries
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

———————–

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • bogeybetsy
    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
    560 pointsBadges:
    report
  • philpl1jb
    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.
    49,960 pointsBadges:
    report
  • Vatchy
    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.
    1,410 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following