File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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
JavaRanch » Java Forums » Java » Performance
Bookmark "JDBC Performance not making sense" Watch "JDBC Performance not making sense" New topic

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
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: 862
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 and that info should let you see where the problem lies. This requires no code changes so that is easy. See - 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

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:, 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
I agree. Here's the link:
subject: JDBC Performance not making sense
jQuery in Action, 3rd edition