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

>I personally think your suggestion is great.  <...>  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.


Here is the line that is in error:
"Seems to me the connection manager has all the information needed to clean
up the resources when I release the connection."

You are right that there are no serious technical hurdles.  I think the
only thing you are missing is the concept of all the levels in place from
the raw database up to where your application sits.  I sit in the middle
(and can only see a couple levels in each direction, but think I can
explain why you don't have the behavior you suggest today).

You are right that the JDBC drivers have to keep track of everything so
that Connection.close() can close the statement handles under it (in the
same way that Statement.close() can close the ResultSet cursors open under
it).  The problem is that the JDBC specification doesn't provide a common
mechanism for a connection to spew information about the statements that
are under it.

The problem is that a connection manager built on top of JDBC has access to
the connection object that they are pooling, but no way to find out all the
statements.  If there was a standard enumeration interface for the
Statements under a connection, doing this cleanup for the user would be
trivial at the connection manager level.  Without that, it is still trivial
if you are writing code for one system that is going to use one JDBC
driver.  Not so simple when you write for many platforms and many JDBC
drivers.

Interestingly (to me, maybe not to the rest of you), it is exactly the fact
that the JDBC driver has to hang onto these references that messes
everything (instead of making the solution).  Here is why:

1) The JDBC driver has to call all the closes on the statements when the
connection.close() is called.
2) Therefore it has to keep references to the Statements in the Connection
close (or else what would I call close on)
3) Because of the references to the Statements in the connection (which is
still active - this is a connection pool) the garbage collector can't
destroy these objects (that is run the object finalizers - which would
release the SQL handles).
4) When the user tried to get a statement and had run out of handles (a
specific AS/400 Error), I can catch it and clean up the handles not in use
anymore, but I have no clue which ones are not in use at the JDBC driver
level.  Therefore my hands end up tied as well.

I have been doing a little reading about object references that would still
allow the garbage collector to run (recent Dr. Dobb's issue), and will
perhaps find a solution along these lines.  Another area we have
investigated is a callback mechinism from the CLI to the JDBC driver on
connection close.  This would allow the JDBC driver to not have the object
references and the garbage collector could be used to free resources.

I will propose that we do something in this area (just a matter of finding
the time to fully do something), but I hope this gives some background as
to why it doesn't happen today.

BTW:  I really appreciate this kind of posting in this group, Alex and
Gary.  For you to have issues, bring them forward, suggest how to work
around them and post the solutions that exist today does wonderful things
for our other customers.  It also helps people like myself work out how
best to prioritize and understand what we can do to help our customers.

Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"TRUE! nervous, very, very dreadfully nervous I had been and am; but why
WILL you say that I am mad?
The disease had sharpened my senses, not destroyed, not dulled them. "

- Edgar Allan Poe
"The Tell-Tale Heart"




"Alex Garrison" <agarrison@logtech.com> on 01/12/2000 07:49:25 AM

Please respond to JAVA400-L@midrange.com

To:   JAVA400-L@midrange.com
cc:
Subject:  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
+---



+---
| 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
+---