Skip navigation.

eDocs Home > BEA WebLogic Server and WebLogic Express 8.1 Documentation > Programming WebLogic JDBC > Configuring and Using WebLogic JDBC

Programming WebLogic JDBC

  Previous Next vertical dots separating previous/next from contents/index/pdf Contents View as PDF   Get Adobe Reader

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:

For additional information, see

 

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:

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:

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:

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:

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)mbeanHome.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:

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:

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.

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:

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:

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:

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:

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.

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:

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:

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:

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:

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

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:

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:app/jdbc/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 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:

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:

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:

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:

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:

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:

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.

 

Skip navigation bar  Back to Top Previous Next