Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
1 reply Latest Post - ‏2012-10-24T12:20:00Z by Kulnor
Kulnor
Kulnor
2 Posts
ACCEPTED ANSWER

Pinned topic ResultSet multi row fetch from Java/JDBC?

‏2012-10-24T11:15:56Z |
I'm having a hard time trying to enable multi-row fetching from a JDBC connection. When I loop through my ResultSet, the process is very slow and fetches one row at a time (see simple code below)

My driver is db2jcc-4.jar. I'm connecting over a DB2SimpleDataSource and do call
db2DataSource.setEnableRowsetSupport(com.ibm.db2.jcc.DB2BaseDataSource.YES);
during initialization (not sure if this is necessary)

Any help/suggestion appreciated....

best
*P

stmt = this.getConnection().createStatement();
stmt.setFetchSize(1000);
stmt.execute(xquery);

ResultSet res = stmt.getResultSet();
logger.debug("Fetch size is "+res.getFetchSize()); // this returns 10000
while(res.next()) {
...
}
Updated on 2012-10-24T12:20:00Z at 2012-10-24T12:20:00Z by Kulnor
  • Kulnor
    Kulnor
    2 Posts
    ACCEPTED ANSWER

    Re: ResultSet multi row fetch from Java/JDBC?

    ‏2012-10-24T12:20:00Z  in response to Kulnor
    Further to this, I found out for a result set to support multi row in JDBC, a series of conditions must be satisfied
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.apdv.java.doc%2Fsrc%2Ftpc%2Fimjcc_c0052058.html
    in particular the cursor must be scrollable.

    Now to make the cursor scrollable, I need to specify the following resultset type and concurrency.
    stmt = this.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

    The issue now is that when I try this, DB2 return
    DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=63, DRIVER=4.13.127
    which basically seem mean "not supported"

    Suggestions appreciated.

    *K