RE: Relationship between JDBC Connections, Statements, ResultSets and open data paths

["Richards, Etienne (CAP, PTL)" <Etienne.Richards@xxxxxxxxxx>]

Martin,

It seems like your question is was geared more towards ODP's than statements
and result sets. Ignore this if I am wrong.

When you execute the same query more than once (using the same connection)
you will see a shared read member lock. This is a pseudo lock created by DB2
for optimization. You can not explicitly release this lock, it gets released
when you close the connection or when an exclusive lock is acquired on the
same table. So DB2 automatically closes it for you.

What I found is that a long running JDBC connection will show that 100's of
files are open, these are only because of the pseudo locks (it is a good
thing). I think in the more recent OS/400 version the number of pseudo locks
are not limited. If you are concerned you could configure the profile to
limit the max number of open files that the system will allow on a per
connection bases. I have also seen people close connections that have been
open for a very long time (I do not like this).

Etienne

-----Original Message-----
From: Eyers, Daniel [mailto:daniel.eyers@honeywell.com]
Sent: Friday, October 04, 2002 12:26 PM
To: 'java400-l@midrange.com'
Subject: RE: Relationship between JDBC Connections, Statements,
ResultSets and open data paths


The QZDASOINIT job is launched when the connection is established.  The
files are open when the SQL runs.

If you close the ResultSet, the Statement remains open (as does the
Connection).  If you close the Statement (without
closing the ResultSet) The Statement is closed but the Connection is still
open and the ResultSet object still has
scope.

Normally, we open Connection, Statement, and Result Set and we close them in
reverse order.  Just as a guess, it sounds
like either 1) you aren't closing everything and/or 2) you aren't closing
them in the proper sequence.

Incidently, we were experiencing much the same issues as you were recently
and found that a JavaBean wasn't closing
things properly... Once we fixed it, we went from 8 *pages* of QZDASOINIT
jobs to 2 pages.

Does that help???

dan

-----Original Message-----
From: McCallion, Martin [mailto:martin.mccallion@misys.com]
Sent: Friday, October 04, 2002 11:35 AM
To: 'java400-l@midrange.com'
Subject: Relationship between JDBC Connections, Statements, ResultSets
and open data paths


Can anyone tell me whether there's an easy way of determining how JDBC
Connections, Statements (Prepared or otherwise) and ResultSets relate to
open files/ODPs?  For example, if I get a ResultSet from querying a
file, presumably that file will be opened.  If I run the same query
again, I would not expect it to be opened again; but if I run a
different query?  And if I close the ResultSet, should the file be
closed?
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.
_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.