Stored procedure and close connection

Hi Gilles.

I don't have the answer but do have a few questions on your environment.

1 are you using toolbox or native jdbc driver for a data source?
2 are you using a try | catch | finally construct to ensure:
        stmt and connection is closed in your app thread?
3 have you implemented a destroy method for your servlet or jsp that is
called  to ensure stmt and connection are closed?
4 your callable stmt is a prepared call probably that is cached in the sql
package for reuse
6 what's the transaction isolation level specified on the jdbc connection
settings?
        - maybe the isolation level has an impact on the behavior
        of the datasource, try repeatable read.  Although this
        is a call statement, I don't know if that might impact
        the behavior of the call client....
7 is there a potential problem with sql server mode running or not running
correctly servicing the was job?
        (see db2 as400 joblog)
8 what are the settings for connection timeout vs idle and orphan on the
        was datasource?
        - maybe the 2nd app is getting connected to your callable
        stmt that hasn't been closed and it's connection wasn't closed
        as well...

Thanks...

Jim Mason


Message text written by INTERNET:java400-l@midrange.com
>
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--
[ Picked text/plain from multipart/alternative ]
Hello everybody,

I have had a strange problem on my system and i am wondering what is
happening when, from the browser we clic on
a new link while a stored procedure is being executed.

Let me explain.

We use WAS3.5 on the AS400, and the IBM Data Access Beans (com.ibm.db.*) in
our java code

I have done the following:

Connected to our system through the internet.

Information have been accessed into our database:
        - Get a connection from the pool
        - Call a stored procedure

Since it was long to get a response, i have decided to clic on another
link.

what is happening with Websphere? What happens to the thread that was
handling my DB request?
At the end of the method that was called i have a try {} finally to make
sure the connection is always returned.

I have this question, because a colleague of mine get connected at the same
time, and got the result of my query !!!
I have checked all my code, it seems to be ok, completely thread safe. It
is
the very first time we have this (since more than 1 year).

So i was wondering if the connection had been released properly or not, and
if the other user could have received my connection.

Is it possible that the connection has been released to the pool and have
been given to the other user, but the stored procedure was still running
(there is no new job with this stored proc). As a result the other user got
my result.

I don't think it hasnot been released because i have this finally in my
code.
I am quite sure that our java code is thread safe (checked and rechecked,
plus ok since more than a year)

Any idea?

Gilles Ducret
Wealth Management Division
IT Architect
Lloyds TSB Bank
Tel: + 41 22 307 31 50
Mob: +41 79 217 21 41


<