IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 6 replies
  • Latest Post - ‏2013-12-31T07:47:20Z by AnandKumarMatta
GaryGrant
GaryGrant
5 Posts

Pinned topic problem with holding the cursor open with commits in loop over a ResultSet

‏2012-06-01T11:38:40Z |
We have a problem with a Websphere CE / DB2-C install. We're using WAS CE 3.0.0.1 running on Linux with DB2-C v9.7.
The problem is that in a simple loop through a ResultSet, the ResultSet gets closed, even if holdability is set to "on", if there are commits in the processing of the loop.

The code below shows how we are reproducing the problem.
The code retrieves a result set from the database, loops through it, mapping each record to a java bean. There is no further processing in this example aside from adding the id to a list.

After every 16 records the loop does a commit. After a given number of commits the loop errors out with the messages in the log extracts below, which indicate that the ResultSet has been closed. There are more records left to process in the ResultSet.

The settings listed show some of the relevant settings on the JDBC connection.

Has anyone ever seen this ? Any potential solutions ?

thanks in advance

+++++++++++++++++++++++++

<<Log extract >>

org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; uncategorized SQLException for SQL []; SQL state null; error code -4470; jcct41012010898http://3.50.152 Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null; nested exception is com.ibm.db2.jcc.b.SqlException: jcct41012010898http://3.50.152 Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
Caused by: com.ibm.db2.jcc.b.SqlException: jcct41012010898http://3.50.152 Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
at com.ibm.db2.jcc.b.wc.a(wc.java:55)
at com.ibm.db2.jcc.b.wc.a(wc.java:102)
at com.ibm.db2.jcc.b.jk.Bb(jk.java:3987)
at com.ibm.db2.jcc.b.jk.c(jk.java:273)
at com.ibm.db2.jcc.b.jk.next(jk.java:258)
at org.tranql.connector.jdbc.ResultSetHandle.next(ResultSetHandle.java:801)
<<End Log extract >>

<<Log extract >>

The identified cursor is not open. ERRORCODE=-4499, SQLSTATE=58009; nested exception is com.ibm.db2.jcc.b.hm: jcct4205011326http://3.50.152 Execution failed due to a distribution protocol error that caused deallocation of the conversation.
The identified cursor is not open. ERRORCODE=-4499, SQLSTATE=58009
org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; uncategorized SQLException for SQL []; SQL state 58009; error code -4499; jcct4205011326http://3.50.152 Execution failed due to a distribution protocol error that caused deallocation of the conversation.
The identified cursor is not open. ERRORCODE=-4499, SQLSTATE=58009; nested exception is com.ibm.db2.jcc.b.hm: jcct4205011326http://3.50.152 Execution failed due to a distribution protocol error that caused deallocation of the conversation.
The identified cursor is not open. ERRORCODE=-4499, SQLSTATE=58009
Caused by: com.ibm.db2.jcc.b.hm: jcct4205011326http://3.50.152 Execution failed due to a distribution protocol error that caused deallocation of the conversation.
The identified cursor is not open. ERRORCODE=-4499, SQLSTATE=58009
at com.ibm.db2.jcc.b.wc.a(wc.java:281)
at com.ibm.db2.jcc.b.wc.a(wc.java:336)
at com.ibm.db2.jcc.t4.eb.i(eb.java:445)
at com.ibm.db2.jcc.t4.eb.h(eb.java:279)
at com.ibm.db2.jcc.t4.eb.a(eb.java:227)
at com.ibm.db2.jcc.t4.eb.c(eb.java:31)
at com.ibm.db2.jcc.t4.u.a(u.java:32)
at com.ibm.db2.jcc.t4.j.Xb(j.java:245)
at com.ibm.db2.jcc.b.jk.X(jk.java:3412)
at com.ibm.db2.jcc.t4.d.lb(d.java:1928)
at com.ibm.db2.jcc.t4.d.n(d.java:2115)
at com.ibm.db2.jcc.t4.d.o(d.java:2127)
at com.ibm.db2.jcc.t4.d.a(d.java:118)
at com.ibm.db2.jcc.t4.d.b(d.java:478)
at com.ibm.db2.jcc.b.bc.a(bc.java:213)
at com.ibm.db2.jcc.b.jk.c(jk.java:296)
at com.ibm.db2.jcc.b.jk.next(jk.java:258)
at org.tranql.connector.jdbc.ResultSetHandle.next(ResultSetHandle.java:801)

