Re: QZDASOINIT jobs have many files open; recommend close Statement Handle and Connection Handle

Because I use a wrapper around the JT400 JDBC driver to override database files automatically, I also have debug statements that show how many connections are open and in use and I monitor the number of connections in use so that I can see if I left a prepared statement or record set open inadvertently. Memory leaks are easily created using SQL in Java if you don't keep track of the SQL transactions and properly manage them. I cleaned up a ton of mismanaged SQL after I saw how many SQL objects were left lying around.

I typically close the result set and then close the statement...I think there has been discussion here on that very subject before....

Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com


On 6/28/2011 4:26 PM, elehti@xxxxxxxxxxxxxxxxxx wrote:

Everyone,
Why do our QZDASOINIT jobs (from JDBC) open the same files repeatedly
until the job has 100+, 200+, 300+, and even 400+ files open?. Bryan
Dietz' WRKODBCJOB command shows all the files.
If IBM's recommendation solves our issue, I will be very happy and share
our success with you on this forum:

IBM's recommendation is:
A JDBC application should close the statement handle after it is done
with the results set. These days, for performance reasons, the
connection is commonly left open or returned to a connection pool. It
is important to close statement handles so that not too many resources
are tied up on the system.

The following has a code snippet of closing both the statement handle
and the connection handle:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzahh/jdbc
rs.htm
Search for:
The AS400JDBCConnectionPool class represents a pool of
AS400JDBCConnection objects that are available for use by a Java(tm)
program as part of IBM(r) Toolbox for Java support for the JDBC 2.0
Optional Package API.

Search also for:
You cannot change the connection pool data source after you have
requested a connection and the pool is in use. To reset the connection
pool data source, first call close() on the pool.
Return connections to an AS400JDBCConnectionPool by using close() on the
AS400JDBCConnection object.
Note: When connections are not returned to the pool, the connection pool
continues to grow in size and connections are not reused.
Set properties on the pool by using methods inherited from
ConnectionPool. Some of the properties that you can set include:
* maximum number of connections to allow in the pool
* maximum lifetime of a connection
* maximum inactivity time of a connection.