Skip navigation.
Downloads | Product Documentation | Support

Programming WebLogic JDBC

# Configuring and Using WebLogic JDBC

You use the WebLogic Server Administration Console to enable, configure, and monitor features of WebLogic Server, including JDBC connection pools, data sources, and MultiPools. You can do the same tasks programmatically using the JMX API and the `weblogic.Admin` command line utility. After configuring JDBC connectivity components, you can use them in your applications.

The following sections describe how to program the JDBC connectivity components:

- Configuring and Using Connection Pools
- Configuring and Using DataSources
- Managing Connection Pools
- Configuring and Using Application-Scoped JDBC Connection Pools
- Configuring and Using MultiPools

For additional information, see

- Administration Console Online Help.
- WebLogic Server Javadocs for the following interfaces and packages:

- `weblogic.management.configuration.JDBCConnectionPoolMBean`
- `weblogic.management.configuration.JDBCDataSourceFactoryMBean`
- `weblogic.management.configuration.JDBCDataSourceMBean`
- `weblogic.management.configuration.JDBCMultiPoolMBean`
- `weblogic.management.configuration.JDBCTxDataSourceMBean`
- `weblogic.management.runtime.JDBCConnectionPoolRuntimeMBean`
- `weblogic.jdbc.extensions`

# Configuring and Using Connection Pools

A connection pool is a named group of identical JDBC connections to a database that are created when the connection pool is deployed, either at WebLogic Server startup or dynamically during run time. Your application "borrows" a connection from the pool, uses it, then returns it to the pool by closing it. Also see Overview of Connection Pools.

## Advantages to Using Connection Pools

Connection pools provide numerous performance and application design advantages:

- Using connection pools is far more efficient than creating a new connection for each client each time they need to access the database.
- You do not need to hard-code details such as the DBMS username and password in your application.
- You can limit the number of connections to your DBMS. This can be useful for managing licensing restrictions on the number of connections to your DBMS.
- You can change the DBMS you are using without changing your application code.

The attributes for a configuring a connection pool are defined in the Administration Console Online Help. There is also an API that you can use to programmatically create connection pools in a running WebLogic Server; see Creating a Connection Pool Dynamically. You can also use the command line; see the Web Logic Server Command-Line Interface Reference .

## Creating a Connection Pool at Startup

To create a startup (static) connection pool, you define attributes and permissions in the Administration Console. WebLogic Server opens JDBC connections to the database during the startup process and adds the connections to the pool.

To configure a connection pool in the Administration Console, in the navigation tree in the left pane, expand the Services and JDBC nodes, then select Connection Pool. The right pane displays a list of existing connection pools. Click the Configure a new JDBC Connection Pool text link to create a connection pool.

For step-by-step instructions and a description of connection pool attributes, see the Administration Console Online Help, available when you click the question mark in the upper-right corner of the Administration Console.

### Avoiding Server Lockup with the Correct Number of Connections

When your applications attempt to get a connection from a connection pool in which there are no available connections, the connection pool throws an exception stating that a connection is not available in the connection pool. To avoid this error, make sure your connection pool can expand to the size required to accommodate your peak load of connection requests.

To set the maximum number of connections for a connection pool in the Administration Console, expand the navigation tree in the left pane to show the Services—>JDBC—>Connection Pools nodes and select a connection pool. Then, in the right pane, select the Configuration—>Connections tab and specify a value for `Maximum Capacity`.

### Database Passwords in Connection Pool Configuration

When you create a connection pool, you typically include at least one password to connect to the database. If you use an open string to enable XA, you may use two passwords. You can enter the passwords as a name-value pair in the `Properties` field or you can enter them in their respective fields:

- `Password`. Use this field to set the database password. This value overrides any `password` value defined in the `Properties` passed to the tier-2 JDBC Driver when creating physical database connections. BEA recommends that you use the Password attribute in place of the password property in the properties string because the value is encrypted in the `config.xml` file (stored as the `Password` attribute in the `JDBCConnectionPool` tag) and is hidden on the administration console.
- `Open String Password`. Use this field to set the password in the open string that the transaction manager in WebLogic Server uses to open a database connection. This value overrides any password defined as part of the open string in the `Properties` field. The value is encrypted in the `config.xml` file (stored as the `XAPassword` attribute in the `JDBCConnectionPool` tag) and is hidden on the Administration Console. At runtime, WebLogic Server reconstructs the open string with the password you specify in this field. The open string in the Properties field should follow this format:

>     openString=Oracle_XA+Acc=P/userName/+SesTm=177+DB=demoPool+Threads=true=Sqlnet=dvi0+logDir=.

Note that after the `userName` there is no password.

If you specify a password in the `Properties` field when you first configure the connection pool, WebLogic Server removes the password from the `Properties` string and sets the value as the `Password` value in an encrypted form the next time you start WebLogic Server. If there is already a value for the `Password` attribute for the connection pool, WebLogic Server does not change any values. However, the value for the `Password` attribute overrides the password value in the `Properties` string. The same behavior applies to any password that you define as part of an open string. For example, if you include the following properties when you first configure a connection pool:

>     user=scott;
>     password=tiger;
>     openString=Oracle_XA+Acc=p/scott/tiger+SesTm=177+db=jtaXaPool+Threads=true+Sqlnet=lcs817+logDir=.+dbgFl=0x15;server=lcs817

The next time you start WebLogic Server, it moves the database password and the password included in the open string to the Password and Open String Password attributes, respectively, and the following value remains for the `Properties` field:

>     user=scott;
>     openString=Oracle_XA+Acc=p/scott/+SesTm=177+db=jtaXaPool+Threads=true+Sqlnet=lcs817+logDir=.+dbgFl=0x15;server=lcs817

After a value is established for the `Password` or `Open String Password` attributes, the values in these attributes override the respective values in the `Properties` attribute. That is, continuing with the previous example, if you specify `tiger2` as the database password in the `Properties` attribute, WebLogic Server ignores the value and continues to use `tiger` as the database password, which is the current encrypted value of the `Password` attribute. To change the database password, you must change the `Password` attribute.

Note: The value for `Password` and `Open String Password` do not need to be the same.

## SQL Statement Timeout Enhancements for Pooled JDBC Connections

In WebLogic Server 8.1SP3, the following attributes were added to JDBC connection pools to enable you to limit the amount of time that a statement can execute on a database connection from a JDBC connection pool:

- `StatementTimeout`—The time in seconds after which a statement executing on a pooled JDBC connection times out. When set to -1, (the default) statements do not timeout.
- `TestStatementTimeout`—The time in seconds after which a statement executing on a pooled JDBC connection for connection initialization or testing times out. When set to -1, (the default) statements do not timeout. See Initializing Connections with a SQL Query and Testing Connection Pools and Database Connections for more information about SQL statements used for initializing and testing connections.

These attributes rely on underlying JDBC driver support. WebLogic Server passes the time specified to the JDBC driver using the `java.sql.Statement.setQueryTimeout()` method. If your JDBC driver does not support this method, it may throw an exception and the timeout value is ignored.

Note: Using these features may cause a performance degradation. You should test these features in a staging or testing environment before using them in production.

Also, these attributes are not available in the Administration Console. You must manually edit the `config.xml` file to enable these features.

## JDBC Connection Pool Testing Enhancements

In WebLogic Server 8.1SP3, the following attributes were added to JDBC connection pools to improve the functionality of database connection testing for pooled connections:

- `CountOfTestFailuresTillFlush`—Closes all connections in the connection pool after the number of test failures that you specify to minimize the delay caused by further database testing. See Minimizing Connection Test Delay After Database Connectivity Loss.
- `CountOfRefreshFailuresTillDisable`—Disables the connection pool after the number of test failures that you specify to minimize the delay in handling the connection request after a database failure. See Minimizing Connection Request Delay After Connection Test Failures.
- SecondsToTrustAnIdlePoolConnection—Skips the connection test if the connection was used or tested successfully within the time specified. See Minimizing Connection Request Delay with Seconds to Trust an Idle Pool Connection.

### Minimizing Connection Test Delay After Database Connectivity Loss

When connectivity to the DBMS is lost, even if only momentarily, some or all of the JDBC connections in the connection pool typically become defunct. If the connection pool is configured to test connections on reserve (recommended), when an application requests a database connection, WebLogic Server tests the connection, discovers that the connection is dead, and tries to replace it with a new connection to satisfy the request. Ordinarily, when the DBMS comes back online, the refresh process succeeds. However, in some cases and for some modes of failure, testing a dead connection can impose a long delay. This delay occurs for each dead connection in the connection pool until all connections are replaced.

To minimize the delay that occurs during the test of dead database connections, you can set the `CountOfTestFailuresTillFlush` attribute on the connection pool. With this attribute set, WebLogic Server considers all connections in the connection pool as dead after the number of consecutive test failures that you specify, and closes all connections in the connection pool.

When an application requests a connection, the connection pool creates a connection without first having to test a dead connection. This behavior minimizes the delay for connection requests following the connection pool flush.

You specify the `CountOfTestFailuresTillFlush` attribute in the JDBCConnectionPool entry in the `config.xml` file. `TestConnectionsOnReserve` must also be set to `true`. For example:

>     <JDBCConnectionPool
>       CapacityIncrement="1"
>       DriverName="com.pointbase.xa.xaDataSource"
>       InitialCapacity="2" MaxCapacity="10"
>       Name="demoXAPool" Password="password"
>       Properties="user=examples;
>         DatabaseName=jdbc:pointbase:server://localhost/demo"
>       Targets="examplesServer"
>       TestConnectionsOnReserve="true"
>       CountOfTestFailuresTillFlush="1"
>       TestTableName="SYSTABLES"
>       URL="jdbc:pointbase:server://localhost/demo"
>     />

Note: The `CountOfTestFailuresTillFlush` attribute is not available in the Administration Console.

If you tend to see small network glitches or have a firewall that may occasionally kill only one socket or connection, you may want to set the number of test failures to 2 or 3, but a value of 1 will provide the best performance after database availability issues have been resolved.

### Minimizing Connection Request Delay After Connection Test Failures

If your DBMS becomes and remains unavailable, the connection pool will persistently test and try to replace dead connections while trying to satisfy connection requests. This behavior is beneficial because it enables the connection pool to react immediately when the database becomes available. However, testing a dead database connection can take as long as the network timeout, and can cause a long delay for clients.

To minimize the delay that occurs for client applications while a database is unavailable, you can set the `CountOfRefreshFailuresTillDisable` attribute on the connection pool. With this attribute set, WebLogic Server disables the connection pool after the number of consecutive failures to replace a dead connection. When an application requests a connection from a disabled connection pool, WebLogic Server throws a `ConnectDisabledException` immediately to notify the client that a connection is not available.

For connection pools that are disabled in this manner, WebLogic Server periodically run the refresh process. When the refresh process succeeds in creating a new database connection, WebLogic Server re-enables the connection pool. You can also manually re-enable the connection pool using the administration console or the `weblogic.Admin ENABLE_POOL` command.

You specify the `CountOfRefreshFailuresTillDisable` attribute in the JDBCConnectionPool entry in the `config.xml` file. `TestConnectionsOnReserve` must also be set to `true`. For example:

>     <JDBCConnectionPool
>       CapacityIncrement="1"
>       DriverName="com.pointbase.xa.xaDataSource"
>       InitialCapacity="2" MaxCapacity="10"
>       Name="demoXAPool" Password="password"
>       Properties="user=examples;
>         DatabaseName=jdbc:pointbase:server://localhost/demo"
>       Targets="examplesServer"
>       TestConnectionsOnReserve="true"
>       CountOfRefreshFailuresTillDisable="1"
>       TestTableName="SYSTABLES"
>       URL="jdbc:pointbase:server://localhost/demo"
>     />

Note: The `CountOfRefreshFailuresTillDisable` attribute is not available in the Administration Console.

If you tend to see small network glitches or have a firewall that may occasionally kill only one socket or connection, you may want to set the number of refresh failures to 2 or 3, but a value of 1 will usually provide the best performance.

### Minimizing Connection Request Delay with Seconds to Trust an Idle Pool Connection

Database connection testing during heavy traffic can reduce application performance. To minimize the impact of connection testing, you can set the SecondsToTrustAnIdlePoolConnection attribute in the JDBC connection pool configuration to trust recently-used or recently-tested database connections as viable and skip the connection test.

If your connection pool is configured to test connections on reserve (recommended), when an application requests a database connection, WebLogic Server tests the database connection before giving it to the application. If the request is made within the time specified for SecondsToTrustAnIdlePoolConnection since the connection was tested or successfully used and returned to the connection pool, WebLogic Server skips the connection test before delivering it to the application.

If your connection pool is configured to periodically test available connections in the connection pool (TestFrequencySeconds is specified), WebLogic Server also skips the connection test if the connection was successfully used and returned to the connection pool within the time specified for SecondsToTrustAnIdlePoolConnection.

To set SecondsToTrustAnIdlePoolConnection, you can specify the value on the JDBC Connection Pool —> Configuration —> Connections tab in the Administration Console. See "JDBC Connection Pool --> Configuration --> Connections" in the Administration Console Online Help. You can also set it directly in the `config.xml` file. For example:

>     <JDBCConnectionPool
>       CapacityIncrement="1"
>       DriverName="com.pointbase.xa.xaDataSource"
>       InitialCapacity="2" MaxCapacity="10"
>       Name="demoXAPool" Password="password"
>       Properties="user=examples;
>         DatabaseName=jdbc:pointbase:server://localhost/demo"
>       Targets="examplesServer"
>       SecondsToTrustAnIdlePoolConnection="15"
>       TestConnectionsOnreserve="true"
>       TestTableName="SYSTABLES"
>       URL="jdbc:pointbase:server://localhost/demo"
>     />

SecondsToTrustAnIdlePoolConnection is a tuning feature that can improve application performance by minimizing the delay caused by database connection testing, especially during heavy traffic. However, it can reduce the effectiveness of connection testing, especially if the value is set too high. The appropriate value depends on your environment and the likelihood that a connection will become defunct.

## Creating a Connection Pool Dynamically

The `JDBCConnectionPool` administration MBean as part of the WebLogic Server management architecture (JMX). You can use the JMX API to create and configure a connection pool dynamically from within a Java application. That is, from your client or server application code, you can create a connection pool in a WebLogic Server instance that is already running.

You can also use the `CREATE_POOL` command in the WebLogic Server command line interface to dynamically create a connection pool. See CREATE_POOL.

To dynamically create a connection pool using the JMX API, follow these main steps:

1. Import required packages.
2. Look up the administration MBeanHome in the JNDI tree.
3. Get the server MBean.
4. Create the connection pool MBean.
5. Set the properties for the connection pool.
6. Add the target.
7. Create a DataSource object.

Note: Dynamically created connection pools must use dynamically created DataSource objects. For a DataSource to exist, it must be associated with a connection pool. Also, a one-to-one relationship exists between DataSource objects and connection pools in WebLogic Server. Therefore, you must create a DataSource to use with a connection pool.

When you create a connection pool using the JMX API, the connection pool is added to the server configuration and will be available even if you stop and restart the server. If you do not want the connection pool to be persistent, you must remove it programmatically.

For more information about using MBeans to manage WebLogic Server, see Programming WebLogic Management Services with JMX. For more information about the `JDBCConnectionPool` MBean, see the Javadoc at.

## Dynamic Connection Pool Sample Code

The following sections show code samples for performing the main steps to create a connection pool dynamically.

### Import Packages

>     import java.sql.*;
>     import java.util.*;
>     import javax.naming.Context;
>     import javax.sql.DataSource;
>     import weblogic.jndi.Environment;
>     import weblogic.management.configuration.JDBCConnectionPoolMBean;
>     import weblogic.management.runtime.JDBCConnectionPoolRuntimeMBean;
>     import weblogic.management.configuration.JDBCTxDataSourceMBean;
>     import weblogic.management.configuration.ServerMBean;
>     import weblogic.management.MBeanHome;
>     import weblogic.management.WebLogicObjectName;

>     String cpName = null;
>     String cpJNDIName = null;

### Look Up the Administration MBeanHome

>     mbeanHome = (MBeanHome)ctx.lookup(MBeanHome.ADMIN_JNDI_NAME);

### Get the Server MBean

>     svrAdminMBean = (ServerMBean)adminMBeanHome.getAdminMBean("myserver",
>      "Server");

### Create the Connection Pool MBean

>       // Create ConnectionPool MBean
>       cpMBean = (JDBCConnectionPoolMBean)mbeanHome.createAdminMBean(
>         cpName, "JDBCConnectionPool",
>         mbeanHome.getDomainName());

### Set the Connection Pool Properties

>       Properties pros = new Properties();
>       pros.put("user", "scott");
>         pros.put("server", "dbserver1t1");

>       // Set DataSource attributes
>       cpMBean.setURL("jdbc:weblogic:oracle");
>       cpMBean.setDriverName("weblogic.jdbc.oci.xa.XADataSource");
>       cpMBean.setProperties(pros);
>       cpMBean.setPassword("tiger");

Note: In this example, the database password is set using the `setPassword(String)` method instead of including it with the user and server names in `Properties`. When you use the `setPassword(String)` method, WebLogic Server encrypts the password in the `config.xml` file and when displayed on the administration console. BEA recommends that you use this method to avoid storing database passwords in clear text in the `config.xml` file.

### Add the Target

When you add a deployment target, the connection pool is deployed and database connections in the connection pool are created.

>       cpMBean.addTarget(serverMBean);

### Create a DataSource

