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

The statement caching stuff should be interesting when it becomes available.
My last comment on the subject: I do not think the connection manager should
touch the allocated statements when a connection is released back into the
pool. However, I do think that it would not be a bad idea for the Connection
itself to release those resources when it is closed/destroyed.

The interesting thing is that this problem occurs in other programming areas
as well (in java). For example, if you allocate any new Frame, Diolog, or
Graphic objects in AWT then they must be diposed of explicitly using
dispose() in order to free up the peer resources they use! (otherwise you
may run out of memory in Windows after a while, for example).

Luther

----- Original Message -----
From: <cujo@us.ibm.com>
To: <JAVA400-L@midrange.com>
Sent: Wednesday, 12 January 2000 23:00
Subject: Re: jdbc-IBM Connection Manager-Servlets and sql handles


> Yes, what you describe could be done.  It just has to be done at the
> Connection Manger level (above the JDBC driver level).  I think it is fair
> to say that it should be being done (or at least configurable to work that
> way).  The problem isn't a technical one, I simply wanted to explain why
> things are the way they are today - and why we can't provide a quick
> solution.
>
> The only reason we can't simply go in and just make it happen is that the
> JDBC driver is being written by AS/400 developers in Rochester and
> WebSphere is primarily written... er, somewhere else - Raleigh I believe,
> but don't hold me to that.  I don't know what exactly happens on other
> platforms when they "leak" database resources either.
> What I do know is that there is work being done on the connection manager
> for a future WebSphere release.  I have heard there is plans to do
> statement caching type support which is something that was mentioned in
one
> of the earlier posts in this thread.  I don't know exactly what is
planned,
> but I would think this sort of resource handling would be tightened up.
If
> I do learn anything concrete, I will pass it along through this group.
>
> 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"
>
>
>
>
> Gary L Peskin <garyp@firstech.com> on 01/12/2000 02:28:25 PM
>
> Please respond to JAVA400-L@midrange.com
>
> To:   JAVA400-L@midrange.com
> cc:
> Subject:  Re: jdbc-IBM Connection Manager-Servlets and sql handles
>
>
>
>
> Richard --
>
> Thank you for jumping in here.  I, too, think that this is interesting.
> I've never used the connection pooling in Websphere but I have read the
> documentation.  So, if my question doesn't make sense, please correct
> me.
>
> As I understand it, there are basically two ways to deal with Connection
> Manager connections.  I'll discuss the old method but operation under
> the new method (ie using DataSource) should be analogous.
>
> To get a connection manager connection, you call
> connMgr.getIBMConnection() and get back an IBMJdbcConn.  To get an
> actual data server connection, you call IBMJdbcConn.getJdbcConnection()
> which returns a Connection.
>
> Couldn't this return a subclass of a Connection (call it IBMConnection)
> that has the createStatement, prepareStatement, and prepareCall methods
> overridden?  The IBMConnection class could have a Field like
>
>      private Vector myStatements
>
> The new methods would call the corresponding method in the superclass
> (ie the one in Connection) and then add the returned Statement object to
> the myStatements vector.  The IBMJdbcConn.releaseIBMConnection() could
> then go into the IBMConnection object and close the statements which
> reside in myStatements.  As a bonus, you could override
> IBMConnection.close() to either do nothing, put out an error message, or
> call IBMJdbcConn.releaseIBMConnection().
>
> Am I missing something or is this not feasible?
>
> Gary
>
> cujo@us.ibm.com wrote:
> >
> > >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
+---