<<End Log extract >>

These are settings for the connection - this is checked at each commit in the loop (every 16 records).

<<Log extract this is a successful commit in the loop >>
INFO LoopTestAction Committ done
INFO LoopTestAction DataSource Connection Check - Holdability:1
INFO LoopTestAction DataSource Connection Check - isClosed:false
INFO LoopTestAction DataSource Connection Check - isReadOnly:false

<<End Log extract >>
<< Java Code Extract >>

import java.sql.*;
import java.util.*;

import javax.sql.*;

import net.framework.persist.*;
import net.framework.spring.*;

import org.springframework.jdbc.core.*;
import org.springframework.jdbc.datasource.*;
import org.springframework.web.servlet.*;
import app.doclib.domain.*;
import app.framework.*;

public class LoopTestAction extends AbstractCommandAction
{

private PersistenceManager persistenceManager;
private TransactionContextHolder transactionContextHolder;
private DocumentFinder documentFinder;
private Connection conn;
protected RowMapper m_rowMapper = null;
private DataSource dataSource;
private JdbcDaoManager m_daoManager;

@Override
protected ModelAndView handle(Object data) throws Exception
{

Command cmd=(Command)data;

logger.info("Doing Connection Loop");
doSimpleLoop(cmd);

logger.info("Finished processing set ");
return new ModelAndView("srch/loop-test");
}

private void doSimpleLoop(Command cmd ) throws Exception
{
conn=DataSourceUtils.getConnection(dataSource);

PreparedStatement prep = conn.prepareStatement("select * from Document", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);

ResultSet rs = prep.executeQuery();

m_rowMapper = persistenceManager.getRowMapperForType(Document.class);

int iCount = 0;
while (rs.next()) {

Object o = m_rowMapper.mapRow(rs, ++iCount);
Document doc = (Document)o;

try {
List<String> l = cmd.getItems();
if(l==null) l = new ArrayList<String>();
l.add(new Integer(doc.getId()).toString());
cmd.setItems(l);
}
catch (Exception e) {
logger.error(e.toString(), e);
throw new SQLException(e.toString());
}

if (iCount >16) {
iCount=0;

conn.commit();
logger.info("Committ done");
logger.info("DataSource Connection Check - Holdability:" + conn.getHoldability());
logger.info("DataSource Connection Check - isClosed:" + conn.isClosed());
logger.info("DataSource Connection Check - isReadOnly:" + conn.isReadOnly());

}

}
}

....

<< End Java Code Extract >>
Updated on 2012-06-06T10:44:49Z at 2012-06-06T10:44:49Z by GaryGrant
  • david.jencks
    david.jencks
    1 Post

    Re: problem with holding the cursor open with commits in loop over a ResultSet

    ‏2012-06-01T16:09:28Z  
    Was CE and tranql aren't doing extra operations when you try to traverse the result set. Googling on db2 and ResultSet.HOLD_CURSORS_OVER_COMMIT indicates that the holdability only works for non-xa connections. Are you using a connection pool, and if so how have you configured it? If you want to do transaction control from your code rather than through UserTransaction and the transaction manager you should use <no-transaction/> in your pool plan. (<local-transaction/> might also work, but you are asking for trouble with conflicts between jta initiated local transaction operations and code initiated local transaction operations such as the commit in your code sample)
  • GaryGrant
    GaryGrant
    5 Posts

    Re: problem with holding the cursor open with commits in loop over a ResultSet

    ‏2012-06-04T14:13:43Z  
    Was CE and tranql aren't doing extra operations when you try to traverse the result set. Googling on db2 and ResultSet.HOLD_CURSORS_OVER_COMMIT indicates that the holdability only works for non-xa connections. Are you using a connection pool, and if so how have you configured it? If you want to do transaction control from your code rather than through UserTransaction and the transaction manager you should use <no-transaction/> in your pool plan. (<local-transaction/> might also work, but you are asking for trouble with conflicts between jta initiated local transaction operations and code initiated local transaction operations such as the commit in your code sample)
    Thanks for your response. Are the connection pool settings stored in an xml file on the server? If so, where is that ? I'd like to check the settings you mention there directly because the UI form in the console does not seem to show all settings when I edit the pool settings (there are additional settings when creating the pool in the first place).
  • U4UE_xie_zhi
    U4UE_xie_zhi
    54 Posts

