MVSFORUMS.com

[misi01]

I have worked a fair bit with multirow fetch processing and I hit upon the following "problem" and wonder if it's doable.

I fetch 100 rows at a time from a table (let's say I'm fetching employee number, hire date from the table, and emp.no is sorted in ascending order). The idea is that each time, I need the hire date, I can search using a binary search on the 100 rows read in to see if the employee is in the CURRENT block of 100 employees. Normally, I would expect to get a number of hits within this table and therefore avoid a "simple" select from the table based on the emp.no.

Let's assume I've done my binary search and didn't find the employee. Let's also assume that the first fetch gave me employees 56-198 (with gaps in the numbers). Now let's assume that the employee I now want is number 1500. Yes, I can loop fetching 100 rows at a time, binary search to see if I found number 1500 and as soon as I do, I'm done.

Is there a way of "jumping" from a starting employee of 56 directly to 1500 so I avoid all the fetches between 199 and 1499 (which will never contain my required information).

Yes, I know I could close the cursor and then reopen it, but that has (?) to be heavier than fetching 100 rows at a time

I suppose I'm looking for a way of telling DB2 that the next fetch I want should start with emp.no = 1500, skipping all the previous rows.
_________________
Michael

[kolusu]

misi01,

Unless I am missing something, If you have a index defined on the employee number, isn't a single SELECT with Employee number and Fetch 1st n rows more effective than having a multi row fetch and a binary search?

I suppose you have a Seqnum to number the records using ROWNUMBER in the multi-row fetch and then jump to the next record using that rownumber record in the program.

Kolusu