give me idea for this procedure

[narayana]

I want the criteria like this.

I have 10k of rows in my table and i have to fetch first 200 rows . And
next time when i execute the same query it should fetch next 200 rows
and that should be continued .For that is there any query for my
requirement or I have to write a dynamic sql procedure for this .

In Oracle using rowid we can get the records dynamically where as in
DB2 is the facility is there or not ?
Give some guidance regarding my requirement.

[Serge Rielau]

narayana wrote:

I want the criteria like this.

I have 10k of rows in my table and i have to fetch first 200 rows . And
next time when i execute the same query it should fetch next 200 rows
and that should be continued .For that is there any query for my
requirement or I have to write a dynamic sql procedure for this .

In Oracle using rowid we can get the records dynamically where as in
DB2 is the facility is there or not ?
Give some guidance regarding my requirement.

Do you have a single column key?
E.g. you can do:
SELECT pk, data FROM T WHERE pk > ? ORDER BY pk FETCH FIRST 100 ROWS

For the first call use e.g. a negative empid (a value out of bound).
Then for each subsequent select you use the last pk you retrieved before
a input.

If that's not possible do this:

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY whatever) AS rn, t.*
FROM T) AS X WHERE rn BETWEEN ? AND ?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab