• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to retrieve 1 million records in DB2 and write to a file?

 
Dennis Lee
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have to use Java to retrieve one million records in DB2 and write the records into a txt file. I think the ResultSet is unable to hold so many records in memory. We also need to get the better performance. How could I do? Hope to get your help. Thanks a lot.
 
Joe Ess
Bartender
Posts: 9312
10
Linux Mac OS X Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How's about processing the records one at a time, or a block of a couple hundred at a time? A scrollable ResultSet makes paging easy.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Dennis Lee:
I have to use Java to retrieve one million records in DB2 and write the records into a txt file. I think the ResultSet is unable to hold so many records in memory. We also need to get the better performance. How could I do? Hope to get your help. Thanks a lot.

Not a problem. Your resultset doesn't really hold the million records in memory ( unless you use a scrollable ResultSet == BAD ). I punch through a million records the same way I punch through 10:

The actual number of records held in memory at one time is equal to the FetchSize of the Statement/ResultSet. For processing a large number of records, you may want to increase the fetch size ( reduces the number of DB calls ) until you find that it's not increasing the application speed anymore.
Jamie
Joe, not sure what this had to do with paging?
 
Joe Ess
Bartender
Posts: 9312
10
Linux Mac OS X Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jamie Robertson:

Joe, not sure what this had to do with paging?

What do you call it when you work with a block of records rather than an entire result set? And I did not know scrollable ResultSet loads all the results from a query. . .
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Joe Ess:
... And I did not know scrollable ResultSet loads all the results from a query. . .

scrollable results aren't all loaded into memory at first, but as you scroll forwards, the previously fetched results are stored in memory ( to allow you to scroll back ).
When I think of paging resultsets I usually think of web apps displaying parts of large results on a page with a next and back button. I can see what you intended in your post though.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic