+

Search Tips   |   Advanced Search

(ZOS) JDBC tuning tips for use with DB2

WebSphere Application Server uses JDBC prepared statement caching as a performance enhancing feature. If we 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 we 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 WAS 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 WAS, 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 WAS 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 WAS 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 we set this to zero (0), as this disables the Aged Timeout function.

  • Data source minimum required settings, by vendor
  • DB2 tuning parameters