• Subject: Re: jdbc-IBM Connection Manager-Servlets and sql handles
  • From: Gary L Peskin <garyp@xxxxxxxxxxxx>
  • Date: Wed, 12 Jan 2000 12:28:25 -0800
  • Organization: The Firstech Corporation

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 thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].