RE: problem multi-threading a stored procedure

The connection object represents a single job on the as400/iSeries - you can
even ask it for the job information:

if (con instanceof com.ibm.as400.access.AS400JDBCConnection) {
        com.ibm.as400.access.AS400JDBCConnection as400Con =
(com.ibm.as400.access.AS400JDBCConnection) con;
        String jobId = as400Con.getServerJobIdentifier();
        System.out.println("JDBC Server Job Identifier is "
                + jobId.substring(20).trim()
                + "/"
                + jobId.substring(10, 20).trim()
                + "/"
                + jobId.substring(0, 10).trim()
                + ".");

This is invaluable when you want to debug stored procedures calls.

First thing to check is that the result set has been explicitly closed after
being used, if not the SQL cursor stays open, and no-one else can do
anything much.

If that doesn't help, try closing the CallableStatement after each request -
it sounds counter-intuitive, but later JDBC drivers perform statement
pooling behind the scences anyway.

Chris.

-----Original Message-----
From: TitanRebel [mailto:TitanRebel@xxxxxxxxxxx]
Sent: 07 March 2003 19:24
To: java400-l@xxxxxxxxxxxx
Subject: problem multi-threading a stored procedure


I am having a problem with a stored procedure.  Hopefully, someone has 
experience with this.  Here is my environment.

- Jakarta Tomcat 4.0.4 running on iSeries.
- Servlet calls a stored procedure to get list of results.
- Stored Procedure is written in RPG, running on same iSeries.

Everything works great when there are not simultanious requests to the 
same Stored Procedure.  If two users request the results at 
approximately the same time, one of them doesn't get any results.  From 
what I can tell, the second person to request the results get the 
results, and the first person gets none.

I have verified that the Servlet (as expected) is being executed in two 
seperate threads.  The Connection and the CallableStatement are declared 
as fields (class level variables).  The ResultSet is a local variable 
that immediately gets turned into an ArrayList which is passed to a JSP 
page for display.  So, I am using the same Connection for every user, 
and calling the "executeQuery()" method on the same CallableStatement.

Can anyone see what is wrong?  My guess is that this connection is being 
serviced by the same QZDASOINIT prestart job, therefore it is 
technically the same job that is calling the Stored Procedure each time, 
and that is why it can't handle multi-threading.  Does anyone know how 
to get around this?  Should I create a seperate Connection object for 
each user?  That seems ineffecient.

_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.