retrieving thousand of rows from DB2 database

Hi

I am trying to retrieve 24000 rows from DB2 database

I could retrieve 1000 rows(by saying first 1000 rows only) .But when I try retrieving all the reocrds ,"I get the error as invalid operation result set closed "

Can any one suggest what i need to do

Thanks
Nagamani

Naga,
What are you doing with all those rows?

Hi Naga,
In Oracle Database, we usually use 'ROWNUM' to do that..
I apologize that I don't know whether DB2 have that syntax or not...

Correct me if I am wrong..
Hope this help

daniel

I am having a search results screen where in I display the page numbers and each page can display 10 records per page and user can click on page number and see 10 records at a time.

Naga,
Then you can get just the first ten records on the inital request, rather than all the rows. This saves a lot of processing. Many users will not get to page 200, so there's no need to get data that nobody will see.

But if some one wants to look at any or all of the records in the database, then the problem arises

I see 2 things here After executing query I am populating the record's fields into a bean which adds on to a collection object .I could retrieve for 2000 and 10000 records too.So basically the problem I see here is memory and the second one is DB2 does not support the retrieval of rows by specific row numbers

I have 25000 rows in my database.Even If I can manage to show first 1000 rows displaying page numbers till 100.I am thinking how I make the user to select the next 1000 rows with out a row num feature.

Naga,
That is a problem in db2. If your rows are sorted in any way, you can use a filter in the where clause (like name > 'up to here') to get less rows.

If not, you are stuck getting the first X rows. However, you will have to do very large queries less frequently if you only get a few hundred rows in the first shot as many users will not get past those.