[z/OS]

JDBC tuning tips for use with DB2

WebSphere® Application Server uses JDBC prepared statement caching as a performance enhancing feature. If you are using this feature together with DB2® for z/OS®, be aware of the potential impact on the number of DB2 JDBC cursor objects available.

Prepared statement caching effects on DB2 for OS/390 JDBC cursor objects

When you obtain a ResultSet object by running a PreparedStatement object, a DB2 JDBC cursor object is bound to it until the corresponding DB2 prepared statement is closed. This happens when the DB2 Connection object is released from the WebSphere Application Server connection pool. From an application perspective, the result set, prepared statement, and connection are each closed in turn. However, the underlying DB2 Connection is pooled by the WebSphere Application Server, the underlying DB2 PreparedStatement is cached by the application server, and each underlying DB2 JDBC cursor object associated with each ResultSet created on this PreparedStatement object is not yet freed.

Each PreparedStatement object in the cache can have one or more result sets associated with it. If a result set is opened and not closed, even though you close the connection, that result set is still associated with the prepared statement in the cache. Each of the result sets has a unique JDBC cursor attached to it. This cursor is kept by the statement and is not released until the prepared statement is cleared from the WebSphere Application Server cache.

If there are more of the cached statements than there are cursors, eventually the execution of a PreparedStatement object results in the following exception:
java.sql.SQLException: DB2SQLJJDBCProfile Error: No more JDBC Cursors without hold

Some WebSphere Application Server tuning suggestions to help avoid this problem are:

  1. Decrease the statement cache size setting on the DB2 for OS/390 data source definition. Setting this value to zero (0) eliminates statement caching, but causes a noticeable performance impact.
  2. Decrease the minConnections connection pool setting on the DB2 for OS/390 data source definition.
  3. Decrease the Aged Timeout connection pool setting on the DB2 for OS/390 data source definition. However, it is NOT recommended that you set this to zero (0), as this disables the Aged Timeout function.



Related reference
Data source minimum required settings, by vendor
DB2 tuning parameters
Reference topic Reference topic    

Terms and conditions for information centers | Feedback

Last updatedLast updated: Jun 11, 2013 8:40:09 AM CDT
http://www14.software.ibm.com/webapp/wsbroker/redirect?version=v701sca&product=was-nd-mp&topic=rprf_wastundb2
File name: rprf_wastundb2.html