SQLSTATE 25501 invoking SPs from a J2EE DataSource connection

[hal9000cr]

We are running into some problems with the execution of DB2 stored
procedures when their isolation level is set to:

set transaction isolation level READ UNCOMMITTED, READ WRITE;

This is a web application running in JBoss 4.0.1 where a standard
DataSource in JBoss is defined for connections against a DB2 database
on an AS/400 (VRM = V5R2M0). The driver is established as: <driver-
class>com.ibm.as400.access.AS400JDBCDriver</driver-class>, we are
using JTOpen 5.4.

The data source provides connections fine, we ask the data source to
give us a connection and then the connection is used to invoke a
Stored Procedure. On most stored procedures this works fine since most
of them read information but when two procedures that modify the DB
with inserts or updates are invoked in a row the second procedure does
not run and reports an 25501 error.

According to DB2 documentation this is associated to:

"SQL0428N DISCONNECT cannot be issued if a connection it is directed
against has executed SQL within the unit of work.
Explanation: DISCONNECT will not be executed against a connection in
the following cases:
If SQL has been issued against the connection within the unit of work.
sqlcode: -428
sqlstate: 25501"

If we use a direct JDBC connection for each SP invocation we do not
get this error, it is when its called with the DataSource connection
that this error happens. We can't afford a new connection for every SP
we have to invoke because the AS/400 slows way down when a lot of
connections are requested and this is a very active system.

The SP that does inserts/updates set their isolation level with the
following settings:

set transaction isolation level READ UNCOMMITTED, READ WRITE;

Has anybody had this problem? and better yet: how was it solved?

Thanks.

[Karl Hanson]

ha*******@gmail.com wrote:

We are running into some problems with the execution of DB2 stored
procedures when their isolation level is set to:

set transaction isolation level READ UNCOMMITTED, READ WRITE;

This is a web application running in JBoss 4.0.1 where a standard
DataSource in JBoss is defined for connections against a DB2 database
on an AS/400 (VRM = V5R2M0). The driver is established as: <driver-
class>com.ibm.as400.access.AS400JDBCDriver</driver-class>, we are
using JTOpen 5.4.

The data source provides connections fine, we ask the data source to
give us a connection and then the connection is used to invoke a
Stored Procedure. On most stored procedures this works fine since most
of them read information but when two procedures that modify the DB
with inserts or updates are invoked in a row the second procedure does
not run and reports an 25501 error.

According to DB2 documentation this is associated to:

"SQL0428N DISCONNECT cannot be issued if a connection it is directed
against has executed SQL within the unit of work.
Explanation: DISCONNECT will not be executed against a connection in
the following cases:
If SQL has been issued against the connection within the unit of work.
sqlcode: -428
sqlstate: 25501"

If we use a direct JDBC connection for each SP invocation we do not
get this error, it is when its called with the DataSource connection
that this error happens. We can't afford a new connection for every SP
we have to invoke because the AS/400 slows way down when a lot of
connections are requested and this is a very active system.

The SP that does inserts/updates set their isolation level with the
following settings:

set transaction isolation level READ UNCOMMITTED, READ WRITE;

Has anybody had this problem? and better yet: how was it solved?

If this is still unresolved and -428 is returned from the server, below
is some message detail (from V5R4M0 VRM). If the connection is not at a
commit boundary when SP1 calls SP2 (and SP2 issues SET TRANSACTION..)
that might be the cause.

===wrkmsgd sql0428 qsqlmsg
Message ID . . . . . . . . . : SQL0428

Message file . . . . . . . . : QSQLMSG

Library . . . . . . . . . : QSYS

Message . . . . : SQL statement cannot be run.

Cause . . . . . : The statement cannot be run in the current
application state. A SET TRANSACTION, DISCONNECT, or SET SESSION
AUTHORIZATION statement was encountered and a connection is not at a
commit boundary.
A SET SESSION AUTHORIZATION statement was encountered and one of
the
following has occurred:

-- The SYSTEM_USER is one of the system-supplied user profiles such
as QSYS, QDFTOWN, or QSPL.

-- The current server is a local relational database and there is
an
active connection to a remote relational database.

-- A stored procedure, user-defined function, or trigger is
running.
-- Resources are being held because a COMMIT HOLD or HOLD LOCATOR

statement has been run.

-- The maximum number of ProfileHandles have been generated.

Recovery . . . : Do one of the following actions prior to running the
SQL statement. Try the request again.

-- If the connection is not at a commit boundary, issue a COMMIT or

ROLLBACK SQL statement.

-- If connections to remote relational databases are active,
disconnect the remote connections.

-- If the SYSTEM_USER is one of the system-supplied user profiles,
sign-on with a different user profile.

-- If resources are being held because a COMMIT HOLD has been run,
issue a COMMIT or ROLLBACK SQL statement.

-- If resources are being held because a HOLD LOCATOR has been run,
issue a ROLLBACK or FREE LOCATOR SQL statement.

-- If the maximum number of ProfileHandles have been generated,
release some of the ProfileHandles that are no longer needed using the
Release ProfileHandle (QSYRLSPH) program.
--
Karl Hanson