Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC DB2 database hits

 
jason joy
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
and have you considered a package procedure, if all you need to do is calculations on the data?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic