bea.com | products | dev2dev | support | askBEA

Download Docs | Site Map | Glossary
Search

Programming WebLogic JDBC

Configuring and Administering WebLogic JDBC

You use WebLogic Server Administration Console to enable, configure, and monitor features of the WebLogic Server, including JDBC.

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

- Configuring and Using Connection Pools

- Application-Scoped JDBC Connection Pools

- Configuring and Using MultiPools

- Configuring and Using DataSources

For additional information, see

- Managing JDBC Connectivity in the Administration Guide. Describes how to use the Administration Console and command-line interface to configure and manage connectivity.

- Administration Console Online Help. Describes how to use the Administration Console to set specific configuration tasks.

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 registered, 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 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 in the Administration Guide.

Creating a Connection Pool at Startup

To create a startup (static) connection pool, you define attributes and permissions in the Administration Console before starting WebLogic Server. 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. For more information about creating and configuring connection pools with the Administration Console, see "Managing JDBC Connectivity" in the Administration Guide.

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. Connection pools do not queue requests for a connection. 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. 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.

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 not 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. When the connection is returned to the connection pool and then reused, the client will still only get 3 rows returned, even if the table they are selecting against has 500 rows. In most cases, there is standard (non-DBMS-specific) SQL code that can accomplish the same result and for which WebLogic Server or the JDBC driver will reset the connection. In this example, you could use setMaxRows() instead of set rowcount.

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

Notes About Refreshing Connections in a JDBC Connection Pool

When the refresh process finds a bad database connection that it cannot replace, the process stops its current cycle. It does not delete remaining broken connections from the connection pool. They remain in the connection pool until they can be replaced by new connections. This behavior was designed to avoid degrading performance by using system cycles to refresh database connections when the DBMS is inaccessible.

The refresh process cannot test or refresh connections currently being used by application code. It will only test connections that are not currently reserved. Thus a refresh cycle, even if it is able to replace any bad connections it finds, may never test all connections in the connection pool if applications are requesting connections.

Because the refresh process can only test connections not in use, it's possible that some connections will never be tested. A client will always run the risk of getting a broken connection unless testConnsOnReserve is enabled. In fact, even if the connection is tested before being given to an application, the connection could go bad immediately after the successful test.

JDBC Connection Pool Testing Enhancements

In WebLogic Server 7.0SP5, 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 connection requests after a database failure. See Minimizing Connection Request Delay After Connection Test Failures.

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"
>       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 refersh 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 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"
>       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 SecondsToTrustAnIdlePoolConnection

Database connection testing during heavy traffic can reduce application performance. To minimize the impact of connection testing, you can set the secondsToTrustAnIdlePoolConnection connection property 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 (RefreshMinutes 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 add it to the list of Properties on the JDBC Connection Pool —> Configuration —> General tab in the Administration Console. See "JDBC Connection Pool --> Configuration --> General" 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;
>         secondsToTrustAnIdlePoolConnection=15;
>         DatabaseName=jdbc:pointbase:server://localhost/demo"
>       Targets="examplesServer"
>       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 JDBCConnectionPool MBean 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 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 in the Administration Guide.

To dynamically create a connection pool using the JDBCConnectionPool administration MBean, 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 JDBCConnectionPool MBean, 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.

Also, you can temporarily disable dynamically created connection pools, which suspends communication with the database server through any connection in the pool. When a disabled pool is re-enabled, each connection returns to the same state as when the pool was disabled; clients can continue their database operations exactly where they left off.

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 for WebLogic Classes.

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.JDBCDataSourceMBean;
>     import weblogic.management.configuration.ServerMBean;
>     import weblogic.management.MBeanHome;
>     import weblogic.management.WebLogicObjectName;

Look Up the Administration MBeanHome

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

Get the Server MBean

>     serverMBean = (ServerMBean)mbeanHome.getAdminMBean(serverName, "Server");
>     //Create a WebLogic object name for the Server MBean
>     //to use to create a name for the JDBCConnectionPoolRuntime MBean.
>     WebLogicObjectName pname = new WebLogicObjectName("server1", "ServerRuntime", mbeanHome.getDomainName(),"server1");
>     //Create a WebLogic object name for the JDBCConnectionPoolRuntime MBean
>     //to use to create or get the JDBCConnectionPoolRuntime MBean.
>     WebLogicObjectName oname = new WebLogicObjectName(cpName, "JDBCConnectionPoolRuntime", mbeanHome.getDomainName(),"server1", pname);
>     JDBCConnectionPoolRuntimeMBean cprmb = (JDBCConnectionPoolRuntimeMBean)mbeanHome.getMBean(oname);

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", "lcdbnt1");

>       // Set DataSource attributes
>       cpMBean.setURL("jdbc:weblogic:oracle");
>       cpMBean.setDriverName("weblogic.jdbc.oci.Driver");
>       cpMBean.setProperties(pros);
>       cpMBean.setPassword("tiger");
>       cpMBean.setLoginDelaySeconds(1);
>       cpMBean.setInitialCapacity(1);
>       cpMBean.setMaxCapacity(10);
>       cpMBean.setCapacityIncrement(1);
>       cpMBean.setShrinkingEnabled(true);
>       cpMBean.setShrinkPeriodMinutes(10);
>       cpMBean.setRefreshMinutes(10);
>       cpMBean.setTestTableName("dual");

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

>       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 DataSource  MBean
>           dsMBeans = (JDBCDataSourceMBean)mbeanHome.createAdminMBean(
>             cpName, "JDBCDataSource",
>             mbeanHome.getDomainName());

>           // Set DataSource attributes
>           dsMBeans.setJNDIName(cpJNDIName);
>           dsMBeans.setPoolName(cpName);

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

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

>         }

>       }

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 datasource from the server

>           dsMBeans.removeTarget(serverMBean);

>           // Remove dynamically created datasource from the configuration

>           mbeanHome.deleteMBean(dsMBeans);

>         } catch (Exception ex) {

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

>         }

>       }

Managing Connection Pools

The JDBCConnectionPool and JDBCConnectionPoolRuntime MBeans provide methods to manage connection pools and obtain information about them. Methods are provided for:

- Retrieving information about a pool

- Disabling a connection pool, which prevents clients from obtaining a connection from it

- Enabling a disabled pool

- Shrinking a pool, which releases unused connections until the pool has reached the minimum specified pool size

- Refreshing a pool, which closes and reopens its connections

- Shutting down a pool

The JDBCConnectionPool and JDBCConnectionPoolRuntime MBeans replace the weblogic.jdbc.common.JdbcServices and weblogic.jdbc.common.Pool classes, which are deprecated.

For more information about methods provided by the JDBCConnectionPool MBean, see the Javadoc. For more information about the methods provided by the JDBCConnectionPoolRuntime MBean, see the Javadoc.

Retrieving Information About a Pool

>     boolean x = JDBCConnectionPoolRuntimeMBean.poolExists(cpName);

>     props = JDBCConnectionPoolRuntimeMBean.getProperties();

The poolExists() method tests whether a connection pool with a specified name exists in the WebLogic Server. You can use this method to determine whether a dynamic connection pool has already been created or to ensure that you select a unique name for a dynamic connection pool you want to create.

The getProperties() method retrieves the properties for a connection pool.

Disabling a Connection Pool

  JDBCConnectionPoolRuntimeMBean.disableDroppingUsers()

  JDBCConnectionPoolRuntimeMBean.disableFreezingUsers()

  JDBCConnectionPoolRuntimeMBean.enable()

You can temporarily disable a connection pool, preventing any clients from obtaining a connection from the pool. Only the "system" user or users granted "admin" permission by an ACL associated with a connection pool can disable or enable the pool.

After you call disableFreezingUsers(), clients that currently have a connection from the pool are suspended. Attempts to communicate with the database server throw an exception. Clients can, however, close their connections while the connection pool is disabled; the connections are then returned to the pool and cannot be reserved by another client until the pool is enabled.

Use disableDroppingUsers() to not only disable the connection pool, but to destroy the client's JDBC connection to the pool. Any transaction on the connection is rolled back and the connection is returned to the connection pool. The client's JDBC connection context is no longer valid.

When a pool is enabled after it has been disabled with disableFreezingUsers(), the JDBC connection states for each in-use connection are exactly as they were when the connection pool was disabled; clients can continue JDBC operations exactly where they left off.

You can also use the disable_pool and enable_pool commands of the weblogic.Admin class to disable and enable a pool.

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 shrink the pool.

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.

Shutting Down a Connection Pool

  JDBCConnectionPoolRuntimeMBean.shutdownSoft()

  JDBCConnectionPoolRuntimeMBean.shutdownHard()

These methods destroy a connection pool. Connections are closed and removed from the pool and the pool dies when it has no remaining connections. Only the "system" user or users granted "admin" permission by an ACL associated with a connection pool can destroy the pool.

The shutdownSoft() method waits for connections to be returned to the pool before closing them.

The shutdownHard() method kills all connections immediately. Clients using connections from the pool get exceptions if they attempt to use a connection after shutdownHard() is called.

You can also use the destroy_pool command of the weblogic.Admin class to destroy a pool.

Resetting a Pool

  JDBCConnectionPoolRuntimeMBean.reset()

You can configure a connection pool to test its connections either periodically, or every time a connection is reserved or released. Allowing the WebLogic Server to automatically maintain the integrity of pool connections should prevent most DBMS connection problems. In addition, WebLogic provides methods you can call from an application to refresh all connections in the pool or a single connection you have reserved from the pool.

The JDBCConnectionPoolRuntimeMBean.reset() method closes and reopens all allocated 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.

Use any of the following means to reset a connection pool:

- The Administration Console.

- The weblogic.Admin command (as a user with administrative privileges) to reset a connection pool, as an administrator. Here is the pattern:

