Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Big JDBC program hangs; other DB2 functionality OK

    I have a JDBC program running against DB2 version 7 (sorry, don't know the fixpack right now) that consistently processes about 10,000 records, then hangs while waiting to return from an resultSet.next(). When it is in the hung state, the DB2 database responds to other clients, including other JDBC clients, just fine. Other JDBC clients that run my code will also eventually hit somewhere around 10,000 records (about 40 selects or inserts per record) and then hangs when attempting to step the result set into the first record of a select statement's result set.

    The code snippet where it hangs:

    rsCyclicAccountTrans = queryDailyTransTable(dbcon, firstTimeCycleCounterDate, cyclicEndDate, debug);

    Date firstTimeCyclicAccountTransDate = null;
    cyclicDayCount = 0;

    // hangs here
    System.out.println("About to step into first txn");
    System.out.flush();
    if((moreCyclicTxns = rsCyclicAccountTrans.next() {
    // never gets here
    System.out.println("new txn");
    System.out.flush();
    firstTimeCyclicAccountTransDate = rsCyclicAccountTrans.getDate("TRAN_DATE");
    }

    And queryDailyTransTable essentially executes a "select * from mytable". I have verified that it executes and returns a non-null result set.

    - The program used to run just fine, and processed 172,000 records about 3 weeks ago. The changes to the program were all minor.

    - I ran a thread dump on the program to prove that it is hanging and waiting for the server to return. I attached it to the bottom of this post. It’s clearly not getting stuck in a client-side problem, like an infinite loop or some sort of exception.

    - DB2 indicates that all the user and system tablespaces have plenty of space.

    - The program fails on a different record each time...although once, it failed on the same record. Specifically, it has failed on record 10725, 10725, 7015, and a few others (usually ending in a 5...hmmm).

    - The client and server have lots of available memory and lots of available disk space on all partitions.

    - The client CPU usage is fairly low (although it is running other processes besides mine, so not idle) when it is in a hung state. The server is busy, but topas reveals that it is busy doing work other than for my program.

    - I noticed a large number of problems on the IBM website related to DB2Jd, the JDBC daemon required to run on DB2 v.7 and earlier to support JDBC clients. It’s a running theory of mine that the thread that handles my program gets caught up at some point in time. This would explain why other JDBC programs can continue to run (since they are using different threads), and why the DB2 database continues to respond. IBM’s support database is filled with a lot of support issues related to the db2jd, so it makes me suspect it might be part of the problem.

    - Since I'm using DB2 v. 7, I'm compiling and running my client code against the 1.1.8 JDK. Another theory of mine is that this JDK or this driver has a known issue, since it's so old..

    - Future versions of DB2 have done away with DB2Jd, and instead support the universal JDBC drivers, which talk directly to the DB, and do not require a server-side daemon. I’ve heard rumors on this site that there’s a way to rig your client program to use the universal driver in order to talk to an older (version 7 or earlier) version of DB2. We’ve had trouble even trying to use different versions of the db2java.jar that come from different fixpaks of the same DB2 version, so I have doubts about this idea of using a driver for DB2 version 8…perhaps I can try it as a last resort, but it's not desired.

    - I made changes to the program so that all tables are set to turn logging off, but that did not solve the problem. I tried running the client program on a different machine, which has its own db2jd, but still hit the same problem. I also tried restarting the db2jd as well as the database instance, but no luck.

    - I dug around and found the jdbc error logs (jdbcerr.log) and db2 error logs (db2diag.log), but there were no clues that were clearly related to my problem. None of the logs contain any info that corresponds to the time when the program failed. DB2 is set to diag level=4.

    - My JDBC program never raises an exception.

    - In one case, when I left the program running over the weekend, the program did actually pick back up around the 80,000th record, processing just one more record, and then going back into a hung state.

    - The program takes about 2-3 hours to reach the hung state.

    - Turning up JDBC trace-level logging is a possibility that I'd like to avoid if possible, since it effects all JDBC clients (of which we have many), and would produce a tremendous amount of output for this program, which performs about 40 queries for each record (and processes 10,000+ records before hitting the problem spot).

    The stack trace snipped from the client program:

    > Current Thread Details

    > > ----- Monitors held -----

    > COM.ibm.db2.jdbc.net.DB2Connection@3003f538

    > COM.ibm.db2.jdbc.net.DB2Request@301ece18

    > COM.ibm.db2.jdbc.net.DB2PreparedStatement@301ece50

    > > ------ Java stack ------ () prio=1 *current thread*

    > COM.ibm.db2.jdbc.net.DB2Request.sendAndRecv(Compil ed Code)

    > COM.ibm.db2.jdbc.net.DB2Statement.close(Compiled Code)

    > COM.ibm.db2.jdbc.net.DB2Statement.finalize(Compile d Code)

    > ----------------------------------------------------------------------

    >

    >

    > Total Thread Count: 3

    > Active Thread Count: 3

    > JNI Thread Count: 0

    >
    ----- Java stack ------ () prio=1 *current thread*

    > COM.ibm.db2.jdbc.net.DB2Request.sendAndRecv(Compil ed Code)

    > COM.ibm.db2.jdbc.net.DB2Statement.close(Compiled Code)

    > COM.ibm.db2.jdbc.net.DB2Statement.finalize(Compile d Code)

    > ----------------------------------------------------------------------

    >

    > "main" (TID:0x30030858, sys_thread_t:0x20209fb8)

    > Native Thread State: ThreadID: 00000001 Reuse: 1 USER PRIMORDIAL

    > SUSPENDED

    > Native Stack Data : base: 2ff224fc pointer 2ff20940 used(7100)

    > free(250948)

    > ----- Monitors held -----

    > COM.ibm.db2.jdbc.net.DB2Socket@30032f28

    > COM.ibm.db2.jdbc.net.DB2PreparedStatement@3017efc8

    > COM.ibm.db2.jdbc.net.DB2Request@301ec198

    > ----- Native stack -----

    > _global_lock_common

    > pthread_mutex_lock

    > sysMonitorEnterQuicker

    > _jit_sysMonitorEnterFromAsm

    > JITSigSegvHandler

    > ------ Java stack ------ () prio=5

    > COM.ibm.db2.jdbc.net.DB2Statement.close(Compiled Code)

    > COM.ibm.db2.jdbc.net.DB2Statement.finalize(Compile d Code)
    > COM.ibm.db2.jdbc.net.DB2Request.receive(Compiled Code)
    > COM.ibm.db2.jdbc.net.DB2Request.sendAndRecv(Compil ed Code)
    > COM.ibm.db2.jdbc.net.DB2RowObject.forwardOnlyNext( Compiled Code)
    > COM.ibm.db2.jdbc.net.DB2ResultSet.next(Compiled Code)
    >
    > ***.processCycleData.instance_main(Compiled
    > Code)
    > ***.processCycleData.main(Compild

    > Code)
    > ----------------------------------------------------------------------




    Thanks for any help you can provide -- it's driving me nuts!

    --mike

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    I think you win the award for longest first post...

    Are you using the Net driver? (ie. through db2jd)? Or do you have a full client install?

    You might want to try a 'GET SNAPSHOT FOR ALL ON <dbname>' on the server, could shed light on a lock-wait or similar issue.

    Just some starting points.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Big JDBC program hangs; other DB2 functionality OK


    The stack trace snipped from the client program:

    > Current Thread Details

    > > ----- Monitors held -----

    > COM.ibm.db2.jdbc.net.DB2Connection@3003f538

    > COM.ibm.db2.jdbc.net.DB2Request@301ece18

    > COM.ibm.db2.jdbc.net.DB2PreparedStatement@301ece50

    > > ------ Java stack ------ () prio=1 *current thread*

    > COM.ibm.db2.jdbc.net.DB2Request.sendAndRecv(Compil ed Code)

    > COM.ibm.db2.jdbc.net.DB2Statement.close(Compiled Code)

    > COM.ibm.db2.jdbc.net.DB2Statement.finalize(Compile d Code)

    > ----------------------------------------------------------------------

    >

    >

    > Total Thread Count: 3

    > Active Thread Count: 3

    > JNI Thread Count: 0

    >
    ----- Java stack ------ () prio=1 *current thread*

    > COM.ibm.db2.jdbc.net.DB2Request.sendAndRecv(Compil ed Code)

    > COM.ibm.db2.jdbc.net.DB2Statement.close(Compiled Code)

    > COM.ibm.db2.jdbc.net.DB2Statement.finalize(Compile d Code)

    > ----------------------------------------------------------------------

    >

    > "main" (TID:0x30030858, sys_thread_t:0x20209fb8)

    > Native Thread State: ThreadID: 00000001 Reuse: 1 USER PRIMORDIAL

    > SUSPENDED

    > Native Stack Data : base: 2ff224fc pointer 2ff20940 used(7100)

    > free(250948)

    > ----- Monitors held -----

    > COM.ibm.db2.jdbc.net.DB2Socket@30032f28

    > COM.ibm.db2.jdbc.net.DB2PreparedStatement@3017efc8

    > COM.ibm.db2.jdbc.net.DB2Request@301ec198

    > ----- Native stack -----

    > _global_lock_common

    > pthread_mutex_lock

    > sysMonitorEnterQuicker

    > _jit_sysMonitorEnterFromAsm

    > JITSigSegvHandler

    > ------ Java stack ------ () prio=5

    > COM.ibm.db2.jdbc.net.DB2Statement.close(Compiled Code)

    > COM.ibm.db2.jdbc.net.DB2Statement.finalize(Compile d Code)
    > COM.ibm.db2.jdbc.net.DB2Request.receive(Compiled Code)
    > COM.ibm.db2.jdbc.net.DB2Request.sendAndRecv(Compil ed Code)
    > COM.ibm.db2.jdbc.net.DB2RowObject.forwardOnlyNext( Compiled Code)
    > COM.ibm.db2.jdbc.net.DB2ResultSet.next(Compiled Code)
    >
    > ***.processCycleData.instance_main(Compiled
    > Code)
    > ***.processCycleData.main(Compild

    > Code)
    >
    I'm not an expert in reading java trace so could you please clarify something for me?

    1. Your snippet mentions 3 active threads. Do you by any chance access the same result set from different threads? If yes, your threads may be locking out one another.

    2. Also, the stack trace indicates that the statement is being finalized before you're done with the result set that comes from that statement. If the Statement instance is local to the queryDailyTransTable() method then the JVM considers it unused once you return from that method and eventually attempts to GC it, thus invalidating the result set. If that's the case you may need to declare your Statement instance within the scope of the method that works with the results set so that the statement lives for as long as the result set itself.

  4. #4
    Join Date
    Apr 2004
    Posts
    2
    Thanks to both of you for answering and offering some help. Despite the fact that my post was quite long, I left out an important piece of information that I believe was the cause of the problem.

    Whenever I executed a query in my processing loop, I created a prepared statement inside a method, then executed it, and returned the result set. When I was done with the result set, I re-ran the query method, and re-assigned my result set to a new one.

    My assumption was that Java will automatically garbage-collect the result set and statement when all pointers to it are pointed to other things, or when the result sets and statement fall out of scope.

    In Java 1.4.2, that seems to be the case. Whenever I ran my program in my test environment, it worked just fine.

    In fact, the Java documentation seems to imply that it should:
    "A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results. "

    However, when I ran it against DB2, which supported Java 1.1.8, I kept hitting the same problem. My guess is that Java was not cleaning up the old statements or result sets, and the JDBC driver for DB2 stopped handing out new ones, which results in my program hanging indefinitely, without any error message.

    I added new bits of code to ensure that each statement and result set gets closed before I process the next record, and that fixed the problem. 30 hours later, my program finished

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Thanks for posting the solution.

    In general, relying on garbage collection can be tricky, since the JVM decides when to do it, there's no guarantee it will be done in a timely manner. Maybe the default thresholds and such have changed between the two versions?
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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