Results 1 to 3 of 3
  1. #1
    Chinnu55 is offline Member
    Join Date
    Apr 2011
    Posts
    20
    Rep Power
    0

    Default Getting exception while trying to update multiple records

    Hi,

    I am trying to update each and every users in a table "users", there are more than 5000 users.

    My implementation details: using JDK1.4, eclipse, WAS6.0

    I am trying to use single connection object for the updated process and using connection.commit() only after all the users details is updated.

    flow:

    1. getUsers() here creating a connection object and getting number of users with STATUS field marked "active". Now, I need to look for update for the user's availability. So calling getUserAvailabilityInformation(connection) passing connection object.

    getUsers() {
    ...
    connection = getConnection();
    ....
    run query and get users

    while (rs.next){
    userID = rs.getString(1);
    getUserAvailabilityInformation(connection, userID);
    }

    2. getUserAvailabilityInformation(connection, userID) -
    here I check if the user is available i.e. still with the company from some external application and if user is not available then need to mark STATUS as "Inactive". If user is not available then calling updateUserInactiveStatus(connection, userID) passing connection object and userID.

    2a. updateUserInactiveStatus(connection, userID) -
    here I will query and mark the user's STATUs as Inactive.

    2b. If user is available then we need to update user's information. So calling
    loadUser(connection, userID) - which loads current information of the user.
    then, updateUserInfo(connection, userID) passing the same connection object.

    and later will come back to getUsers() and then commit the changes.
    getUsers() {
    ...
    try{
    result = false;
    connection = getConnection();
    ....
    run query and get users

    while (rs.next){
    userID = rs.getString(1);
    getUserAvailabilityInformation(connection, userID);
    result = true;
    }
    finally{
    if (resultSet != null)
    resultSet.close();
    if (prepStatement != null)
    prepStatement.close();
    if (result)
    connection.commit();
    if (connection != null)
    connection.close();
    }

    Problem:

    Getting exception :

    ERROR LogWriter.error(LogWriter (?) - [Severity 2] DBUtil.findBySql(): Caught SQL exception:
    com.ibm.db2.jcc.a.SqlException: Processing was cancelled due to an interrupt.
    at com.ibm.db2.jcc.a.zc.b(zc.java(Compiled Code))
    at com.ibm.db2.jcc.a.zc.a(zc.java(Inlined Compiled Code))
    at com.ibm.db2.jcc.c.jb.e(jb.java(Inlined Compiled Code))
    at com.ibm.db2.jcc.c.jb.n(jb.java(Compiled Code))
    at com.ibm.db2.jcc.c.jb.j(jb.java(Compiled Code))
    at com.ibm.db2.jcc.c.jb.c(jb.java(Compiled Code))
    at com.ibm.db2.jcc.c.w.c(w.java(Inlined Compiled Code))
    at com.ibm.db2.jcc.c.cc.h(cc.java(Compiled Code))
    at com.ibm.db2.jcc.a.hd.p(hd.java(Inlined Compiled Code))
    at com.ibm.db2.jcc.a.hd.a(hd.java(Compiled Code))
    at com.ibm.db2.jcc.a.hd.a(hd.java(Compiled Code))
    at com.ibm.db2.jcc.a.hd.executeQuery(hd.java(Compiled Code))
    at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.pmiExecu teQuery(WSJdbcStatement.java(Compiled Code))
    at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeQ uery(WSJdbcStatement.java(Compiled Code))
    at com.ibm.ontarget.util.DBUtil.executeQuery(DBUtil.j ava(Compiled Code))
    at com.ibm.ontarget.util.DBUtil.findBySql(DBUtil.java (Compiled Code))


    WSRdbManagedC W DSRA0080E: An exception was received by the Data Store Adapter. See original exception message: com.ibm.db2.jcc.a.SqlException: invalid operation: connection closed
    at com.ibm.db2.jcc.a.p.Bb(p.java(Compiled Code))
    at com.ibm.db2.jcc.a.p.getAutoCommit(p.java:1067)
    at com.ibm.db2.jcc.a.dc.getAutoCommit(dc.java:157)
    at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImp l.introspectSelf(WSRdbManagedConnectionImpl.java:1 110)
    at com.ibm.ws.rsadapter.FFDCLogger.introspect(FFDCLog ger.java:169)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.introsp ectSelf(WSJdbcConnection.java:1516)


    and then...

    ConnectionEve A J2CA0056I: The Connection Manager received a fatal connection error from the Resource Adaptor for resource jdbc/otaDevDome. The exception which was received is com.ibm.websphere.ce.cm.StaleConnectionException: A communication error has been detected. Communication protocol being used: Reply.fill(). Communication API being used: InputStream.read(). Location where the error was detected: insufficient data. Communication function detecting the error: *. Protocol specific error codes(s) TCP/IP SOCKETS DB2ConnectionCorrelator: G92084A6.P7AD.110628141437
    [6/28/11 10:43:59:313 EDT] 0000003d SystemOut O ERROR util.LogWriter (306) - [Severity 2] com.ibm.ontarget.infrastructure.AsynchProfileUpdat e.updateUserProfile():
    com.ibm.websphere.ce.cm.StaleConnectionException: A communication error has been detected. Communication protocol being used: Reply.fill(). Communication API being used: InputStream.read(). Location where the error was detected: insufficient data. Communication function detecting the error: *. Protocol specific error codes(s) TCP/IP SOCKETS DB2ConnectionCorrelator: G92084A6.P7AD.110628141437
    at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(NativeConstructorAccessorImpl.java(Compiled Code))
    at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(DelegatingConstructorAccessorImpl.java(Com piled Code))
    at java.lang.reflect.Constructor.newInstance(Construc tor.java(Compiled Code))
    at com.ibm.websphere.rsadapter.GenericDataStoreHelper .mapExceptionHelper(GenericDataStoreHelper.java:50 1)
    at com.ibm.websphere.rsadapter.GenericDataStoreHelper .mapException(GenericDataStoreHelper.java:544)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcUtil.mapException( WSJdbcUtil.java(Compiled Code))



    May I know how do we check if multiple connection object are trying to run same time and causing the above issue?

    And above exception we are getting after updating some users information i.e. more than 250 users.

    Please Advice.

    Thanks,
    Chinnu

  2. #2
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,642
    Rep Power
    6

    Default

    It helps to post an SSCCE which demonstrates the problem (use the code tags), otherwise we are left guessing back and forth, making assumptions which may not be correct, and a whole slew of other things. This being said, the stack trace say's "connection closed" - are you closing the connection anywhere else in the code?

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    18

    Default

    Sounds like DB2 got bored and disconnected you.
    I don't know how DB2s connections work, never having used it, so I can't say why.
    Have you tried not passing the connection around and simply grabbing a connection from the pool whenever you need one?

    I will say that above sequence looks wrong to me.
    You've got a lot of individual calls for single rows there.
    Can't you grab more data in one go?

    Presumably step 2 is hitting a different database (or data source anyway). Thene again, if that's the case why are you passing the same connection in?
    Could you get a list of all active user ids? Or are there significantly more on this other database than the 5000 you're working with?

Similar Threads

  1. JDBC | Unable to update multiple records
    By bittu218 in forum New To Java
    Replies: 5
    Last Post: 06-28-2010, 02:45 PM
  2. Throttling records
    By jitman in forum New To Java
    Replies: 0
    Last Post: 03-11-2010, 06:18 PM
  3. insert records
    By rudravaram in forum JDBC
    Replies: 3
    Last Post: 01-03-2010, 05:54 PM
  4. Query with 1 record vs. multiple records
    By anderma8 in forum JDBC
    Replies: 5
    Last Post: 07-09-2007, 11:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •