Re: jdbc-IBM Connection Manager-Servlets and sql handles

Gary,

> Do you see a downside to asking IBM to enhance the
> releaseIBMConnection() method to call Statement.close() on any open
> statements?

I personally think your suggestion is great.  There would certainly be no
downside from our application development standpoint.  I cant think of any
performance downside either (whether I close() the Statements or the
connection manager closes them, they still have to be closed).  The Sun
java.sql.Connection documentation says that Connection.close() will release
all database and jdbc resources.  To me this means the Connection class has
the ability to find out which resources (like Statement objects which have
allocated sql handles) are associated with the Connection.  Now since I am
using the IBM connection manager, I have to let the connection manager
open/close connections anyway.  Seems to me the connection manager has all
the information needed to clean up the resources when I release the
connection.  Of course there could be lots of tech hurdles of which I am
unaware.

We didnt have any discussions with IBM to request this change to the
releaseIBMConnection() method - I was just too focused on the problem at
hand.  Frankly, it took so long to get to the people who could help me that
all I really wanted to do is hang up and kick something.  I get so
frustrated with the polite ignorance of level 1 support and bouncing from
database to client access to cta that I am a "difficult" customer by the
time I get to the polite helpfullness of level 2...... Anyway, that's a
different topic.

What does everyone else think?  Has anyone run into this before?  Is
everyone ABSOLUTELY sure that you close all statements?  You might want to
run a sql cli trace for a few hours and see how many handles you really do
have open.  Running the trace is easy if you have v4r3 or better (you dont
need to buy the Common Programming API toolkit).  From any green-screen
command prompt:

                ADDENVVAR QIBM_USRTRC_LEVEL 'INFO'
                CHGUSRTRC JOB(xxxxxx/QTMHHTTP/yyyyyyy) MAXSTG(16382)
CLEAR(*YES)

where xxxxxx = the job number of the BCH job corresponding to your websphere
instance.  yyyyyy = job name of the websphere instance.

Exercise your servlets that do jdbc and let the trace run for awhile.  Use
the DMPUSRTRC command to generate a listing of the trace to either your
screen or a file.  Look at the sql handle numbers.  If the numbers are
fairly low and you see them reused, great.

Alex Garrison

----- Original Message -----
From: Gary L Peskin <garyp@firstech.com>
To: <JAVA400-L@midrange.com>
Sent: Wednesday, January 12, 2000 12:00 AM
Subject: Re: jdbc-IBM Connection Manager-Servlets and sql handles


> Alex --
>
> Do you see a downside to asking IBM to enhance the
> releaseIBMConnection() method to call Statement.close() on any open
> statements?  Seems like if you assumed it would work that way, others
> will too.  I don't see any justification for keeping the Statement alive
> so it seems like the method should work as you first assumed.  It sort
> of beats having hard-to-diagnose handle leaks for no good reason.
>
> Did you have any discussions with IBM in this direction?
>
> Gary
>
> > Alex Garrison wrote:
> >
> > We recently had an interesting problem that took some help from
> > Rochester to pinpoint:
> >
> > After several days of uptime, jdbc accross all web server instances
> > in websphere would suddenly stop working.  All subsequent sql queries
> > would fail, even those from interactive green-screen sessions.  Our
> > only recovery would be to end and restart all websphere instances.
> > The problem seemed to happen randomly, at any time of the day or
> > night.  We were at a loss to explain the problem.
> >
> > We sent a sql cli trace to IBM and found out that we were running out
> > of free sql handles.  Long story short: You must call the
> > Statement.close() method when you are finished with a statement.  If
> > you return a connection back to the IBM connection manager with
> > IBMJdbcConn.releaseIBMConnection() without closing all the statements
> > on the connection, you will create a sql handle leak.  Eventually you
> > just run out of sql handles and, wham, you are stuck.  We had assumed
> > that the connection manager would clean up everything when we called
> > the releaseIBMConnection() method - it does not.
> >
> > So if any of your servlets are a little sloppy about calling
> > Statement.close(), fix it.
> >
> > BTW:  I would like to publicly thank the IBM guys that helped us nail
> > this problem.  I would also like to mildly rebuke those at IBM gave me
> > a hard time declaring this a severity 1 problem (I think bringing
> > websphere to its knees on a dedicated e-commerce as/400 is severity
> > 1).
> >
> >
> > Alex Garrison
> > agarrison@logtech.com
> > (423)636-7213
> +---
> | This is the JAVA/400 Mailing List!
> | To submit a new message, send your mail to JAVA400-L@midrange.com.
> | To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to
JAVA400-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner: joe@zappie.net
> +---
>

+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---