Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: Multi Row Fetch - Best Practice Question

    I have a COBOL/CICS program with a HTML frontend (for search purposes). I need to display 50 rows at a time with a forward and previous button. The forward fetches the next 50 rows and the backward button fetches the previous 50 rows. I think what I need is a multi rowset cursor with a backward/forward scanning index (Access type: IR). I don't think I need a scrollable cursor for this task? Am I correct?

    Also, I need to display the total number of matching records based on the search criteria. Do I need to write a separate COUNT(*) SQL to get that or is that information available anywhere in the rowset cursor (I doubt it does that, but thought I'd ask)

    Environment: DB2 v8 on z/OS

    Thanks in advance for any input you can provide me with.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I'd use two separate (non-scrollable) cursors, one for the forward button and one for the backward button.
    Both should have a "fetch first 50 rows only".
    It's indeed a good idea to use multi-row fetch for this purpose, although that will complicate the programming logic compared to a "normal" iteration until SQLCODE NOT= 0. (Especially when less than 50 rows are returned, some additional GET DIAGNOSTICS DB2 (and COBOL) logic will be needed...)
    Don't forget to use a "WHERE key > :last-seen" and "ORDER BY key" in the forward cursor (and something similar in the backward one). The host variable "last-seen" should be passed in (through an http POST?) from the previous screen.

    For the "count(*)" I would indeed write a separate query.

    Note that the implementation mentioned above uses the classical "pseudo-conversational" approach. Which is normally what you should be after.
    There are completely different implementations for your problem. One particularly interesting one *could* be a static scrollable cursor, since it will materialise the full result set when entering the first screen of 50 entries. Next screens will then run through this result set (stored inside DB2) without having to access the base data anymore, which guarantees inter-consistency of the data shown. But if the data changes in the mean time, those changes will not be visible.
    Most important drawback of the scrollable cursor approach is the fact that it is not pseudo-conversational. It will require the cursor to remain open across screens, which could be technically difficult or even impossible.

    See also ABIS Training & Consulting - DB2 for z/OS advanced programming
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jul 2011
    Posts
    2
    Thanks Peter. I have it coded exactly like the way you described. You separate non-scrollable cursors (ORDERed by one forward >:hv and one backward <:hv) with fetch first 50 and a host variable (:hv) on a primary key indicating the last record fetched.

    I would like to use multi row fetch and get to know GET DIAGNOSTICS a bit.

    I think if my web page displayed "Page 1, 2, 3,... 16" where the numbers are hyperlinks, then a scrollable cursor would make more sense.

    From what I read about static scrollable cursors, I had a bad feeling it would be conversational and I really don't want resources hanging out loose if my user decides to go for a lunch break :-)

    Thanks again for taking the time to respond and have a good weekend.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stret67 View Post
    From what I read about static scrollable cursors, I had a bad feeling it would be conversational and I really don't want resources hanging out loose if my user decides to go for a lunch break :-)
    Quite correct; so you'd better not use scrollable cursors for that purpose.

    Quote Originally Posted by stret67 View Post
    I think if my web page displayed "Page 1, 2, 3,... 16" where the numbers are hyperlinks, then a scrollable cursor would make more sense.
    A scrollable cursor would indeed be easier to ensure the correct starting key for each page ("fetch absolute k*50-49"). With a "classical" pseudo-conversational cursor for 50 elements, there is no simple way to obtain the keys for the 1st, 51st, 101st, etc. element of the result set without traversing all the data.
    Moreover, supposing that you store those keys on your web page, these keys might no longer be the 1st etc. value because of inserts, updates, or deletes between the first traversal of the data and the user clicking on the link.

    So it all depends on how exactly you want the users to conceive the data.

    Personally, for a similar web-based application that I built some time ago, I chose to return the full result set, *but* always hard-limited to 200 entries; if the user wants to see more data, he/she would need to modify the search criteria.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •