Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes Performance and the fly likes JDBC Performance not making sense Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "JDBC Performance not making sense" Watch "JDBC Performance not making sense" New topic
Author

JDBC Performance not making sense

Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
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:


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
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


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
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.
Jim Yingst
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
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.


"I'm not back." - Bill Harding, Twister
 
jQuery in Action, 2nd edition
 
subject: JDBC Performance not making sense
 
Similar Threads
Need help
SQL Exception: No suitable driver
COBOL-DB2 SQLCODE handling vs Java Query
DB2 Performance Troubles
JDBC and Pointbase