File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes JDBC DB2 database hits Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC DB2 database hits" Watch "JDBC DB2 database hits" New topic
Author

JDBC DB2 database hits

jason joy
Greenhorn

Joined: Feb 16, 2012
Posts: 2
I am connecting to z/os mainframe db2 database using a java application via type 4 db2 driver.
I need to retrieve data in bulk and it is in 150 millions for a single table. I can see that the result set gets 125 records at a time. How do i modify this so that the db hits are minimized and more records are fetched at a time.

I have tried setFetchSize in jdbc but it does not seem to work. Can anything can be does to specify the number of records to be fetched during a db hit?
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

yes, alter your original sql query, to only return the rows you need.
If you really do need to process all 150 million rows in the table, allow yourself a week or 2 to run this task.
jason joy
Greenhorn

Joined: Feb 16, 2012
Posts: 2
I am altering my table as I need based on a particular field but it still gives me close to 20 million records, and I can't filter it on any other column too as I will need to process it in the initial order.
Is there some way to specify the fetch size? either on the db side or in my client side(using jdbc)?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Setting the fetch size way higher probably doesn't improve anything much. There is a 100-fold difference in number of roundtrips between fetchsize of 1 and 100, and just twofold from increasing it further to 200. So the time saved per one processed rows decreases quite fast.

Can't you really decrease the number of rows further? You mention you cannot decrease it further due to order in which rows are returned, but this is quite certainly not true. Moving as much processing as close to the data as possible (ie. into the database) will bring you the largest benefit.

If you really need to process so much rows, use profiler to find out how much time you spent actually processing them. If it is a significant amount of total time, you could read the records on another thread. AS JDBC generally only wait for the database to get the data, your processing thread would be able to chew on them in the meantime.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

and have you considered a package procedure, if all you need to do is calculations on the data?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC DB2 database hits