>     public void createDataSource() throws SQLException {
>       try {
>         // Get context
>         Environment env = new Environment();
>         env.setProviderUrl(url);
>         env.setSecurityPrincipal(userName);
>         env.setSecurityCredentials(password);
>         ctx = env.getInitialContext();

>           // Create TxDataSource  MBean
>           dsMBean = (JDBCTxDataSourceMBean)mbeanHome.createAdminMBean(
>             cpName, "JDBCTxDataSource",
>             mbeanHome.getDomainName());

>           // Set TxDataSource attributes
>           dsMBean.setJNDIName(cpJNDIName);
>           dsMBean.setPoolName(cpName);

>           // Startup datasource
>           dsMBean.addTarget(serverMBean);

>         } catch (Exception ex) {
>           ex.printStackTrace();
>           throw new SQLException(ex.toString());

>         }

>       }

Note: The JDBCDataSourceMBean is deprecated in WebLogic server 8.1. Use the `JDBCTxDataSourceMBean` instead. The attributes that are not available in the `JDBCTxDataSourceMBean` (`WaitForConnectionEnabled` and `ConnectionWaitPeriod`) have been deprecated and are replaced with the `ConnectionReserveTimeoutSeconds` attribute in the `JDBCConnectionPoolMBean`. See Enabling Connection Requests to Wait for a Connection.

### Removing a Dynamic Connection Pool and DataSource

The following code sample shows how to remove a dynamically created connection pool. If you do not remove dynamically created connection pools, they will remain available even after the server is stopped and restarted.

>     public void deleteConnectionPool() throws SQLException {
>       try {
>         // Remove dynamically created connection pool from the server
>         cpMBean.removeTarget(serverMBean);
>         // Remove dynamically created connection pool from the configuration
>         mbeanHome.deleteMBean(cpMBean);
>       } catch (Exception ex) {
>         throw new SQLException(ex.toString());
>       }
>     }

>     public void deleteDataSource() throws SQLException {

>         try {

>           // Remove dynamically created TxDataSource from the server

>           dsMBean.removeTarget(serverMBean);

>           // Remove dynamically created TxDataSource from the configuration

>           mbeanHome.deleteMBean(dsMBean);

>         } catch (Exception ex) {

>           throw new SQLException(ex.toString());

>         }

>       }

# Configuring and Using DataSources

As with Connection Pools and MultiPools, you can create DataSource objects in the Administration Console or using the WebLogic Management API. DataSource objects can be defined with or without transaction services. You configure connection pools and MultiPools before you define the pool name attribute for a DataSource.

DataSource objects, along with the JNDI, provide access to connection pools for database connectivity. Each DataSource can refer to one connection pool or MultiPool. However, you can define multiple DataSources that use a single connection pool. This allows you to define both transaction and non-transaction-enabled DataSource objects that share the same database.

WebLogic Server supports two types of DataSource objects:

- DataSources (for local transactions only)
- TxDataSources (for distributed transactions)

Note: In the Administration Console, Data Sources and Tx Data Sources are distinguished by the Honor Global Transactions setting that you select when you create the datasource:

`true` for Tx Data Sources

`false` for Data Sources (non-Tx)

Tx Data Sources are created by default when you create the data source in the Administration Console.

If your application meets any of the following criteria, you should use a TxDataSource in WebLogic Server:

- Uses the Java Transaction API (JTA)
- Uses the WebLogic Server EJB container to manage transactions
- Includes updates to multiple databases during a single transaction.

The only time you should use a non-Tx Data Source is when you want to do some work on the database that you do not want to include in the current transaction.

If you want applications to use a DataSource (Tx or non-Tx) to get a database connection from a connection pool (the preferred method), you should define the DataSource in the Administration Console before running your application. For more information about how to configure a DataSource and when to use a TxDataSource, see JDBC DataSources.

Note: The JDBCDataSourceMBean is deprecated in WebLogic server 8.1. Use the `JDBCTxDataSourceMBean` instead. The attributes that are not available in the `JDBCTxDataSourceMBean` (`WaitForConnectionEnabled` and `ConnectionWaitPeriod`) have been deprecated and are replaced with the `ConnectionReserveTimeoutSeconds` attribute in the `JDBCConnectionPoolMBean`. See Enabling Connection Requests to Wait for a Connection.

## Importing Packages to Access DataSource Objects

To use the DataSource objects in your applications, import the following classes in your client code:

>     import java.sql.*;
>     import java.util.*;
>     import javax.naming.*;

## Obtaining a Client Connection Using a DataSource

To obtain a connection for a JDBC client, use a Java Naming and Directory Interface (JDNI) lookup to locate the DataSource object, as shown in the following code fragment.

Note: When using a JDBC connection in a client-side application, the exact same JDBC driver classes must be in the `CLASSPATH` on both the server and the client. If the driver classes do not match, you may see `java.rmi.UnmarshalException` exceptions.

>     Context ctx = null;
>       Hashtable ht = new Hashtable();
>       ht.put(Context.INITIAL_CONTEXT_FACTORY,
>              "weblogic.jndi.WLInitialContextFactory");
>       ht.put(Context.PROVIDER_URL,
>              "t3://hostname:port");

>       Connection conn = null;
>       Statement stmt = null;
>       ResultSet rs = null;

>       try {
>         ctx = new InitialContext(ht);
>         javax.sql.DataSource ds
>           = (javax.sql.DataSource) ctx.lookup ("myDataSource");
>         conn = ds.getConnection();

>        // You can now use the conn object to create
>        //  Statements and retrieve result sets:

>         stmt = conn.createStatement();
>         stmt.execute("select * from someTable");
>         rs = stmt.getResultSet();

>     ...

>     //Close JDBC objects as soon as possible
>         stmt.close();
>         stmt=null;

>         conn.close();
>         conn=null;

>      }
>       catch (Exception e) {
>         // a failure occurred
>         log message;
>       }
>     finally {
>       try {
>         ctx.close();
>       } catch (Exception e) {
>          log message; }
>       try {
>         if (rs != null) rs.close();
>       } catch (Exception e) {
>          log message; }
>       try {
>         if (stmt != null) stmt.close();
>       } catch (Exception e) {
>          log message; }
>       try {
>         if (conn != null) conn.close();
>       } catch (Exception e) {
>          log message; }
>     }

>     (Substitute the correct hostname and port number for your WebLogic Server.)

Note: The code above uses one of several available procedures for obtaining a JNDI context. For more information on JNDI, see Programming WebLogic JNDI`.`

## Possible Exceptions When a Connection Request Fails

The weblogic.jdbc.extensions package includes the following exceptions that can be thrown when an application request fails. Each exception extends `java.sql.SQLException`.

- `ConnectionDeadSQLException`—generated when an application request to get a connection fails because the connection test on the reserved connection failed. This typically happens when the database server is unavailable. See Testing Connection Pools and Database Connections.
- `ConnectionUnavailableSQLException`—generated when an application request to get a connection fails because there are currently no connections available in the pool to be allocated. This is a transient failure, and is generated if all connections in the pool are currently in use. It can also be thrown when connections are unavailable because they are being tested. See Testing Connection Pools and Database Connections.
- `PoolDisabledSQLException`—generated when an application request to get a connection fails because the JDBC Connection Pool has been administratively disabled. See Suspending a Connection Pool.
- `PoolLimitSQLException`—generated when an application request to get a connection fails due to a configured threshold of the connection pool, such as `HighestNumWaiters`, `ConnectionReserveTimeoutSeconds`, and so forth. See Enabling Connection Requests to Wait for a Connection.
- `PoolPermissionsSQLException`—generated when an application request to get a connection fails a (security) authentication or authorization check.

## Connection Pool Limitation

When using connection pools, it is possible to execute DBMS-specific SQL code that will alter the database connection properties and that WebLogic Server and the JDBC driver will be unaware of. When the connection is returned to the connection pool, the characteristics of the connection may not be set back to a valid state. For example, with a Sybase DBMS, if you use a statement such as `"set rowcount 3 select * from y"`, the connection will only ever return a maximum of 3 rows from any subsequent query on this connection. When the connection is returned to the connection pool and then reused, the next user of the connection will still only get 3 rows returned, even if the table being selected from has 500 rows.

In most cases, there is standard JDBC code that can accomplish the same result. In this example, you could use `setMaxRows()` instead of `set rowcount`. BEA recommends that you use the standard JDBC code instead of the DBMS-specific SQL code. When you use standard JDBC calls to alter the connection, Weblogic Server returns the connection to a standard state when the connection is returned to the connection pool.

If you use DBMS-specific SQL code that alters the connection, you must set the connection back to an acceptable state before returning the connection to the connection pool.

# Managing Connection Pools

The `JDBCConnectionPool` and `JDBCConnectionPoolRuntime` MBeans provide methods to manage connection pools and obtain information about them. All of these management options are available in the Administration Console. However, you can also use the methods provided to manage connection pools using the JMX API. Methods are provided for these and other operations:

- Getting Status and Statistics for a Connection Pool
- Enabling Connection Creation Retries
- Initializing Connections with a SQL Query
- Testing Connection Pools and Database Connections
- Enabling Connection Requests to Wait for a Connection
- Configuring and Managing the Statement Cache for a Connection Pool
- Shrinking a Connection Pool
- Resetting a Connection Pool
- Suspending a Connection Pool
- Resuming a Connection Pool

