Using the Administration Console, JMX programs, or WebLogic Scripting Tool (WLST) scripts, you can manage the JDBC data sources in your domain. This section includes the following information:
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 the 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.
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.
A data source has a set of properties that define the initial and maximum number of connections in the pool (initialCapacity
and maxCapacity
), and the number of connections to add to the pool when all connections are in use (capacityIncrement
). When the pool reaches its maximum capacity, 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”) 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 initial capacity or the number of connections currently in use.
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”) 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.
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”) 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, WebLogic Server marks the data source as disabled, forcibly disconnects applications that are currently using a connection, and recreates (closes and reopens) connections that were in use when the data source was suspended. Any transaction on the connections that are closed are rolled back. WebLogic Server preserves all other connections exactly as they were before the data source was suspended.
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”) 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 JDBC operations exactly where they left off. If you forcibly suspended the data source, connections that were not in use when the data source was suspended are preserved exactly as they were before the suspension. Connections that were in use were closed and reopened. Clients that had reserved a connection no longer have a valid JDBC context.
Note: | You cannot resume a data source that did not start correctly, for example, if the database server is unavailable. |
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”) 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.
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”) 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.