Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • 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
Sheriff
Posts: 35977
422
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
Sheriff
Posts: 35977
422
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
Sheriff
Posts: 35977
422
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.
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!