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

Martin,

you can look at it this way:

Connection is equivalent of job. Combination of statement/result set can
be seen as an equivalent of ODP. Same query executed by different
statements will cause multiple data paths to the same file. They remain
open until explicitly closed or until statement is reused for another
query to a different file (executing query (result set) over file B from
statement that vas previously used to get result set from file A, and
consequently held file A opened, will cause file A to be closed and file
B to be opened).

One thing to point out is that people tend to forget to properly tune
their systems up for the client/server type applications. Subsystems
QSYSWRK or QSERVER (depending on what type of processing is used) are
often assigned inadequate shared memory pools (which is usually a
default setup), with no enough memory and low activity levels. Combine
that with the bad habits when it comes to closing unused statements and
connections, and what you get is high number of page faults and high
number of wait/ineligible job transitions within those memory pools.
Somewhere there probably is a reason for high processor usage.


Hope some of this helps,

Vanja Jovic,
Canada


martin.mccallion@misys.com wrote:

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?

The background to all this is that one of our clients (we are a software
house) is experiencing extreme CPU usage by their QZDASOINIT jobs; on
looking at the jobs they find that they have many more files open than
seems right, including multiple opens of the same files.  The jobs are
being used by Connections from our application (which in this case is
running on Solaris, but being Java that need not always be so).

It may be that the high CPU usage and the multiple open files are not
related, but something strange is definitely going on, and I'd like to
find out how these things are related.  The archives, IBM's site, and
the web generally have not been illuminating so far.

TIA.

Cheers,

Martin.

--
Martin McCallion
Senior Technical Consultant
Work:  martin.mccallion@misys.com
Home: martin.mccallion@ukonline.co.uk
Misys International Banking Systems
1 St George's Road, London, SW19 4DR, UK
T +44 (0)20 8486 1951
F +44 (0) 20 8947 3373
www.misys.com
This email message is intended for the named recipient only. It may be
privileged and/or confidential. If you are not the  intended named
recipient of this email then you should not  copy it or use it for any
purpose, nor disclose its contents to any other person. You should
contact Misys International Banking Systems as shown below so that we
can take appropriate action at no cost to yourself.
Misys International Banking Systems Ltd,1 St
George's Road, London, SW19 4DR, UK. Email: ibs.postmaster@misys.com.
Tel: +44 (0) 20 8879 1188 Fax: +44 (0) 20 8947 3373
Misys International Banking Systems Ltd is
registered in England and Wales under company no. 971479
_______________________________________________
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.