Unable to close a JDBC Connection to DB2 getting error

Have you tried issuing a connection.commit() or connection.rollback() prior to the close? It sounds like there is a transaction in progress when you are attempting the close.

> However I have closed the connection in my code.

Its closing the connection that is causing the exception.

Dean Horak,

I have already tried connection.commit() and connection.rollback().  They both cause the error message to cease.  My lead developer is concerned however that calling commit or rollback will have some as yet unsforseen side effects.  At this point however I am only performing selects.  Commit or rollback should not hurt any data as far as I can see.  Do you agree with my line of thinking?

Thanks, Jim W.

This depends on the implementation of the DBConnectionPool. Is this a homebrew implementation or are you using a package for this?

If the DBConnectionPool does not ensure that the connection is not in a transactional state when it distributes the the connections from the pool, it is possible that a previous user of the connection (before it was returned to the pool) may have failed to commit or rollback changes. This could be a problem. If the connection pool implementation does ensure that this can't happen, then you should be safe to do rollbacks or commits without effecting other pool users...

Dean,

DBConnectionPool is a "shopbrew" implementation that a fine developer here at my office wrote and has used for quite a few years.  I looked in the class and there is the method:

public void setTransactionIsolation(int level)

That sets the TransactionIsolation level to one of the following:

java.sql.Connection.setTransactionIsolation

java.sql.Connection.TRANSACTION_SERIALIZABLE, java.sql.Connection.TRANSACTION_REPEATABLE_READ, java.sql.Connection.TRANSACTION_READ_COMMITTED, and java.sql.Connection.TRANSACTION_READ_UNCOMMITTED transaction isolations are available from a Cloudscape database.

TRANSACTION_READ_COMMITTED is the default isolation level.

I checked and I don't think it is being set in my application.

You might try setting the isolation level to java.sql.Connection.TRANSACTION_NONE since you are only performing queries and don't need transaction support...

Oops.. I take that back, DB2 does not support this... however, TRANSACTION_READ_UNCOMITTED might be your best option since TRANSACTION_READ_COMMITTED does row locking....

Db2 JDBC Isolation Level mapping:
http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/r0010326.htm
"
JDBC Transaction Isolation Levels DB2 Isolation Levels
Connection.TRANSACTION_READ_UNCOMMITED -> Uncommitted read (UR)
Connection.TRANSACTION_READ_COMMITTED -> Cursor stability (CS)
Connection.TRANSACTION_REPEATABLE_READ -> Read stability (RS)
Connection.TRANSACTION_SERIALIZABLE -> Repeatable read (RR)
Connection.TRANSACTION_NONE is not supported on DB2 Universal Database
"

Locks and concurrency control in DB2:
http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/c0005266.htm
"
The duration of row locking varies with the isolation level being used:
    * UR scans: No row locks are held unless row data is changing.
    * CS scans: Row locks are only held while the cursor is positioned on the row.
    * RS scans: Only qualifying row locks are held for the duration of the transaction.
    * RR scans: All row locks are held for the duration of the transaction.

I am inserting and updating  rows at various places in the application.  Would TRANSACTION_READ_COMMITTED be better?