JDBC Performance not making sense

[Stan James]

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:

public void executeQuery(String sql, ResultSetHandler listener) throws Exception {
    Statement stmt = null;
    ResultSet rs = null;
    try {
        stmt = connection.createStatement();
        rs = stmt.executeQuery(sql);
        new ResultSetReader(listener).read(rs);
    }
    catch (SQLException e) {
        System.out.println(e.getMessage());
        throw e;
    }
    finally {
        Closer.close(rs);
        Closer.close(stmt);
    }
}

[steve souza]

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]

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]

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.