• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

retrieving thousand of rows from DB2 database

 
naga venna
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34965
377
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Naga,
What are you doing with all those rows?
 
Fisher Daniel
Ranch Hand
Posts: 582
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
naga venna
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34965
377
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
naga venna
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34965
377
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic