Topic
6 replies Latest Post - ‏2013-12-31T07:47:20Z by AnandKumarMatta
GaryGrant
GaryGrant
5 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

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

    ‏2012-06-01T16:09:28Z  in response to GaryGrant
    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
      ACCEPTED ANSWER

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

      ‏2012-06-04T14:13:43Z  in response to david.jencks
      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
        ACCEPTED ANSWER

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

        ‏2012-06-05T05:27:08Z  in response to GaryGrant
        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
          ACCEPTED ANSWER

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

          ‏2012-06-06T09:54:27Z  in response to U4UE_xie_zhi
          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
            ACCEPTED ANSWER

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

            ‏2012-06-06T10:44:49Z  in response to GaryGrant
            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
            ACCEPTED ANSWER

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

            ‏2013-12-31T07:47:20Z  in response to GaryGrant

            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