To see all of the methods available and for more information about the methods described in this section, see the Javadocs for the following MBeans:

- JDBCConnectionPoolMBean
- JDBCConnectionPoolRuntimeMBean

## Getting Status and Statistics for a Connection Pool

>     JDBCConnectionPoolRuntimeMBean.getState()

>     JDBCConnectionPoolRuntimeMBean.getActiveConnectionsAverageCount()
>     JDBCConnectionPoolRuntimeMBean.getActiveConnectionsCurrentCount()
>     JDBCConnectionPoolRuntimeMBean.getActiveConnectionsHighCount()
>     JDBCConnectionPoolRuntimeMBean.getConnectionLeakProfileCount()
>     JDBCConnectionPoolRuntimeMBean.getConnectionsTotalCount()
>     JDBCConnectionPoolRuntimeMBean.getCurrCapacity()
>     JDBCConnectionPoolRuntimeMBean.getFailuresToReconnectCount()
>     JDBCConnectionPoolRuntimeMBean.getHighestNumAvailable()
>     JDBCConnectionPoolRuntimeMBean.getHighestNumUnavailable()
>     JDBCConnectionPoolRuntimeMBean.getLeakedConnectionCount()
>     JDBCConnectionPoolRuntimeMBean.getMaxCapacity()
>     JDBCConnectionPoolRuntimeMBean.getNumAvailable()
>     JDBCConnectionPoolRuntimeMBean.getNumUnavailable()
>     JDBCConnectionPoolRuntimeMBean.getStatementProfileCount()
>     JDBCConnectionPoolRuntimeMBean.getVersionJDBCDriver()
>     JDBCConnectionPoolRuntimeMBean.getWaitingForConnectionCurrentCount()
>     JDBCConnectionPoolRuntimeMBean.getWaitingForConnectionHighCount()
>     JDBCConnectionPoolRuntimeMBean.getWaitSecondsHighCount()

The `JDBCConnectionPoolRuntimeMBean` provides methods for getting the current state of the connection pool and for getting statistics about the connection pool, such as the average number of active connections, the current number of active connections, the highest number of active connections, and so forth.

The `getState()` method returns the current state of the connection pool. The current state can be:

- `Running` if the pool is enabled (deployed and not suspended). This is the normal state of the connection pool.
- `Suspended` if the pool is disabled.
- `Shutdown` if the pool is shutdown and all database connections have been closed.
- `Unknown` if the pool state is unknown.
- `Unhealthy` if all connections are unavailable (not because they are in use). This state occurs if the database server is unavailable when the connection pool is created (creation retry must be enabled) or if all connections have failed connection tests (on creation, on reserve, on release, or periodic testing).

For more information about methods for getting connection pool statistics, see the Javadoc for the JDBCConnectionPoolRuntimeMBean. Also see Testing Connection Pools and Database Connections.

## Enabling Connection Creation Retries

>     JDBCConnectionPoolMBean.setConnectionCreationRetryFrequencySeconds(int seconds)

The `setConnectionCreationRetryFrequencySeconds()` method sets the number of seconds between attempts to create database connections when the connection pool is created. If you do not set this value, connection pool creation fails if the database is unavailable. If set and if the database is unavailable when the connection pool is created, WebLogic Server will attempt to create connections in the pool again after the number of seconds you specify, and will continue to attempt to create the connections until it succeeds.

By default, this attribute is set to `0`, which disables connection creation retries.

Note: Do not use connection creation retries with connection pools in a High Availability MultiPool. Connection requests to the MultiPool will fail (not fail-over) when a connection pool in the list is dead and the number of connection requests equals the number of connections in the first connection pool, even if connections are available in subsequent connection pools in the MultiPool.

## Initializing Connections with a SQL Query

>     JDBCConnectionPoolMBean.setInitSQL(java.lang.String string)

With the `setInitSQL()` method, you set a value for the `initSQL` MBean attribute. WebLogic Server runs this SQL code whenever it creates a database connection for the connection pool, which includes at server startup, when expanding the connection pool, when deploying the connection pool on a server, and when refreshing a connection. In essence, WebLogic Server "primes" the connection with this SQL code before applications can use the connection. You can use this feature to set DBMS-specific operational settings that are connection-specific or to ensure that a connection has memory or permissions to perform required actions.

Start the code with `SQL` followed by a space. For example:

>     SQL alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

or

>     SQL SET LOCK MODE TO WAIT

Options that you can set using `InitSQL` vary by DBMS.

For information about setting this attribute with the Administration Console, see "Initializing Database Connections with SQL Code" and "Init SQL" in the Administration Console Online Help.

Note: `InitSQL` is not a dynamic attribute. When you change the value for `InitSQL`, you must either undeploy and redeploy the connection pool or restart the server.

## Testing Connection Pools and Database Connections

>     JDBCConnectionPoolRuntimeMBean.testPool()
>     JDBCConnectionPoolMBean.setTestConnectionsOnCreate(boolean enable)
>     JDBCConnectionPoolMBean.setTestConnectionsOnRelease(boolean enable)
>     JDBCConnectionPoolMBean.setTestConnectionsOnReserve(boolean enable)
>     JDBCConnectionPoolMBean.setTestFrequencySeconds(int seconds)
>     JDBCConnectionPoolMBean.setTestTableName(java.lang.String table)
>     JDBCConnectionPoolMBean.setHighestNumUnavailable(int count)

To make sure that the database connections in a connection pool 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 `JDBCConnectionPoolMBean` (the configuration MBean) and manual testing that you can do to trouble-shoot a connection pool with the `testPool()` method on the `JDBCConnectionPoolRuntimeMBean` (the runtime MBean).

Allowing WebLogic Server to automatically maintain the integrity of pool connections should prevent most DBMS connection problems. You use the following methods on the `JDBCConnectionPoolMBean` to configure automatic connection testing:

- `setTestFrequencySeconds(int seconds)`—Use this method to enable periodic connection testing and to specify the number of seconds between tests of unused connections. The server tests unused connections and reopens any faulty connections. If you do not set `TestFrequencySeconds`, periodic connection testing is not enabled. You must also set the `HighestNumUnavailable` and `TestTableName`.
- `setTestConnectionsOnCreate(boolean enable)`—Use this method to enable testing on each database connection after it is created. This applies to connections created at server startup, when the connection pool is expanded, and when a connection is recreated after failing a test. You must also set a `TestTableName`.
- `setTestConnectionsOnReserve(boolean enable)`—Use this method to enable testing on each connection before it is given to a client. This may add a slight delay to the connection request, but it guarantees that the connection is healthy. You must also set a `TestTableName`.
- `setTestConnectionsOnRelease(boolean enable)`—Use this method to enable testing on database connections when they are returned to the connection pool. You must also set a `TestTableName`.
- `setHighestNumUnavailable(int count)`—Use this method to limit the number of idle connections that the server will test. For example, if you have 10 connections in your connection pool and 5 are in use, if the server were to begin testing all 5 connections that are not in use, there would be no connections available to fill a connection request from an application. If you set the `HighestNumUnavailable` attribute to `3`, the connection pool maintenance thread would take 3 connections from the connection pool for testing, and there would still be 2 connections available to fill a connection request.
- `setTestTableName(java.lang.String table)`—Use this method to specify a table name to use for connection testing. You can also specify SQL code to run in place of the standard test by entering `SQL` followed by a space and the SQL code you want to run as a test. `TestTableName` is required to enable any automatic database connection testing.

For information about setting these attributes in the Administration Console, see "Connection Testing Options" and "JDBC Connection Pool --> Configuration --> Connections" in the Administration Console Online Help.

## Enabling Connection Requests to Wait for a Connection

The `JDBCConnectionPoolMBean` has two attributes that you can set to enable connection requests to wait for a connection from a connection pool: `ConnectionReserveTimeoutSeconds` and `HighestNumWaiters`. You use these two attributes together to enable connection requests to wait for a connection without disabling your system by blocking too many threads.

### Connection Reserve Timeout

>     JDBCConnectionPoolMBean.setConnectionReserveTimeoutSeconds(int seconds)

When an application requests a connection from a connection pool, if all connections in the connection pool are in use and if the connection pool has expanded to its maximum capacity, the application will get a Connection Unavailable SQL Exception. To avoid this, you can configure a Connection Reserve Timeout value (in seconds) so that connection requests will wait for a connection to become available. After the Connection Reserve Timeout has expired, if no connection becomes available, the request will fail and the application will get a `PoolLimitSQLException` exception.

- If you set Connection Reserve Timeout to `0`, a connection request will wait indefinitely. Setting Connection Reserve Timeout to -1 will cause the connection to timeout immediately.
- The default value is 10 seconds.
- If you use global transactions, the Connection Reserve Timeout value is ignored. In this situation, a connection request automatically waits for a connection if one is not available. The amount of time available for an application to wait for a connection (if necessary), connect, and complete the transaction is determined by the transaction time out value.

### Limiting the Number of Waiting Connection Requests

>     JDBCConnectionPoolMBean.setHighestNumWaiters(int count)

Connection requests that wait for a connection block a thread. If too many connection requests concurrently wait for a connection and block threads, your system performance can degrade. To avoid this, you can set the `HighestNumWaiters` attribute, which limits the number connection requests that can concurrently wait for a connection.

If you set `HighestNumWaiters` to `MAX-INT` (the default), there is effectively no bound on how many connection requests can wait for a connection. If you set `HighestNumWaiters` to `0`, connection requests cannot wait for a connection.

## Configuring and Managing the Statement Cache for a Connection Pool

For each connection in a connection pool 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. Statement caching is controlled by the `StatementCacheSize` and the `StatementCacheType`. For more information about how the statement cache works and configuration options, see "Increasing Performance with the Statement Cache" in the WebLogic Server Administration Console Online Help.

Each connection in the connection pool has its own statement cache, but configuration settings are made for all connections in the connection pool.

### Configuring the Statement Cache

>     JDBCConnectionPoolMBean.setStatementCacheSize(int cacheSize)
>     JDBCConnectionPoolMBean.setStatementCacheType(java.lang.String type)

WebLogic Server provides methods to set the size (`StatementCacheSize`) and algorithm (`StatementCacheType`) of the statement cache for each connection pool.

When you set the `StatementCacheSize`, that number of statements (prepared and callable) are cached for each connection in the connection pool.

By default, the `StatementCacheType` is set to `LRU` for Least Recently Used. With this algorithm, the connection pool replaces the least recently used statement in the cache when a new prepared or callable statement is used. In most cases, this option provides the best performance. You can also set the `StatementCacheType` to `Fixed`. With the fixed algorithm, prepared and callable statements are cached until the `StatementCacheSize` value is met. Statements remain in the cache until the cache is cleared manually or the connection is closed.

Note: `StatementCacheType` is not a dynamic attribute. When you change the value for `StatementCacheType`, you must either undeploy and redeploy the connection pool or restart the server.

### Deprecated Statement Cache Configuration Options

In releases before WebLogic Server 8.1, there were separate statement cache implementations for XA and non-XA JDBC connection pools (connection pools that use an XA JDBC driver and connection pools that use a non-XA JDBC driver to create database connections). In WebLogic Server 8.1, the statement cache was rewritten. There is now one statement cache implementation for both XA and non-XA connection pools. With the statement cache revision, there are connection pool attributes in the `JDBCConnectionPoolMBean` for configuring the statement cache that are now deprecated. Table 2-1 lists the deprecated MBean attributes from previous releases and the equivalent option in WebLogic Server 8.1.

Table 2-1 Deprecated Statement Cache Attributes and Equivalents

Deprecated MBean Attribute | Equivalent in WebLogic Server 8.1
--- | ---
`PreparedStatementCacheSize` | `StatementCacheSize`
`XAPreparedStatementCacheSize` | `StatementCacheSize`

To enable migration of a WebLogic Server configuration from an earlier release to version 8.1, Weblogic Server enforces the following order of precedence for these MBean attributes:

1. `PreparedStatementCacheSize`
2. `XAPreparedStatementCacheSize`
3. `StatementCacheSize`

For example, if the `PreparedStatementCacheSize` for a JDBC connection pool is set to `5` and the `StatementCacheSize` is set to `10`, the actual statement cache size for each connection in the connection pool will be 5 because `PreparedStatementCacheSize` takes precedence over `StatementCacheSize`.

### Clearing the Statement Cache for a Connection Pool

>     JDBCConnectionPoolRuntimeMBean.clearStatementCache()

You can manually clear the statement cache for all connections in a connection pool with the `clearStatementCache()` method.

### 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 resSetType,int resSetConcurrency)
>     weblogic.jdbc.extensions.WLConnection.clearPreparedStatement(java.lang.
>     String sql)
>     weblogic.jdbc.extensions.WLConnection.clearPreparedStatement(java.lang.
>     String sql,int resSetType,int resSetConcurrency)

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

>     JDBCConnectionPoolRuntimeMBean.shrink()

A connection pool 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 added 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 connection pool or manually shrink the connection pool with the `shrink()` method.

You may want to drop some connections from the connection pool when a peak usage period has ended, freeing up WebLogic Server and DBMS resources.

## Resetting a Connection Pool

>     JDBCConnectionPoolRuntimeMBean.reset()

The `JDBCConnectionPoolRuntimeMBean.reset()` method closes and reopens all connections in a connection pool. This may be necessary after the DBMS has been restarted, for example. Often when one connection in a connection pool has failed, all of the connections in the pool are bad.

## Suspending a Connection Pool

>     JDBCConnectionPoolRuntimeMBean.suspend()

>     JDBCConnectionPoolRuntimeMBean.forceSuspend()

WebLogic server includes two methods in the `JDBCConnectionPoolRuntimeMbean` to suspend a connection pool: `suspend()` and `forceSuspend()`. You can use these methods to temporarily disable a connection pool, preventing any clients from obtaining or using a connection from the pool. Only users with the proper permissions can suspend a connection pool.

When you suspend a connection pool with the `suspend()` method, the connection pool is marked as disabled and applications cannot use connections from the pool. Applications that already have a reserved connection from the connection pool when it is suspended will get an exception when trying to use the connection. WebLogic Server preserves all connections in the connection pool exactly as they were before the connection pool was suspended.

When you suspend a connection pool with the `forceSuspend()` method, WebLogic Server marks the connection pool as disabled, forcibly disconnects applications that are currently using a connection, and recreates (closes and reopens) connections that were in use when the connection pool 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 connection pool was suspended.

The `suspend()` and `forceSuspend()` methods replace the `disableFreezingUsers()` and `disableDroppingUsers()` methods, which are deprecated.

## Resuming a Connection Pool

>     JDBCConnectionPoolRuntimeMBean.resume()

To re-enable a connection pool that you disabled with the `suspend()` or `forceSuspend()` method, you can use the `resume()` method, which marks the connection pool as enabled and allows applications to use connections from the connection pool. If you suspended the connection pool with the `suspend()` method, all connections are preserved exactly as they were before the connection pool was suspended. Clients that had reserved a connection before the connection pool was suspended can continue JDBC operations exactly where they left off. If you suspended the connection pool with the `forceSuspend()` method, connections that were not in use when the connection pool 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.

The `resume()` method replaces the `enable()` method, which is deprecated.

Note: You cannot use the `resume()` method to start a connection pool that did not start correctly, for example, if the database server is unavailable.

# Configuring and Using Application-Scoped JDBC Connection Pools

When you package your enterprise applications, you can include the `weblogic-application.xml` supplemental deployment descriptor, which you use to configure application scoping. Within the `weblogic-application.xml` file, you can configure JDBC connection pools that are created when you deploy the enterprise application.

An instance of the connection pool is created with each instance of your application. This means an instance of the pool is created with the application on each node that the application is targeted to. It is important to keep this in mind when considering pool sizing.

Connection pools created in this manner are known as application-scoped connection pools, app scoped pools, application local pools, app local pools, or local pools, and are scoped for the enterprise application only. That is, they are isolated for use by the enterprise application.

For more information about application scoping and application scoped resources, see:

- XML Application Scoping in Programming WebLogic XML.
- Two-Phase Deployment Protocol in Deploying WebLogic Server Applications.

## Configuring Application-Scoped Connection Pools

To configure an application-scoped connection pool, you add a jdbc-connection-pool element with connection pool configuration parameters to the `weblogic-application.xml` file for your enterprise application. For example:

>     <jdbc-connection-pool>
>           <data-source-name>XA_LocalDS1</data-source-name>
>           <connection-factory>
>             <factory-name>XA_LocalCF1</factory-name>
>             <connection-properties>
>               <user-name>SCOTT</user-name>
>               <password>tiger</password>
>               <url>jdbc:oracle:thin:@dbserver:1521:sid</url>
>               <driver-class-name>oracle.jdbc.xa.client.OracleXADataSource
>                 </driver-class-name>
>               <connection-params>
>                 <parameter>
>                   <param-name>foo</param-name>
>                   <param-value>xyz</param-value>
>                 </parameter>
>                 <parameter>
>                   <param-name>bar</param-name>
>                   <param-value>abc</param-value>
>                 </parameter>
>               </connection-params>
>             </connection-properties>
>           </connection-factory>

>           <pool-params>
>             <size-params>
>               <initial-capacity>5</initial-capacity>
>               <max-capacity>10</max-capacity>
>               <capacity-increment>2</capacity-increment>
>               <shrinking-enabled>true</shrinking-enabled>
>               <shrink-frequency-seconds>300</shrink-frequency-seconds>
>               <highest-num-waiters>100</highest-num-waiters>
>               <highest-num-unavailable>4</highest-num-unavailable>
>             </size-params>

>             <xa-params>
>               <debug-level>3</debug-level>
>               <local-transaction-supported>true</local-transaction-supported>
>               <xa-set-transaction-timeout>true</xa-set-transaction-timeout>
>               <xa-transaction-timeout>30</xa-transaction-timeout>
>             </xa-params>

>             <login-delay-seconds>1</login-delay-seconds>
>             <leak-profiling-enabled>false</leak-profiling-enabled>
>             <connection-check-params>
>               <table-name>check_table</table-name>
>               <check-on-create-enabled>true</check-on-create-enabled>
>               <check-on-reserve-enabled>true</check-on-reserve-enabled>
>               <check-on-release-enabled>false</check-on-release-enabled>
>               <connection-reserve-timeout-seconds>30
>                 </connection-reserve-timeout-seconds>
>               <test-frequency-seconds>600</test-frequency-seconds>
>               <connection-creation-retry-frequency-seconds>360
>                 </connection-creation-retry-frequency-seconds>
>               <inactive-connection-timeout-seconds>360
>                 </inactive-connection-timeout-seconds>
>               <init-sql>SQL SET LOCK MODE TO WAIT</init-sql>
>             </connection-check-params>

>           </pool-params>

>           <driver-params>
>             <prepared-statement>
>               <cache-size>10</cache-size>
>               <cache-type>LRU</cache-type>
>             </prepared-statement>

>             <row-prefetch-enabled>true</row-prefetch-enabled>
>             <row-prefetch-size>500</row-prefetch-size>
>             <stream-chunk-size>1024</stream-chunk-size>
>           </driver-params>

>         </jdbc-connection-pool>

For more details about JDBC connection pool element entries, see weblogic-application.xml Deployment Descriptor Elements in Developing WebLogic Server Applications.

If you deploy your enterprise application as an exploded archive, you can also change configuration options using the Administration Console. See "Application-Scoped JDBC Data Sources and Connection Pools" in the Administration Console Online Help.

### Required Elements Within the jdbc-connection-pool Element

When configuring and application-scoped connection pool within the `weblogic-application.xml` file, you must include the following sub-elements:

- `data-source-name`, which defines a name for the application-scoped data source created (always a TxDataSource) with the application-scoped connection pool when you deploy your application. The application uses this name to look up the data source on the local JNDI tree to get a connection from the connection pool.

>     <data-source-name>XA_LocalDS1</data-source-name>

See Getting a Connection from an Application-Scoped Connection Pool for more information.

- `connection-factory`, which is a reference to the data source factory in your WebLogic domain to use to create the application-scoped data source and connection pool when you deploy your application. The data source factory also supplies some default values for connections in the application-scoped connection pool. You can over-ride these values. For example:

>     <connection-factory>
>       <factory-name>XA_LocalCF1</factory-name>
>       <connection-properties>
>         <user-name>SCOTT</user-name>
>         <password>tiger</password>
>         <url>jdbc:oracle:thin:@dbserver:1521:sid</url>
>         <driver-class-name>oracle.jdbc.xa.client.OracleXADataSource
>           </driver-class-name>
>         <connection-params>
>           <parameter>
>             <param-name>foo</param-name>
>             <param-value>xyz</param-value>
>           </parameter>
>           <parameter>
>             <param-name>bar</param-name>
>             <param-value>abc</param-value>
>           </parameter>
>         </connection-params>
>       </connection-properties>
>     </connection-factory>

If you do not specify a data source factory name, you must provide all parameters necessary to create the connection pool, including the user name, password, URL, driver class name, and connection parameters in the `connection-properties` tag.

For more information about configuring a data source factory in your WebLogic domain, see "JDBC Data Source Factories" in the Administration Console Online Help.

### Encrypting the Database Password in weblogic-application.xml

To avoid storing or transmitting database passwords in clear text, you can encrypt database passwords in the `weblogic-application.xml` file with the `weblogic.j2ee.PasswordEncrypt` utility. This utility searches for database passwords in the following places:

- In the password tag:

>     <connection properties>
>       <password>tiger</password>
>     </connection properties>

- In the connection-params tag:

>     <connection properties>
>       <parameter>
>          <param-name>password</param-name>
>          <param-value>tiger</param-value>
>       </parameter>
>     </connection properties>

The utility hashes the passwords, replaces the passwords in the `weblogic-application.xml` file with a hashed version, and stores the hashed values in the `SerializedSystemIni.dat` in your WebLogic domain.

Note: Password encryption is domain specific. That is, when you run the encryption utility, you must specify the domain in which you will deploy your application. If you try to deploy the application in another domain, WebLogic Server will not be able to decrypt the passwords for use at runtime. For more information about encrypting passwords, see "Protecting Passwords" in Managing WebLogic Security.

You run this utility before your application archive is created. You cannot run it on a file that is already archived.

Before you run this utility, you should have WebLogic Server installed and your environment configured (so that the utility can find required classes). The server does not have to running when you run the password encryption utility.

To run the password encryption utility, enter the following command:

>     java weblogic.j2ee.PasswordEncrypt <descriptor file> <domain config dir>

Where:

- `descriptor file` is the weblogic-application.xml for the application.
- `domain config dir` is the root directory of the WebLogic domain (which contains the config.xml file).

After you run the password encryption utility, passwords may look like:

- In the password tag:

>     <connection properties>
>       <password>{3DES}iaHh5dH7clU=</password>
>     </connection properties>

- In the connection-params tag:

>     <connection properties>
>       <parameter>
>          <param-name>password</param-name>
>          <param-value>{3DES}iaHh5dH7clU=</param-value>
>       </parameter>
>     </connection properties>

Notes: If you need to change a password, you can change it in the weblogic-application.xml file and then re-run the password encryption utility. The utility will not re-encrypt passwords that are already encrypted.

You must re-encrypt passwords in the descriptor file if:

- You move the application from one installation of WebLogic Server to another.
- You delete the domain directory referenced when encrypting passwords, even if the directory is recreated.

### Deprecated Statement Cache Configuration Options for Application-Scoped Connection Pools

In releases before WebLogic Server 8.1, there were separate statement cache implementations for XA and non-XA JDBC connection pools. In WebLogic Server 8.1, the statement cache was rewritten. There is now one statement cache implementation for both XA and non-XA connection pools. With the statement cache revision, there is one tag available in the weblogic-application.xml descriptor file that is deprecated. Table 2-2 lists the deprecated descriptor tag, its replacement, and the related MBean attributes created when the application-scoped connection pool is deployed.

Table 2-2 Deprecated Statement Cache Descriptor Tags and Related MBeans Attributes

Deprecated | Equivalent in WebLogic Server 8.1
--- | ---
Deprecated descriptor tag: `<pool-params> <xa-params>` `<prepared-statement-cache-size>10 </prepared-statement-cache-size>` `</xa-params> </pool-params>` Note: Only the tag in bold is deprecated. The other tags are listed for contextual purposes only. | Use this tag instead: `<driver-params> <prepared-statement>` `<cache-size>10 </cache-size>` `</prepared-statement> </driver-params>`
MBean attribute set from tag above: `XaParamsMBean.PreparedStatementCacheSize` | MBean attribute set from tag above: `PreparedStatementMBean.CacheSize`

To enable migration of a WebLogic Server configuration or enterprise application from an earlier release to version 8.1, Weblogic Server enforces the following order of precedence for these MBean attributes:

1. `PreparedStatementMBean.CacheSize`
2. `XAParamsMBean.PreparedStatementCacheSize`

For example, if the `<cache-size>` for a JDBC connection pool is set to `5` in the `weblogic-application.xml` file and the `<prepared-statement-cache-size>` is set to `10`, the actual statement cache size for each connection in the connection pool will be 5 because `PreparedStatementMBean.CacheSize` takes precedence over `XaParamsMBean.PreparedStatementCacheSize`.

Note: When migrating an application from WebLogic Server 7.0 SP3 or later, to disable XA statement caching, you must set the `<cache-size>` for the JDBC connection pool in the `weblogic-application.xml` file to `0`.

## Getting a Connection from an Application-Scoped Connection Pool

To get a connection from an application-scoped connection pool, you look up the data source defined in the weblogic-application.xml descriptor file in the local environment (`java:comp/env`) and then request a connection from the data source. For example:

>     javax.sql.DataSource ds =
>         (javax.sql.DataSource) ctx.lookup("java:comp/env/myDataSource");
>     java.sql.Connection conn = ds.getConnection();

When you are finished using the connection, make sure you close the connection to return it to the connection pool:

>     conn.close();

# Configuring and Using MultiPools

A MultiPool is a pool of connection pools. All the connections in a particular connection pool are created identically with a single database, single user, and the same connection attributes; that is, they are attached to a single database. However, the connection pools within a MultiPool may be associated with different users or DBMSs.

## Configuring MultiPools

MultiPools contain a configurable algorithm for choosing which connection pool will return a connection to the client.

Create a MultiPool using the following steps:

1. Create necessary connection pools.
2. Determine if the primary purpose of the MultiPool is high availability or load balancing. See Choosing the MultiPool Algorithm.
3. Create the MultiPool using the Administration Console or WebLogic Management API and assign the connection pools to the MultiPool.

For more information about MultiPools, see the Administration Console Online Help. For information about the `JDBCMultiPoolMBean`, see the WebLogic Server Javadocs.

## Choosing the MultiPool Algorithm

Before you set up a MultiPool, you need to determine the primary purpose of the MultiPool—high availability or load balancing. You can choose the algorithm that corresponds with your requirements.

### High Availability

The High Availability algorithm provides an ordered list of connection pools. Normally, every connection request to this kind of MultiPool is served by the first pool in the list. If a database connection test fails and the connection cannot be replaced, or if the connection pool is suspended, a connection is sought sequentially from the next pool on the list.

Note: This algorithm relies on `TestConnectionsOnReserve` to test to see if a connection in the first connection pool is healthy. If the connection fails the test, the MultiPool uses a connection from the next connection pool in the MultiPool. See Testing Connection Pools and Database Connections for information about configuring `TestConnectionsOnReserve`.

### Load Balancing

Connection requests to a load balancing MultiPool are served from any connection pool in the list. Pools are accessed using a round-robin scheme. When the MultiPool provides a connection, it selects a connection from the connection pool listed just after the last pool that was used to provide a connection. MultiPools that use the Load Balancing algorithm also fail over to the next connection pool in the list if a database connection test fails and the connection cannot be replaced, or if the connection pool is suspended.

## Transaction Support in JDBC MultiPools

In WebLogic Server 8.1SP5, MultiPools were enhanced to provide support for global transactions.

Note: WebLogic Server 8.1 SP5 is certified to support Multipools with XA only on Oracle RAC. For information on supported versions of Oracle RAC, see Supported Database Configurations.

For an example of a MultiPool configuration that supports global transactions, see Using MultiPools with Global Transactions.

### Transaction Failover Processing for MultiPools

If a connection from a MultiPool fails while a global transaction is in progress, the result of the transaction depends on the stage of the transaction at the time of the connection failure.

The first stage at which a failure may occur is before the application calls for the transaction to be committed. If a database connection fails at this stage, the application gets an exception and must get a new connection and make a new attempt at processing the transaction. WebLogic Server does not support transparent failover.

If a failure occurs after the application has called for the transaction to be committed, the handling of any in-flight transaction depends upon whether the `PREPARE` operation is complete. If the `PREPARE` operation is not complete, the transaction manager rolls back the transaction and sends the application an exception for the failed transaction. If the `PREPARE` operation is complete, the transaction manager attempts to drive the in-flight transaction to completion using another connection.

If a failure occurs during the `COMMIT` operation, the transaction manager attempts to retry the `COMMIT` operation several times, depending on the `XARetryDurationSeconds` setting. Note that the connection is blocked during these attempts. If the `COMMIT` operation is not successful during the first set of retry attempts, the application gets an exception. The transaction manager then continues to retry the `COMMIT` operation periodically until it is successful or until it reaches the JTA Abandon Timeout period defined in the configuration file and abandons the transaction.

## MultiPool Failover Enhancements

In WebLogic Server 8.1SP3, the following enhancements were made to MultiPools:

- Connection request routing enhancements to avoid requesting a connection from an automatically disabled (dead) connection pool within a MultiPool. See Connection Request Routing Enhancements When a Connection Pool Fails.
- Automatic failback on recovery of a failed connection pool within a MultiPool. See Automatic Re-enablement on Recovery of a Failed Connection Pool within a MultiPool.
- Failover for busy connection pools within a MultiPools. See Enabling Failover for Busy Connection Pools in a MultiPool.
- Failover callbacks for MultiPools with the High Availability algorithm. See Controlling MultiPool Failover with a Callback.
- Failback callbacks for MultiPools with either algorithm. See Controlling MultiPool Failback with a Callback.

### Connection Request Routing Enhancements When a Connection Pool Fails

To improve performance when a connection pool within a MultiPool fails, WebLogic Server automatically disables the connection pool when a pooled connection fails a connection test. After a connection pool is disabled, WebLogic Server does not route connection requests from applications to the connection pool. Instead, it routes connection requests to the next available connection pool listed in the MultiPool.

This feature requires that connection pool testing options are configured for all connection pools in a MultiPool, specifically `TestTableName` and `TestConnectionsOnReserve`.

If a callback handler is registered for the MultiPool, WebLogic Server calls the callback handler before failing over to the next connection pool in the list. See Controlling MultiPool Failover with a Callback for more details.

### Automatic Re-enablement on Recovery of a Failed Connection Pool within a MultiPool

After a connection pool is automatically disabled because a connection failed a connection test, WebLogic Server periodically tests a connection from the disabled connection pool to determine when the connection pool (or underlying database) is available again. When the connection pool becomes available, WebLogic Server automatically re-enables the connection pool and resumes routing connection requests to the connection pool, depending on the MultiPool algorithm and the position of the connection pool in the list of included connection pools.

To control how often WebLogic Server checks automatically disabled connection pools in a MultiPool, you add a value for the `HealthCheckFrequencySeconds` attribute to the MultiPool configuration in the `config.xml` file. For example:

>     <JDBCMultiPool
>     AlgorithmType="High-Availability"
>     Name="demoMultiPool"
>     PoolList="demoPool2,demoPool"
>     HealthCheckFrequencySeconds="240"
>     Targets="examplesServer" />

Note: This attribute is not available in the administration console. To implement this functionality, you must manually add the attribute to the MultiPool configuration in the `config.xml` file.

WebLogic Server waits for the period you specify between connection tests for each disabled connection pool. The default value is 300 seconds. If you do not specify a value, WebLogic Server will test automatically disabled connection pools every 300 seconds.

This feature requires that connection pool testing options are configured for all connection pools in a MultiPool, specifically `TestTableName` and `TestConnectionsOnReserve`.

WebLogic Server does not test and automatically re-enable connection pools that you manually disable. It only tests connection pools that it automatically disables.

If a callback handler is registered for the MultiPool, WebLogic Server calls the callback handler before re-enabling the connection pool. See Controlling MultiPool Failback with a Callback for more details.

### Enabling Failover for Busy Connection Pools in a MultiPool

By default, for MultiPools with the High Availability algorithm, when the number of requests for a database connection exceeds the number of available connections in the current connection pool in the MultiPool, subsequent connection requests fail.

To enable the MultiPool to failover when all connections in the current connection pool are in use, you must set a value for the `FailoverRequestIfBusy` attribute in the MultiPool configuration in the `config.xml` file. If set to `true`, when all connections in the current connection pool are in use, application requests for connections will be routed to the next available connection pool within the MultiPool. When set to `false` (the default), connection requests do not failover. Weblogic Server throws a `weblogic.jdbc.extensions.PoolUnavailableSQLException`.

After you add the `FailoverRequestIfBusy` attribute to the `config.xml` file, the MultiPool entry may look like the following:

>     <JDBCMultiPool
>     AlgorithmType="High-Availability"
>     Name="demoMultiPool"
>     PoolList="demoPool2,demoPool"
>     FailoverRequestIfBusy="true"
>     Targets="examplesServer" />

Note: The `FailoverRequestIfBusy` attributes is not available in the administration console. To implement this functionality, you must manually add this attribute to the MultiPool configuration in the `config.xml` file.

If a `ConnectionPoolFailoverCallbackHandler` is included in the MultiPool configuration, WebLogic Server calls the callback handler before failing over. See Controlling MultiPool Failover with a Callback for more details.

### Controlling MultiPool Failover with a Callback

You can register a callback handler with WebLogic Server that controls when a MultiPool with the High-Availability algorithm fails over connection requests from one JDBC connection pool in the MultiPool to the next connection pool in the list.

You can use callback handlers to control if or when the failover occurs so that you can make any other system preparations before the failover, such as priming a database or communicating with a high-availability framework.

Callback handlers are registered via an attribute of the MultiPool in the `config.xml` file and are registered per MultiPool. Therefore, you must register a callback handler for each MultiPool to which you want the callback to apply. And you can register different callback handlers for each MultiPool.

#### Callback Handler Requirements

A callback handler used to control the failover and failback within a MultiPool must include an implementation of the `weblogic.jdbc.extensions.ConnectionPoolFailoverCallback` interface. When the MultiPool needs to failover to the next connection pool in the list or when a previously disabled connection pool becomes available, WebLogic Server calls the `allowPoolFailover()`method in the `ConnectionPoolFailoverCallback` interface, and passes a value for the three parameters, `currPool`, `nextPool`, and `opcode`, as defined below. WebLogic Server then waits for the return from the callback handler before completing the task.

Your application must return OK, RETRY_CURRENT, or DONOT_FAILOVER as defined below.The application should handle failover and failback cases.

See the Javadoc for the weblogic.jdbc.extensions.ConnectionPoolFailoverCallback interface for more details.

Note: Failover callback handlers are optional.If no callback handler is specified in the MultiPool configuration, WebLogic Server proceeds with the operation (failing over or re-enabling the disabled connection pool).

#### Callback Handler Configuration

There are two MultiPool configuration attributes associated with the failover and failback functionality:

- `ConnectionPoolFailoverCallbackHandler`—To register a failover callback handler for a MultiPool, you add a value for this attribute to the MultiPool configuration in the `config.xml` file. The value must be an absolute name, such as `com.bea.samples.wls.jdbc.MultiPoolFailoverCallbackApplication`.

`HealthCheckFrequencySeconds`—To control how often WebLogic Server checks disabled (dead) connection pools in a MultiPool to see if they are now available, you can add a value for this attribute to the MultiPool configuration in the `config.xml` file.

The maximum value that can be passed to the method is MAXINT while the minimum value is 0. Setting the value to zero disables the attribute.See Automatic Re-enablement on Recovery of a Failed Connection Pool within a MultiPool for more details.

After you add the attributes to the `config.xml` file, the MultiPool entry may look like the following:

>     <JDBCMultiPool
>     AlgorithmType="High-Availability"
>     Name="demoMultiPool"
>     ConnectionPoolFailoverCallbackHandler="com.bea.samples.wls.jdbc.MultiPoolFailoverCallbackApplication"
>     PoolList="demoPool2,demoPool"
>     HealthCheckFrequencySeconds="120"
>     Targets="examplesServer" />

Note: These attributes are not available in the administration console. To implement this functionality, you must manually add these attributes to the MultiPool configuration in the `config.xml` file.

#### How It Works—Failover

WebLogic Server attempts to failover connection requests to the next connection pool in the list when the current connection pool fails a connection test or, if you enabled `FailoverRequestIfBusy`, when all connections in the current connection pool are busy.

To enable the callback feature, you register the callback handler with Weblogic Server using the `ConnectionPoolFailoverCallbackHandler` attribute in the MultiPool configuration in the `config.xml` file.

With the High Availability algorithm, connection requests are served from the first connection pool in the list. If a connection from that connection pool fails a connection test, WebLogic Server marks the connection pool as dead and disables it. If a callback handler is registered, WebLogic Server calls the callback handler, passing the following information, and waits for a return:

- `currPool`—For failover, this is the name of connection pool currently being used to supply database connections. This is the "failover from" connection pool.
- `nextPool`—The name of next available connection pool listed in the MultiPool. For failover, this is the "failover to" connection pool.
- `opcode`—A code that indicates the reason for the call:

- `OPCODE_CURR_POOL_DEAD`—WebLogic Server determined that the current connection pool is dead and has disabled it.
- `OPCODE_CURR_POOL_BUSY`—All database connections in the connection pool are in use. (Requires `FailoverIfBusy=true` in the MultiPool configuration. See Enabling Failover for Busy Connection Pools in a MultiPool.)

Failover is synchronous with the connection request: Failover occurs only when WebLogic Server is attempting to satisfy a connection request.

The return from the callback handler can indicate one of three options:

- `OK`—proceed with the operation. In this case, that means to failover to the next connection pool in the list.
- `RETRY_CURRENT`—Retry the connection request with the current connection pool.
- `DONOT_FAILOVER`—Do not retry the current connection request and do not failover. WebLogic Server will throw a `weblogic.jdbc.extensions.PoolUnavailableSQLException`.

WebLogic Server acts according to the value returned by the callback handler.

If the secondary connection pools fails, WebLogic Server calls the callback handler again, as in the previous failover, in an attempt to failover to the next available connection pool in the MultiPool, if there is one.

Note: WebLogic Server does not call the callback handler when you manually disable a connection pool.

For MultiPools with the Load-Balancing algorithm, WebLogic Server does not call the callback handler when a connection pool is disabled. However, it does call the callback handler when attempting to re-enable a disabled connection pool. See the following section for more details.

### Controlling MultiPool Failback with a Callback

If you register a failover callback handler for a MultiPool, WebLogic Server calls the same callback handler when re-enabling a connection pool that was automatically disabled. You can use the callback to control if or when the disabled connection pool is re-enabled so that you can make any other system preparations before the connection pool is re-enabled, such as priming a database or communicating with a high-availability framework.

Callback handlers are registered via an attribute of the MultiPool in the `config.xml` file and are registered per MultiPool. Therefore, you must register a callback handler for each MultiPool to which you want the callback to apply. And you can register different callback handlers for each MultiPool.

See the following sections for more details about the callback handler:

- Callback Handler Requirements
- Callback Handler Configuration

#### How It Works—Failback

WebLogic Server periodically checks the status of connection pools in a MultiPool that were automatically disabled. (See Automatic Re-enablement on Recovery of a Failed Connection Pool within a MultiPool.) If a disabled connection pool becomes available and if a failover callback handler is registered, WebLogic Server calls the callback handler with the following information and waits for a return:

- `currPool`—For failback, this is the name of the connection pool that was previously disabled and is now available to be re-enabled.
- `nextPool`—For failback, this is null.
- `opcode`—A code that indicates the reason for the call. For failback, the code is always `OPCODE_REENABLE_CURR_POOL`, which indicates that the connection pool named in `currPool` is now available.

Failback, or automatically re-enabling a disabled connection pool, differs from failover in that failover is synchronous with the connection request, but failback is asynchronous with the connection request.

The callback handler can return one of the following values:

- `OK`—proceed with the operation. In this case, that means to re-enable the indicated connection pool. WebLogic Server resumes routing connection requests to the connection pool, depending on the MultiPool algorithm and the position of the connection pool in the list of included connection pools.
- `DONOT_FAILOVER`—Do not re-enable the `currPool` connection pool. Continue to serve connection requests from the connection pool(s) in use.

WebLogic Server acts according to the value returned by the callback handler.

If the callback handler returns DONOT_FAILOVER, WebLogic Server will attempt to re-enable the connection pool during the next testing cycle as determined by the `HealthCheckFrequencySeconds` attribute in the MultiPool configuration, and will call the callback handler as part of that process.

The order in which connection pools are listed in a MultiPool is very important. A MultiPool with the High Availability algorithm will always attempt to serve connection requests from the first available connection pool in the list of connection pools in the MultiPool. Consider the following scenario:

`MultiPool_1` uses the High Availability algorithm, has a registered `ConnectionPoolFailoverCallbackHandler`, and includes three connection pools: `CP1`, `CP2`, and `CP3`, listed in that order.

`CP1` becomes disabled, so `MultiPool_1` fails over connection requests to `CP2`.

`CP2` then becomes disabled, so `MultiPool_1` fails over connection requests to `CP3`.

After some time, `CP1` becomes available again and the callback handler allows WebLogic Server to re-enable the connection pool. Future connection requests will be served by `CP1` because `CP1` is the first connection pool listed in the MultiPool.

If `CP2` subsequently becomes available and the callback handler allows WebLogic Server to re-enable the connection pool, connection requests will continue to be served by `CP1` because `CP1` is listed before `CP2` in the list of connection pools.

## MultiPool Fail-Over Limitations and Requirements

WebLogic Server provides the High Availability algorithm for MultiPools so that if a connection pool fails (for example, if the database management system crashes), your system can continue to operate. However, you must consider the following limitations and requirements when configuring your system.

### Test Connections on Reserve to Enable Fail-Over

Connection pools rely on the `TestConnectionsOnReserve` feature to know when database connectivity is lost. Connections are not automatically tested before being reserved by an application. You must set `TestConnectionsOnReserve=true` for the connection pools within the MultiPool. After turning on this feature, WebLogic Server will test each connection before returning it to an application, which is crucial to the High Availability algorithm operation. With the High Availability algorithm, the MultiPool uses the results from testing connections on reserve to determine when to fail over to the next connection pool in the MultiPool. After a test failure, the connection pool attempts to recreate the connection. If that attempt fails, the MultiPool fails over to the next connection pool.

### By Default, No Fail-Over When All Connections are In Use

By default, if all connections in the primary connection pool are being used, a MultiPool with the High Availability algorithm will not attempt to provide a connection from the next pool in the list. MultiPool failover takes effect only if loss of database connectivity has occurred (or the connection pool has been disabled). See Enabling Failover for Busy Connection Pools in a MultiPool for information about enabling failover in a MultiPool when all connections in a connection pool are in use.

### Do Not Enable Connection Creation Retries

Do not enable connection creation retries with connection pools in a High Availability MultiPool. Connection requests to the MultiPool will fail (not fail-over) when a connection pool in the list is dead and the number of connection requests equals the number of connections in the first connection pool, even if connections are available in subsequent connection pools in the MultiPool.

MultiPools and the connection creation retries feature both attempt to solve the same problem—to gracefully handle database connections when a database is unavailable. If you use these two features together, their functionality will interfere with each other.

### No Fail-Over for In-Use Connections

It is possible for a connection to fail after being reserved, in which case your application must handle the failure. WebLogic Server cannot provide fail-over for connections that fail while being used by an application. Any failure while using a connection requires that you restart the transaction and provide code to handle such a failure.