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?
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.
Joined: Feb 16, 2012
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)?
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.