>     $ java weblogic.Admin WebLogicURL RESET_POOL poolName system passwd

You might use this method from the command line on an infrequent basis. There are more efficient programmatic ways that are also discussed here.

- The reset() method from the JDBCConnectionPoolRuntimeMBean in your client application.

  The last case requires the most work for you, but also gives you flexibility. To reset a pool using the reset() method:

  1. In a try block, test a connection from the connection pool with a SQL statement that is guaranteed to succeed under any circumstances so long as there is a working connection to the DBMS. An example is the SQL statement select 1 from dual which is guaranteed to succeed for an Oracle DBMS.

  2. Catch the SQLException.

  3. Call the reset() method in the catch block.

Using weblogic.jdbc.common.JdbcServices and weblogic.jdbc.common.Pool Classes (Deprecated)

Previous versions of WebLogic Server included classes that you could use to programmatically create and manage connection pools: weblogic.jdbc.common.JdbcServices and weblogic.jdbc.common.Pool. These classes are now deprecated. Although these classes are still available, BEA recommends that you use the JDBCConnectionPool MBean instead of these classes to dynamically create and manage connection pools.

When you use the JDBCConnectionPool MBean to create or modify a connection pool on a managed server, the JMX service immediately notifies the administration server of the change. When you use weblogic.jdbc.common.JdbcServices and weblogic.jdbc.common.Pool to create or modify a connection pool, the following actions are not conveyed to the Administration Server:

- shutdown

- retrieve

- refresh

- enable

- disable

After any of these actions, applications on managed servers that use the affected connection pool may fail.

For more information about weblogic.jdbc.common.JdbcServices and weblogic.jdbc.common.Pool, see "Configuring WebLogic JDBC Features" in Programming WebLogic JDBC for WebLogic Server 6.1.

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:

- weblogic-application.xml Deployment Descriptor Elements in Developing WebLogic Server Applications app-scoped-pool.

- Packaging Enterprise Applications in Developing WebLogic Server Applications.

- Two-Phase Deployment in Developing WebLogic Server Applications.

Configuring and Using MultiPools

A MultiPool is a "pool of pools." You create a MultiPool by first creating connection pools, then creating the MultiPool using the Administration Console or WebLogic Management API and assigning the connection pools to the MultiPool.

For instructions to create a MultiPool using the Administration Console, see the Administration Console Online Help. For information about the JDBCMultiPoolMBean, see the WebLogic Server Javadocs.

Note: If you are not using global transactions (XA), you can only use MultiPools to connect to Oracle RAC.

MultiPool Features

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

Database connections from a MultiPool are used in local transactions only and are not supported by WebLogic Server for use in distributed transactions.

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 via that pool fails, then a connection is sought sequentially from the next pool on the list.

Notes: You must set TestConnectionsOnReserve=true for the connection pools within the MultiPool so that the MultiPool can determine when to fail over to the next connection pool in the list.

By default, if all connections in a 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. This is by design so that you can set the capacity for a connection pool. You can enable failover in this scenario by setting the FailoverRequestIfBusy attribute in the MultiPool configuration to true. See Enabling Failover for Busy Connection Pools in a MultiPool for more details.

Load Balancing

Connection requests to a load balancing MultiPool are served from any connection pool in the list. Pools are added in the order listed and are accessed using a round-robin scheme. When an application requests a connection, the MultiPool attempts to provide a connection from the next connection pool in the list.

MultiPool Failover Enhancements

In WebLogic Server 7.0SP5, 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.[Which exception is thrown?]

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. [Add link]

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. 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 you turn 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. See MultiPool Failover Enhancements for details about enhancements to MultiPool failover.

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.

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)

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 multiple database updates during a single transaction.

For more information about when to use a TxDataSource and how to configure a TxDataSource, see JDBC Configuration Guidelines for Connection Pools, MultiPools, and DataSources.

If you want applications to use a DataSource 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 instructions to create a DataSource, see the Administration Console Online Help. For instructions to create a TxDataSource, see the Administration Console Online Help.

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 from a JDBC client, use a Java Naming and Directory Interface (JDNI) lookup to locate the DataSource object, as shown in this code fragment:

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

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

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

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

>     // Close the statement and connection objects when you are finished:

>        stmt.close();
>        conn.close();
>      }
>       catch (NamingException e) {
>         // a failure occurred
>       }
>       finally {
>         try {ctx.close();}
>         catch (Exception e) {
>           // a failure occurred
>         }
>       }

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

Code Examples

See the DataSource code example in the samples/examples/jdbc/datasource directory of your WebLogic Server installation.

JDBC Data Source Factories

In WebLogic Server, you can bind a JDBC DataSource resource into the WebLogic Server JNDI tree as a resource factory. You can then map a resource factory reference in the EJB deployment descriptor to an available resource factory in a running WebLogic Server to get a connection from a connection pool.

For details about creating and using a JDBC Data Source factory, see Resource Factories in Programming WebLogic Enterprise JavaBeans.