Configuring and Using WebLogic JDBC

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

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

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

 

Configuring and Using Connection Pools

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

 

Advantages to Using Connection Pools

Connection pools provide numerous performance and application design advantages:

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

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

 

Creating a Connection Pool at Startup

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

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

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

 

Avoiding Server Lockup with the Correct Number of Connections

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

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

 

Database Passwords in Connection Pool Configuration

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

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

    Note that after the userName there is no password.

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

user=scott;



password=tiger;



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

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

user=scott;



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

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

 

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, 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, remove it programmatically.

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

 

Dynamic Connection Pool Sample Code

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

 

Import Packages

import java.sql.*;



import java.util.*;



import javax.naming.Context;



import javax.sql.DataSource;



import weblogic.jndi.Environment;



import weblogic.management.configuration.JDBCConnectionPoolMBean;



import weblogic.management.runtime.JDBCConnectionPoolRuntimeMBean;



import weblogic.management.configuration.JDBCTxDataSourceMBean;



import weblogic.management.configuration.ServerMBean;



import weblogic.management.MBeanHome;



import weblogic.management.WebLogicObjectName;

String cpName = null;



String cpJNDIName = null;

 

Look Up the Administration MBeanHome

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

 

Get the Server MBean

svrAdminMBean = (ServerMBean)adminMBeanHome.getAdminMBean("myserver",



 "Server");

 

Create the Connection Pool MBean

  // Create ConnectionPool MBean


cpMBean = (JDBCConnectionPoolMBean)mbeanHome.createAdminMBean(


  cpName, "JDBCConnectionPool", 


  mbeanHome.getDomainName());

 

Set the Connection Pool Properties

  Properties pros = new Properties();


pros.put("user", "scott");


  pros.put("server", "dbserver1t1");

  // Set DataSource attributes


cpMBean.setURL("jdbc:weblogic:oracle");


cpMBean.setDriverName("weblogic.jdbc.oci.xa.XADataSource");


cpMBean.setProperties(pros);


cpMBean.setPassword("tiger");

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

 

Add the Target

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

  cpMBean.addTarget(serverMBean);

 

Create a DataSource

public void createDataSource() throws SQLException {


try {


  // Get context


  Environment env = new Environment();


  env.setProviderUrl(url);


  env.setSecurityPrincipal(userName);


  env.setSecurityCredentials(password);


  ctx = env.getInitialContext();

      // Create TxDataSource  MBean


    dsMBean = (JDBCTxDataSourceMBean)mbeanHome.createAdminMBean(


      cpName, "JDBCTxDataSource",


      mbeanHome.getDomainName());

      // Set TxDataSource attributes


    dsMBean.setJNDIName(cpJNDIName);


    dsMBean.setPoolName(cpName);

      // Startup datasource


    dsMBean.addTarget(serverMBean);

    } catch (Exception ex) {


    ex.printStackTrace();


    throw new SQLException(ex.toString());

    }

  }
Note that 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 that 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 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");

  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 that the code above uses one of several available procedures for obtaining a JNDI context. For more information on JNDI, see Programming WebLogic JNDI.

 

Possible Exceptions When a Connection Request Fails

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

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

 

Connection Pool Limitation

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

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

If you use DBMS-specific SQL code that alters the connection, 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:

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

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

 

Enabling Connection Creation Retries

JDBCConnectionPoolMBean.setConnectionCreationRetryFrequencySeconds(int seconds)

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

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

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

 

Initializing Connections with a SQL Query

JDBCConnectionPoolMBean.setInitSQL(java.lang.String string)

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

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

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

or 

SQL SET LOCK MODE TO WAIT

Options that you can set using InitSQL vary by DBMS.

Note: InitSQL is not a dynamic attribute. When you change the value for InitSQL, 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 the WebLogic Server to automatically maintain the integrity of pool connections should prevent most DBMS connection problems. Use the following methods on the JDBCConnectionPoolMBean to configure automatic connection testing:

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

 

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. Use these two attributes together to enable connection requests to wait for a connection without disabling your system by blocking too many threads.

 

