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

[Vanja Jovic <vanjajovic@xxxxxxxxxxxx>]

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.