Re: Relationship between JDBC Connections, Statements, ResultSets and opendata paths

[Fred Kulack <kulack@xxxxxxxxxx>]

On 10/08/2002 at 01:42:49 PM, java400-l-admin@midrange.com wrote:
Can you please shed light on the following behaviour I observed recently.

I ran a stress-test, which was executing thousands of queries through JDBC
to DB/2 at AS/400 box.
Database connections were pooled, i.e. not closed at all.
When I watched particular database connection job, it had a number of open
files.
Over time, the number grew up to nearly 100, then in a few seconds went
down
to ~10, then grew back to nearly 100 again, then dropped back... and so on,
all the way.

Is it a sort of "garbage collection"?
--- end of excerpt ---

Hmm.... unfortunately, given just that information, I don't think I could
really come up
with a concrete "Here's what's going on". Perhaps just some discussion and
brainstorming
about steps to take might help.
I'm sure others will have some good input.

It could possibly be that you're allowing garbage collection to take care
of open
Statement objects instead of the application closing them (although that
seems sort of unlikely
as I wouldn't necessarily expect to run unless you're memory constrained).

If you're running native, you could turn on native JDBC tracing and see if
you see finalize()
methods being called. Use the Java command line parameters to define the
system
properties that native JDBC uses for tracing.
   -Djdbc.db2.trace=3 -Djdbc.db2.trace.config=file:///home/user/jdbc.%j.txt
Would turn on a verbose level of Native JDBC trace and put the output in
/home/user/jdbc.<jobname>.txt

You would see something like this in the trace if the finalizers were
running:
DB2Statement.finalize()  Warning:  Garbage Collector is closing statement
handle N
or
DB2ResultSet.finalize()  Warning:  Garbage Collector is closing ResultSet

Even if you don't see it, I might follow up a little on your closes of
Statement and
ResultSet objects and make sure you're doing the close in the correct
places
when you're finished with those objects.

Its hard to tell if those are psuedo-closed cursors or fully opened
cursors.
If they are psuedo-closed cursors, I'm not quite sure why they
would wax and wain like that. If they're open, perhaps your application
model fits
having many of those queries 'in flight' at the same time?
There are some QAQQINI settings that can control the number of
psuedo-closed
cursors and how they grow that could help you diagnose too.

As an information gathering tool, I might also create a small stored
procedure that retrieves
the current number of open cursors for the job, and call it intermittently
and see if you can get a
mental 'map' of how the number of open cursors in the backend relates to
what your application
is doing.

If anyone wants some example code, I've got a little utility I run in
qshell that I've used for
some monitoring similar to this (but from outside the job) that could be
modified to get
put into a stored procedure.



"The stuff we call "software" is not like anything that human society
  is used to thinking about. Software is something like a machine, and
  something like mathematics, and something like language, and
  something like thought, and art, and information...
  but software is not in fact any of those other things."
Bruce Sterling - The Hacker Crackdown

Fred A. Kulack - IBM eServer iSeries - Enterprise Application Solutions
ERP, Java DB2 access, Jdbc, JTA, etc...
IBM in Rochester, MN  (Phone: 507.253.5982   T/L 553-5982)
mailto:kulack@us.ibm.com   Personal: mailto:kulack@magnaspeed.net
AIM Home:FKulack  AIM Work:FKulackWrk
MSN Work: fakulack@hotmail.com