Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

JDBC Performance not making sense

 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm "moving" this from JDBC since it got no answers in a few days:

Hi, I'm hitting a mainframe DB2 database from my PC with IBM's driver. I have a query that runs fine for small retrieval sizes but the executeQuery() call just never returns on about 2700 rows. All the same SQL with different values for 'X' below. It's also fine at any scale from DB2 Client or SQLExplorer in Eclipse. Is there some way in Java to screw up this badly?

Database: UDB version 9 on Z/OS
Driver: com.ibm.db2.jcc.DB2Driver
Query: SELECT COL1, COL2, COL3 FROM TBL_NM WHERE COL2 = 'X'
What I tried: stmt.setFetchSize()

The Code:
 
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am not sure what the ResultSetHandler and listener do. Maybe there is a mismatch between the java data type and the table data type and so an index isn't being used (not sure if this applies to db2 or not). Also, try to query a different table and return that many rows. For examplle create a table with 2700 rows in it with 1 column, and then if that works ok take 2700 rows out of the table in question and put them in a table by themselves and remove the where clause.

Also, i would use the jamon jdbc driver as it will time the various jdbc methods including resultSet.next and that info should let you see where the problem lies. This requires no code changes so that is easy. See http://jamonapi.sourceforge.net/#WhatsNew22
 
Stan James
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, I've needed an excuse to try Jamon! I'll check in indexes - not sure there are any at all yet. 2700 rows shouldn't be enough to choke DB2 on Z/OS and I can do the query through SQL Explorer or DB2 Development Tools.

I got similar results in WSAD with 1.3 and Eclipse with Java 5. Similar results with normal Statement and PreparedStatement.

Connection, Statement and ResultSet are pure stock. ResultSetReader iterates the rows and columns and calls a handler, inspired by the SAX handler, but I never get there. The execute never returns.
 
Wanderer
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess the performance stats are kind of irrelevant if execute() never returns. Though one does wonder if "never" really means "never", or just longer than you've been willing to wait, so far. It might be interesting to try a progression of smaller resultset sizes. Does it work for, say, 2000 rows? Does the performance get substantially worse as the size increases, or is there some sharp cutoff where it goes from performing acceptably, to not performing at all? To be fair, I'm not sure what one might do with this info, but it would be interesting to know.

My gut feeling is that this is some kind of driver bug. There are some older drivers that you might try to see if you get a better result: com.ibm.db2.jdbc.app.DB2Driver, com.ibm.db2.jdbc.net.DB2Driver. Yes, the newer jcc driver is recommended, but it seems like a switch is worth a try, at least.

Other than that, I'd look to DB2 documentation and support sites for other info.
 
Why am I so drawn to cherry pie? I can't seem to stop. Save me tiny ad!
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic