Skip navigation.
Downloads | Product Documentation | Support

Configuring and Managing WebLogic JDBC

# Managing WebLogic JDBC Resources

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:

- Testing Data Sources and Database Connections
- Managing the Statement Cache for a Data Source
- Shrinking a Data Source
- Resetting a Data Source
- Suspending a Data Source
- Resuming a Data Source
- Shutting Down a Data Source
- Restarting a Data Source

# Testing Data Sources and Database Connections

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

# Managing the Statement Cache for a Data Source

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.

## Clearing the Statement Cache for a Data Source

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

## 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 Data Source

>     JDBCDataSourceRuntimeMBean.shrink

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.

# Resetting a Data Source

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

# Suspending a Data Source

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

# Resuming a Data Source

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

# Shutting Down a Data Source

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

# Restarting a Data Source

After you shut down a data source, the runtime MBean for the data source is deleted, so there is no way to restart the data source using a method on the runtime MBean. To restart a data source, you can untarget and retarget the data source on all servers on which you shut down the data source.

To restart a data source using the Administration Console:

1. In the Change Center of the Administration Console, click Lock & Edit.
2. In the Domain Structure tree, expand Services > JDBC and select Data Sources.
3. Select the name of the data source that you want to restart and select the Targets tab.
4. On the Target tab, clear the check box for each server or cluster on which you want to restart the data source.
5. Click Save.
6. In the Change Center of the Administration Console, click Activate Changes.
7. On the Target tab, select each server or cluster on which you want to restart the data source.
8. Click Save.
9. In the Change Center of the Administration Console, click Activate Changes.