This chapter provides information on how to use the Administration Console, command line, JMX programs, or WebLogic Scripting Tool (WLST) scripts to manage the JDBC data sources in your domain.
JDBCDataSourceRuntimeMBean.testPool
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 Administration Console (see "Test JDBC data sources") or the testPool() method in the JDBCDataSourceRuntimMBean. 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 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.
For each connection in a data source in your system, WebLogic Server creates a statement cache. 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 Administration Console or you can programmatically clear the statement cache for an individual connection.
JDBCDataSourceRuntimeMBean.clearStatementCache
You can manually clear the statement cache for all connections in a data source using the Administration Console (see "Clear the statement cache in a JDBC data source") or with the clearStatementCache() method on the JDBCDataSourceRuntimeMBean.
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.
JDBCDataSourceRuntimeMBean.shrink
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 Administration Console (see "Shrink the connection pool in a JDBC data source" in Oracle WebLogic Server Administration Console Help) or the shrink() method on the JDBCDataSourceRuntimeMBean. 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.
JDBCDataSourceRuntimeMBean.reset
To close and recreate all available database connections in a data source, you can use the Reset option on the JDBC Data Source: Control page in the Administration Console (see "Reset connections in a JDBC data source" in Oracle WebLogic Server Administration Console Help) or the reset() method on the JDBCDataSourceRuntimeMBean. 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.
JDBCDataSourceRuntimeMBean.suspend JDBCDataSourceRuntimeMBean.forceSuspend
To suspend a data source, you can use the Suspend and Force Suspend options on the JDBC Data Source: Control page in the Administration Console (see "Suspend JDBC data sources" in Oracle WebLogic Server Administration Console Help) or the suspend() and forceSuspend() methods in the JDBCDataSourceRuntimeMBean.
When you suspend a data source (not forcibly suspend), the data source is marked as disabled and applications cannot use 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 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.
JDBCDataSourceRuntimeMBean.resume
To re-enable a data source that you suspended, you can use the Resume option on the JDBC Data Source: Control page in the Administration Console (see "Resume suspended JDBC data sources" in Oracle WebLogic Server Administration Console Help) or the resume() method on the JDBCDataSourceRuntimeMBean. When you resume a data source, WebLogic Server marks the data source as enabled and allows applications to use 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.
JDBCDataSourceRuntimeMBean.shutdown JDBCDataSourceRuntimeMBean.forceShutdown
To shut down a data source, you can use the Shutdown and Force Shutdown options on the JDBC Data Source: Control page in the Administration Console (see "Shut down JDBC data sources" in Oracle WebLogic Server Administration Console Help) or the shutdown() and forceShutdown() methods in the JDBCDataSourceRuntimeMBean.
When you shut down a data source (not forcibly shut down), WebLogic Server closes database connections in the data source and shuts down the data source. If any connections from the data source are currently in use, the operation will fail.
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.
JDBCDataSourceRuntimeMBean.start
After you shut down a data source, you can use the Start option on the JDBC Data Source: Control page in the Administration Console (see "Start JDBC data sources" in Oracle WebLogic Server Administration Console Help) or the start() method in the JDBCDataSourceRuntimeMBean. Invoking 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
where 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 applications to hang for extended periods of time (for example, 10 minutes). If the assigned time period expires, the server instance purges and disables the pool (closes all connections and blocks further reserve attempts) and re-enables the pool as soon as it is possible to reconnect.
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.