How to get specified number of rows from a db2 table. 1

[abhijitkolhatkar]

Hi,
I am querying a table which is having 70000 rows, from a ASP page.
since the page can not display this much huge data, I am thinking of giving following functionality.
When the page will be first loaded, only first 2000 rows will be retrieved and a button will be displayed which will hav title MORE.
When user will click on this button, next 2000 rows will be dispalyed and so on...

any suggestions on this???

thanks
Abhijit

 

[ddiamond]

Are you sorting on a unique field? If so you can do the following:

select <field list>
from <tables>
order by <unique field>
fetch first 2000 rows only;

This will return the first 2000 rows. You then need to store the greatest of the <unique field> in a variable - lets call it Last_Selected.

To get the next 2000:

select <field list>
from <tables>
where <unique field> > Last_Selected
order by <unique field>
fetch first 2000 rows only;

 

[gregsimpson]

Abhijit,

scrollable cursors will do it.

http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci979371,00.html

Alternatively, you can code your application in terms of opening a cursor retrieving the first 2000, rows. Then when MORE is pressed you can open the cursor again, passing in the key of the last key, with a > :HV. Fetch the next 2000 rows and so on.

I can give you a code example to demonstrate this if you need it.

Cheers
Greg

 

[gregsimpson]

Abhijit,

ddiamond has posted a code sample, whilst I was typing.

Cheers
Greg