Prepared Statement Caching in WebSphere Application Server v5.0 for z/OS
 Technote (troubleshooting)
 
Problem(Abstract)
java.sql.SQLException: DB2SQLJJDBCProfile Error: No more JDBC Cursors without hold
 
Cause
The number of cached result sets was larger than the number of JDBC cursors that could be used
 
Resolving the problem
The prepared statement cache is used by WebSphere® Application Server and is a significant performance improvement. However, each prepared statement
that is cached will have a result set associated with it, and this result set is still associated with the prepared statement in the cache.
Each of these prepared statements will have a unique JDBC cursor attached to it. Until the prepared statement is cleared out of the cache, these said
cursors will not be released. DB2® defaults to 100 available cursors per connection, and WebSphere Application Server defaults to 10 cached statements (per connection) for the prepared statement cache -
if there are more of these cached statements than cursors, the following error will occur:
java.sql.SQLException: DB2SQLJJDBCProfile Error: No more JDBC Cursors without hold

There are at least 2 ways to avoid running out of cursors in this scenario:

1. Set the Aged Timeout value in the Administrative Console to something reasonable. This should be longer than the maximum amount of time you would expect a transaction to take, where result sets are generated.
2. Set the Statement Cache Size in the Administrative Console to 100 or less.

The application developer could also write the prepared statement to be generic enough to where the statement can be reused by multiple users and/or connections.
 
 
 


Document Information


Current web document: swg21116269.html
Product categories: Software > Application Servers > Distributed Application & Web Servers > WebSphere Application Server for z/OS > DB Connections/Connection Pooling
Operating system(s): z/OS
Software version: 5.0
Software edition:
Reference #: 1116269
IBM Group: Software Group
Modified date: May 19, 2004