    Re: problem with holding the cursor open with commits in loop over a ResultSet

    ‏2012-06-05T05:27:08Z  
    • GaryGrant
    • ‏2012-06-04T14:13:43Z
    Thanks for your response. Are the connection pool settings stored in an xml file on the server? If so, where is that ? I'd like to check the settings you mention there directly because the UI form in the console does not seem to show all settings when I edit the pool settings (there are additional settings when creating the pool in the first place).
    You can find the related setting in <geronimo-home>\var\config\config.xml. But I think you need to check your deploy plan first according David's reply.
  • GaryGrant
    GaryGrant
    5 Posts

    Re: problem with holding the cursor open with commits in loop over a ResultSet

    ‏2012-06-06T09:54:27Z  
    You can find the related setting in <geronimo-home>\var\config\config.xml. But I think you need to check your deploy plan first according David's reply.
    In setting up the data source, I did use "LOCAL" in Transaction Type. I have also tried "NONE", with no difference in behaviour.

    However, I don't see any reference to that setting in the config.xml or my deployment plan for the project. Where should the reference to <local-transaction/> be found ?
  • GaryGrant
    GaryGrant
    5 Posts

    Re: problem with holding the cursor open with commits in loop over a ResultSet

    ‏2012-06-06T10:44:49Z  
    • GaryGrant
    • ‏2012-06-06T09:54:27Z
    In setting up the data source, I did use "LOCAL" in Transaction Type. I have also tried "NONE", with no difference in behaviour.

    However, I don't see any reference to that setting in the config.xml or my deployment plan for the project. Where should the reference to <local-transaction/> be found ?
    Just to be clear,

    here is the section from the DataSource deployment plan that shows the settings. It does include the <local_transaction/> tag. What I am looking for is some indication in the config of the server that this setting has been taken on.
    <resourceadapter>
    • <outbound-resourceadapter>
    • <connection-definition>
    <connectionfactory-interface>javax.sql.DataSource</connectionfactory-interface>
    • <connectiondefinition-instance>
    <name>jdbc/DBNAME</name>
    <config-property-setting name="DatabaseName">DBNAME</config-property-setting>
    <config-property-setting name="Password">PASSWORD</config-property-setting>
    <config-property-setting name="UserName">USERNAME</config-property-setting>
    <config-property-setting name="TraceFile">db_trace</config-property-setting>
    <config-property-setting name="ServerName">localhost</config-property-setting>
    <config-property-setting name="PreparedStatementCacheSize">50</config-property-setting>
    • <connectionmanager>
    <local-transaction />
    • <single-pool>
    <max-size>10</max-size>
    <min-size>5</min-size>
    <blocking-timeout-milliseconds>5000</blocking-timeout-milliseconds>
    <idle-timeout-minutes>20</idle-timeout-minutes>
    <match-one />
    </single-pool>
    </connectionmanager>
    </connectiondefinition-instance>
    </connection-definition>
    </outbound-resourceadapter>
    </resourceadapter>
  • AnandKumarMatta
    AnandKumarMatta
    11 Posts

    Re: problem with holding the cursor open with commits in loop over a ResultSet

    ‏2013-12-31T07:47:20Z  
    • GaryGrant
    • ‏2012-06-06T09:54:27Z
    In setting up the data source, I did use "LOCAL" in Transaction Type. I have also tried "NONE", with no difference in behaviour.

    However, I don't see any reference to that setting in the config.xml or my deployment plan for the project. Where should the reference to <local-transaction/> be found ?

    Hi The combination of ResultSetHoldability(2) and Transaction Type(Local) is solving the issue.

    Look at the configuration screenshot GUI.  Experts, You also please refer the attachment and let me know is this a good configuration for 4gb ram Linux sever cloud instance.

    Thank you,

    Anand

    1 Jan 2014 - One update : Don't change  below value.

    :

    If the value more than 0 (I tried with 5),this is also throwing error resultset closed.

    Attachments

    Updated on 2014-01-01T16:42:32Z at 2014-01-01T16:42:32Z by AnandKumarMatta