20 Managing WebLogic JDBC Resources

Learn how to use the WebLogic Server Administration Console, command line, JMX programs, or WebLogic Scripting Tool (WLST) scripts to manage the JDBC data sources in your domain.

This chapter includes the following sections:

Testing Data Sources and Database Connections

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.

To manually test a connection from a data source, you can use the Test Data Source feature on the JDBC Data Source: Monitoring: Testing page in the WebLogic Server Administration Console (see Test JDBC data sources) or the testPool() method in the JDBCDataSourceRuntimMBean.
JDBCDataSourceRuntimeMBean.testPool
To 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.

Managing the Statement Cache for a Data Source

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.

Clearing the Statement Cache for a Data Source

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

Clearing the Statement Cache for a Single Connection

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.

Shrinking a Connection Pool

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.

Resetting a Connection Pool

Use the Reset option to close and recreate all available database connections in a data source.

Reset option is available on the JDBC Data Source: Control page in the WebLogic Server Administration Console (see Reset connections in a JDBC data source in Oracle WebLogic Server Administration Console Online Help) or the 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.

Suspending a Connection Pool

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

Resuming a Connection Pool

Use the Resume option to re-use a suspended data source.

The Resume option is available on the JDBC Data Source: Control page in the WebLogic Server Administration Console (see Resume suspended JDBC data sources in Oracle WebLogic Server Administration Console Online Help) or the resume() method on the JDBCDataSourceRuntimeMBean.
JDBCDataSourceRuntimeMBean.resume
When 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.

Shutting Down a Data Source

Use the Shutdown and Force Shutdown options to shut down a data source.

The Shutdown and Force Shutdown options are available on the JDBC Data Source: Control page in the WebLogic Server Administration Console (see Shut down JDBC data sources in Oracle WebLogic Server Administration Console Online Help) or the 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

Starting a Data Source

Use the Start option to start a data source which has been shut down.

The Start option is available on the JDBC Data Source: Control page in the WebLogic Server Administration Console (see Start JDBC data sources in Oracle WebLogic Server Administration Console Online Help) or the start() method in the JDBCDataSourceRuntimeMBean.
JDBCDataSourceRuntimeMBean.start
Invoking the Start operation re-initializes the data source, creates connections and transitions the data source to a health state of Running.

Managing DBMS Network Failures

Manage the DBMS network failures by setting a desired amount of time for -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.