Connection Reserve Timeout

JDBCConnectionPoolMBean.setConnectionReserveTimeoutSeconds(int seconds)

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

If you set Connection Reserve Timeout to -1, a connection request will wait indefinitely. If you set Connection Reserve Timeout to 0, a connection request will not wait for a connection if none are available when the request is made. The default value is 10 seconds.

 

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

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, 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, 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, include the following sub-elements:

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

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

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


<factory-name>XA_LocalCF1</factory-name>


<connection-properties>


  <user-name>SCOTT</user-name>


  <password>tiger</password>


  <url>jdbc:oracle:thin:@dbserver:1521:sid</url>


  <driver-class-name>oracle.jdbc.xa.client.OracleXADataSource


    </driver-class-name>


  <connection-params>


    <parameter>


      <param-name>foo</param-name>


      <param-value>
xyz</param-value>


    </parameter>


    <parameter>


      <param-name>bar</param-name>


      <param-value>abc</param-value>


    </parameter>


  </connection-params>


</connection-properties>



    </connection-factory>

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

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

 

Encrypting the Database Password in weblogic-application.xml

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

  • In the password tag: 
    <connection properties>


<password>tiger</password>



    </connection properties>
  • In the connection-params tag: 
    <connection properties>


<parameter>


   <param-name>password</param-name>


   <param-value>tiger</param-value>


</parameter>



    </connection properties>

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

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

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

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

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

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

Where:

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

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

  • In the password tag: 
    <connection properties>


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



    </connection properties>
  • In the connection-params tag: 
    <connection properties>


<parameter>


   <param-name>password</param-name>


   <param-value>{3DES}iaHh5dH7clU=</param-value>


</parameter>



    </connection properties>

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

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

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

 

Deprecated Statement Cache Configuration Options for Application-Scoped Connection Pools

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

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, set the <cache-size> for the JDBC connection pool in the weblogic-application.xml file to 0.

 

Getting a Connection from an Application-Scoped Connection Pool

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

javax.sql.DataSource ds = 


  (javax.sql.DataSource) ctx.lookup("java:comp/env/myDataSource");



java.sql.Connection conn = ds.getConnection();

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

conn.close();

 

Configuring and Using MultiPools

A MultiPool is a "pool of pools." MultiPools contain a configurable algorithm for choosing which connection pool will return a connection to the client.

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 more information about MultiPools, see the Administration Console Online Help. For information about the JDBCMultiPoolMBean, see the WebLogic Server Javadocs.

 

MultiPool Features

A MultiPools is a pool of connection pools in a single server. 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.

MultiPools are used in local transactions and are not supported by WebLogic Server for 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.

Note: Capacity is not a failover reason, because users have the right to set capacity. MultiPools take effect only if loss of database connectivity has occurred.

 

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.

 

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

 

No Fail-Over When All Connections are In Use

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 fail-over takes effect only if loss of database connectivity has occurred (or the connection pool has been disabled). This is by design so that you can set the capacity for a connection pool. To avoid this situation, you should increase the maximum number of connections in the connection pool.

 

Connection Requests are Always Routed to Connection Pools in Order

When an application requests a connection from a MultiPool, the request is routed to connection pools in the list in order until a connection is available or until the last connection pool in the list, even if the MultiPool is aware that the first "n" (1 or more) connection pools are unavailable (dead or disabled). When connection pools in the MultiPool are unavailable, there is a performance cost to every application until the first connection pool in the list is restored.

 

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.

 

Messages and Error Conditions for MultiPools

Entries are posted to the JDBC log under these circumstances:

  • At boot time, when a connection pool is added to a MultiPool.
  • Whenever there is a switch to a new connection pool within the MultiPool, either during load balancing or high availability.

 

Capacity Issues

In a high availability scenario, the fact that the first pool in the list is busy (all connections are being used) does not trigger an attempt to get a connection from the next pool in the list.

Skip navigation bar  Back to Top Previous Next