This chapter includes the following sections:
To make sure that the database connections in a data source remain healthy, you should periodically test the connections. WebLogic Server includes two basic types of testing: automatic testing that you configure with attributes on the data source and manual testing that you can do to trouble-shoot a data source.
Allowing WebLogic Server to automatically maintain the integrity of pool connections should prevent most DBMS connection problems. For more information about configuring automatic connection testing, see Connection Testing Options for a Data Source.
testPool() method in the JDBCDataSourceRuntimMBean.
JDBCDataSourceRuntimeMBean.testPoolTo test a database connection from a data source, Test Reserved Connections must be enabled and Test Table Name must be defined in the data source configuration. Both are defined by default if you create the data source using the WebLogic Server Administration Console.
When you test a data source, WebLogic Server reserves a connection, tests it using the query defined in Test Table Name, and then releases the connection.
WebLogic Server creates a statement cache of each connection in a data source. When a prepared statement or callable statement is used on a connection, WebLogic Server caches the statement so that it can be reused.
For more information about the statement cache, see Increasing Performance with the Statement Cache.
Each connection in the data source has its own statement cache, but configuration settings are made for all connections in the data source. You can clear the statement cache for all connections in a data source using the WebLogic Server Administration Console or you can programmatically clear the statement cache for an individual connection.
Note:
When the JDBC 4.0 setPoolable(false) method is called for a WebLogic data source that has prepared statement caching enabled, the statement is removed from the cache in addition to calling the method on the driver object.
You can manually clear the statement cache for all connections in a data source using the WebLogic Server Administration Console (see Clear the statement cache in a JDBC data source in Oracle WebLogic Server Administration Console Online Help) or with the clearStatementCache() method on the JDBCDataSourceRuntimeMBean.
JDBCDataSourceRuntimeMBean.clearStatementCache
weblogic.jdbc.extensions.WLConnection.clearStatementCache() weblogic.jdbc.extensions.WLConnection.clearCallableStatement(java.lang. String sql) weblogic.jdbc.extensions.WLConnection.clearCallableStatement(java.lang. String sql,int resType,int resConcurrency) weblogic.jdbc.extensions.WLConnection.clearPreparedStatement(java.lang. String sql) weblogic.jdbc.extensions.WLConnection.clearPreparedStatement(java.lang. String sql,int resType,int resConcurrency)
You can use methods in the weblogic.jdbc.extensions.WLConnection interface to clear the statement cache for a single connection or to clear an individual statement from the cache. These methods return true if the operation was successful and false if the operation fails because the statement was not found.
When prepared and callable statements are stored in the cache, they are stored (keyed) based on the exact SQL statement and result set parameters (type and concurrency options), if any. When clearing an individual prepared or callable statement, you must use the method that takes the proper result set parameters. For example, if you have callable statement in the cache with resSetType of ResultSet.TYPE_SCROLL_INSENSITIVE and a resSetConcurrency of ResultSet.CONCUR_READ_ONLY, you must use the method that takes the result set parameters:
clearCallableStatement(java.lang.String sql,int resSetType,int resSetConcurrency)
If you use the method that only takes the SQL string as a parameter, the method will not find the statement, nothing will be cleared from the cache, and the method will return false.
When you clear a statement that is currently in use by an application, WebLogic Server removes the statement from the cache, but does not close it. When you clear a statement that is not currently in use, WebLogic Server removes the statement from the cache and closes it.
For more details about these methods, see the Javadoc for WLConnection.
Use the Shrink option to drop some connections from the data source when a peak usage period has ended. This option frees up WebLogic Server and DBMS resources.
A data source has a set of properties that define the initial, minimum, and maximum number of connections in the pool (initialCapacity, minCapacity, and maxCapacity). A data source automatically adds one connection to the pool when all connections are in use. When the pool reaches maxCapacity, the maximum number of connections are opened, and they remain opened unless you enable automatic shrinking on the data source or manually shrink the data source with the shrink() method.
You may want to drop some connections from the data source when a peak usage period has ended, freeing up WebLogic Server and DBMS resources. You can use the Shrink option on the JDBC Data Source: Control page in the WebLogic Server Administration Console (see Shrink the connection pool in a JDBC data source in Oracle WebLogic Server Administration Console Online Help) or the shrink() method on the JDBCDataSourceRuntimeMBean.
JDBCDataSourceRuntimeMBean.shrink
When you shrink a data source, WebLogic Server reduces the number of connections in the pool to the greater of either the minCapacity or the number of connections currently in use. The pool is decreased gradually to minimize thrashing. The number of unused connections is cut in half each time automatic shrinking is performed.
Use the Reset option to close and recreate all available database connections in a data source.
reset() method on the JDBCDataSourceRuntimeMBean.
JDBCDataSourceRuntimeMBean.reset
This may be necessary after the DBMS has been restarted, for example. Often when one connection in a data source has failed, all of the connections in the pool are bad.
Use the Suspend and Force Suspend options to suspend a data source.
The Suspend and Force Suspend options can be reserved on the JDBC Data Source: Control page in the WebLogic Server Administration Console (see Suspend JDBC data sources in Oracle WebLogic Server Administration Console Online Help) or the suspend() and forceSuspend() methods in the JDBCDataSourceRuntimeMBean.
JDBCDataSourceRuntimeMBean.suspend JDBCDataSourceRuntimeMBean.forceSuspend
When you suspend a data source (not forcibly suspend), the data source is marked as disabled and applications cannot reserve connections from the pool. Applications that already have a reserved connection from the data source when it is suspended will get an exception when trying to reserve the connection. WebLogic Server preserves all connections in the data source exactly as they were before the data source was suspended.
When you gracefully suspend a data source, the following occurs:
The data source is immediately marked as suspended at the beginning of the operation and no further connections are created on the data source.
Idle (not reserved) connections are marked as disabled.
After a timeout period for the suspend operation, all remaining connections in the pool are marked as suspended and the following exception is thrown for any operations on the connection, indicating that the data source is suspended:
java.sql.SQLRecoverableException: Connection has been administratively disabled. Try later.
If graceful suspend is done as part of a graceful shutdown operation, connections are immediately closed when no longer reserved or at the end of the timeout period. If not done as part of a shutdown operation, these connections remain in the pool and are not closed because the pool may be resumed.
A graceful suspend can be done synchronously or asynchronously.
The synchronous operation does not have a timeout period on the method. By default, the timeout period is 60 seconds. You can change the value of this timeout period by configuring or dynamically setting Inactive Connection Timeout Seconds to a non-zero value. There is no upper limit on the inactive timeout period. Note that the processing actually checks for in-use (reserved) resources every tenth of a second so if the timeout value is set to 2 hours and all reserved resources are released a second later, the shutdown will complete a second later.
The asynchronous operation takes a timeout value in seconds. It returns a JDBCDataSourceTaskRuntimeMBean that can be used to check the status of the operation. The getProgress()method returns TaskRuntimeMBean.PROGRESS_SUCCESS ("success"), TaskRuntimeMBean.PROGRESS_FAILED ("failed"), or TaskRuntimeMBean.PROGRESS_PROCESSING ("processing"). The getStatus() method returns "SUCCESS", "FAILURE", and now "PROCESSING". There can be multiple task MBeans in existence. The next operation call on the datasource will clean up MBeans for tasks that have been completed for at least 30 minutes. Note that once a suspend or shutdown operation is started, the other operations will fail immediately but a task MBean is still created. The isRunning() method returns true if suspend or shutdown is still running. Timeout of the operation is controlled by the timeout parameter on the new task operations. If set to 0, the default is used. The default is to use Inactive Connection Timeout Seconds if set or 60 seconds. If you want a minimal timeout, set the value to 1. If you want no timeout, set it to a large value (not recommended).
When you forcibly suspend a data source, all pool connections are destroyed and any subsequent attempt to use reserved connections fail. Any transactions on the connections that are closed are rolled back.
Use the Resume option to re-use a suspended data source.
resume() method on the JDBCDataSourceRuntimeMBean.
JDBCDataSourceRuntimeMBean.resumeWhen you resume a data source, WebLogic Server marks the data source as enabled and allows applications to reserve connections from the data source. If you suspended the data source (not forcibly suspended), all connections are preserved exactly as they were before the data source was suspended. Clients that had reserved a connection before the data source was suspended can continue exactly where they left off. If you forcibly suspended the data source, clients will have to reserve new connections to proceed.
Note:
You cannot resume a data source that did not start correctly, for example, if the database server is unavailable.
Use the Shutdown and Force Shutdown options to shut down a data source.
shutdown() and forceShutdown() methods in the JDBCDataSourceRuntimeMBean.
JDBCDataSourceRuntimeMBean.shutdown JDBCDataSourceRuntimeMBean.forceShutdown
A graceful (non-forced) datasource shutdown operation involves first gracefully suspending the data source and then releasing the associated resources including the connections. See the description above for details of gracefully suspending the datasource. After the datasource is gracefully suspended, all remaining in-use connections are closed and the datasource is marked as shut down.
A graceful shutdown can be done synchronously or asynchronously.
The synchronous operation does not have a timeout period on the method. The timeout period is 60 seconds by default. This can be changed by configuring or dynamically setting Inactive Connection Timeout Seconds to a non-zero value (note that this value is overloaded with another feature when connection leak profiling is enabled). There is no upper limit on the inactive timeout. Note that the processing actually checks for in-use (reserved) resources every tenth of a second so if the timeout value is set to 2 hours and it's done a second later, it will complete a second later.
The asynchronous operation takes a timeout value in seconds. It returns a JDBCDataSourceTaskRuntimeMBean that can be used to check the status of the operation. The getProgress() method returns TaskRuntimeMBean.PROGRESS_SUCCESS ("success"), TaskRuntimeMBean.PROGRESS_FAILED ("failed"), or TaskRuntimeMBean.PROGRESS_PROCESSING ("processing"). The getStatus() method returns "SUCCESS", "FAILURE", and now "PROCESSING". There can be multiple task MBeans in existence. The next operation call on the datasource will clean up MBeans for tasks that have been completed for at least 30 minutes. Note that once a suspend or shutdown operation is started, the other operations will fail immediately but a task MBean is still created. The isRunning() method returns true if suspend or shutdown is still running. Timeout of the operation is controlled by the timeout parameter on the new task operations. If set to 0, the default is used. The default is to use Inactive Connection Timeout Seconds if set or 60 seconds. If you want a minimal timeout, set the value to 1. If you want no timeout, set it to a large value (not recommended).
When you forcibly shut down a data source, WebLogic Server closes database connections in the data source and shuts down the data source. All current connection users are forcibly disconnected. For a sample WLST script that shuts down a data source, see the WLST exampleExample 5-1
Use the Start option to start a data source which has been shut down.
start() method in the JDBCDataSourceRuntimeMBean.
JDBCDataSourceRuntimeMBean.startInvoking the Start operation re-initializes the data source, creates connections and transitions the data source to a health state of Running.
-Dweblogic.resourcepool.max_test_wait_secs=xx .Here, xx is the amount of time, in seconds, WebLogic Server waits for connection test before considering the connection test failed. By default, a server instance is assigned a value of 10 seconds.
This command line flag manages failures, such as a DBMS network failure, which can cause connection tests and connections in use by applications to hang for extended periods of time (for example, 10 minutes). If the assigned time period expires, the server instance purges unused connections and puts a watch on connections that are in use by the application.
A value of ten seconds provides a reasonable amount of time to allow for peak stress loads, when a DBMS may temporarily halt responses to clients, and then resume service on existing connections. However, if the wait time is too long or too short, add the flag to the startWebLogic script used for starting the server with a value that is more appropriate for your environment. Setting the value for the amount of time to zero (0) seconds, causes the server to wait indefinitely on a hanging connection test.