aspose file tools*
The moose likes JDBC and the fly likes How to retrieve 1 million records in DB2 and write to a file? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to retrieve 1 million records in DB2 and write to a file?" Watch "How to retrieve 1 million records in DB2 and write to a file?" New topic
Author

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

Dennis Lee
Greenhorn

Joined: Dec 03, 2001
Posts: 7
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

Joined: Oct 29, 2001
Posts: 8927
    
    9

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.


"blabbing like a narcissistic fool with a superiority complex" ~ N.A.
[How To Ask Questions On JavaRanch]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Oct 29, 2001
Posts: 8927
    
    9

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

Joined: Jul 09, 2001
Posts: 1879

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to retrieve 1 million records in DB2 and write to a file?