I'm just converting to DB2 8.1 right now and I'm having a hard time finding any information at all on how (or if it's possible at all) to limit a result set to a start_index and items_to_return.
I've found "fetch first N rows only" which is would solve items_to_return, but so far the only way I've found to do start_index is to just keep calling next() on the result set. (Yes I'm using java/JDBC)
As an example in mysql there's a thing called "limit x,y" where x is the starting row # and y is the number of rows to return. Is there such a thing in DB2?
Here is an example where you can define row numbers to the result set [with row_number() over…] and then skip over a certain number of rows.
FROM ( SELECT ...,
row_number() over(order by s.crtd_dtt desc, s.tableA_id)
FROM tableA_t s
WHERE s.tableA_id > '65185' AND
s.display_status = 5005 ) AS inner(..., rnum)
WHERE rnum BETWEEN 37 AND 99
The above query selects rows 37 through 99 in the result set. You would need to add the “select first n rows only”.