|
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. |
|
|
|
|
|
|