Configuring and Managing WebLogic JDBC

     Previous  Next    Open TOC in new window  Open Index in new window  View as PDF - New Window  Get Adobe Reader - New Window
Content starts here

Configuring JDBC Data Sources

This section includes the following information:

 


Understanding JDBC Data Sources

In WebLogic Server, you configure database connectivity by adding data sources to your WebLogic domain. WebLogic JDBC data sources provide database access and database connection management. Each data source contains a pool of database connections that are created when the data source is created and at server startup. Applications reserve a database connection from the data source by looking up the data source on the JNDI tree or in the local application context and then calling getConnection(). When finished with the connection, the application should call connection.close() as early as possible, which returns the database connection to the pool for other applications to use.

Data sources and their connection pools provide connection management processes that help keep your system running and performant.You can set options in the data source to suit your applications and your environment. The following sections describe these options and how to enable them.

 


Creating a JDBC Data Source

To create a JDBC data source in your WebLogic domain, you can use the Administration Console or the WebLogic Scripting Tool (WLST). See the following for more information:

Note: WLST replaced the weblogic.Admin command line utility. The WebLogic Server examples that are optionally installed with WebLogic Server contain sample scripts that can be used in place of the weblogic.Admin JDBC commands. If installed, the example scripts are available at WL_HOME\samples\server\examples\src\examples\wlst\online, where WL_HOME refers to the main WebLogic directory, such as C:\bea\weblogic91.

For more information about JDBC data source attributes, see the “ JDBCDataSourceBean” and all of its child MBeans in the WebLogic Server MBean Reference

Notes: JDBC drivers listed in the Create JDBC Data Source pages in the Administration Console are not necessarily certified for use with WebLogic Server. In keeping with the goal of the Create JDBC Data Source pages, JDBC drivers are listed as a convenience to help you create connections to many of the database management systems available.
Note: You must install JDBC drivers in order to use them to create database connections in a data source on each server on which the data source is deployed. Drivers are listed in the Create JDBC Data Source pages in the Administration Console with known required configuration options to help you configure a data source. The JDBC drivers in the list are not necessarily installed. Driver installation can include setting system Path, Classpath, and other environment variables. See Setting the Environment for a Type-4 Third-Party JDBC Driver.
Note: When a JDBC driver is updated, configuration requirements may change. The Create JDBC Data Source pages in the Administration Console use known configuration requirements at the time the WebLogic Server software was released. If configuration options for your JDBC driver have changed, you may need to manually override the configuration options when creating the data source or in the property pages for the data source after it is created.

General Data Source Options

JDBC data sources include options that determine the identity of the data source, way the data is handled on a database connection, and the way transactions are handled when a connection from the data source is used in a global transaction. You can view general options for a JDBC data source on the JDBC Data Source: Configuration: General page in the Administration Console. You can also access these options from the JDBCDataSourceParamsBean, which is a child of the JDBCDataSourceBean.

Selecting a JDBC Driver

When deciding which JDBC driver to use to connect to a database, you should try drivers from various vendors in your environment. In general, JDBC driver performance is dependent on many factors, especially the SQL code used in applications and the JDBC driver implementation.

For information about supported JDBC drivers, see “ Supported Database Configurations” in Supported Configurations for WebLogic Platform 9.2.

JDBC Data Source Names

JDBC data source names are used to identify the data source within the WebLogic domain. For system resource data sources, names must be unique among all other JDBC system resources, including data sources and multi data sources. To avoid naming conflicts, data source names should also be unique among other configuration object names, such as servers, clusters, and JMS queues, topics, and servers. For JDBC application modules scoped to an application, data source names must be unique among JDBC data sources and multi data sources that are similarly scoped.

Binding a Data Source to the JNDI Tree with Multiple Names

In WebLogic Server 9.0 and later releases, you can configure a data source so that it binds to the JNDI tree with multiple names. You can use a multi-JNDI-named data source in place of legacy configurations that included multiple data sources that pointed to a single JDBC connection pool.

To add JNDI names to an existing data source using the Administration Console, add names to the JNDI Name attribute with each JNDI name on a separate line. You must either restart the system after making your change or undeploy the data source before making the change, and then redeploy after making the change. Follow the instructions below.

  1. On the JDBC Data Source Arrow symbol Configuration Arrow symbol General page in the Administration Console, in JNDI Name, enter the names you want to use to bind the data source to the JDNI tree with each name on a separate line. For example:
  2. name1
    name2
    name3
  3. Click Save.

After you activate your changes, you will need to redeploy the data source or restart your server before the changes will take effect.

 


Transaction Options

When you configure a JDBC data source using the Administration Console, WebLogic Server automatically selects specific transaction options based on the type of JDBC driver:

Enabling Support for Global Transactions with a Non-XA JDBC Driver

If you use global transactions in your applications, you should use an XA JDBC driver to create database connections in the JDBC data source. If an XA driver is unavailable for your database, or you prefer not to use an XA driver, you should enable support for global transactions in the data source. You should also enable support for global transaction if your applications meet any of the following criteria:

With an EJB architecture, it is common for multiple EJBs that are doing database work to be invoked as part of a single transaction. Without XA, the only way for this to work is if all transaction participants use the exact same database connection. When you enable global transactions and select either Logging Last Resource or Emulate Two-Phase Commit, WebLogic Server internally uses the JTS driver to make sure all EJBs use the same database connection within the same transaction context without requiring you to explicitly pass the connection from EJB to EJB.

If multiple EJBs are participating in a transaction and you do not use an XA JDBC driver for database connections, configure a Data Source with the following options:

This configuration will force the JTS driver to internally use the same database connection for all database work within the same transaction.

With XA (requires an XA driver), EJBs can use a different database connection for each part of the transaction. WebLogic Server coordinates the transaction using the two-phase commit protocol, which guarantees that all or none of the transaction will be completed.

Understanding the Logging Last Resource Transaction Option

WebLogic Server supports the Logging Last Resource (LLR) transaction optimization through JDBC data sources. LLR is a performance enhancement option that enables one non-XA resource to participate in a global transaction with the same ACID guarantee as XA. LLR is a refinement of the “Last Agent Optimization.” It differs from Last Agent Optimization in that it is transactionally safe. The LLR resource uses a local transaction for its transaction work. The WebLogic Server transaction manager prepares all other resources in the transaction and then determines the commit decision for the global transaction based on the outcome of the LLR resource’s local transaction.

The LLR optimization improves performance by:

When a connection from a data source configured for LLR participates in a two-phase commit (2PC) global transaction, the WebLogic Server transaction manager completes the transaction by:

For a one-phase commit (1PC) global transaction, LLR eliminates the XA overhead by using a local transaction to complete the database operations, but no 2PC transaction record is written to the database.

The Logging Last Resource optimization maintains data integrity by writing the commit record on the LLR participant. If the transaction fails during the local transaction commit, the WebLogic Server transaction manager rolls back the transaction on all other transaction participants. For failure recovery, the WebLogic Server transaction manager reads the transaction log on the LLR resource along with other transaction log files in the default store and completes any transaction processing as necessary. Work associated with XA participants is committed if a commit record exists, otherwise their work is rolled back.

For instructions on how to create an LLR-enabled JDBC data source, see “ Create LLR-enabled JDBC data sources” in the Administration Console Online Help. For more details about the Logging Last Resource transaction processing, see “ Logging Last Resource Transaction Optimization” in Programming WebLogic JTA.

Advantages to Using the Logging Last Resource Optimization

Depending on your environment, you may want to consider the LLR transaction protocol in place of the two-phase commit protocol for transaction processing because of its performance benefits. The LLR transaction protocol offers the following advantages:

Note: The LLR optimization provides a significant increase in performance for insert, update, and delete operations. However, for read operations with LLR, performance is somewhat slower than read operations with XA. For best performance, you may want to configure a non-LLR JDBC data source for read-only operations.
Note: For more information about performance tuning with LLR, see “ Optimizing Performance with LLR” in Programming WebLogic JTA.

Enabling the Logging Last Resource Transaction Optimization

To enable the LLR transaction optimization, you create a JDBC data source with the Logging Last Resource transaction protocol, then use database connections from the data source in your applications. WebLogic Server automatically creates the required table on the database.

See “ Create LLR-enabled JDBC data sources” in the Administration Console Online Help.

Programming Considerations and Limitations for LLR Data Sources

You use JDBC connections from an LLR-enabled data source in an application as you would use JDBC connections from any other data source: after beginning a transaction, you look up the data source on the JNDI tree, then request a connection from the data source. However, with the LLR optimization, WebLogic Server internally manages the connection request and handles the transaction processing differently than in an XA transaction. For more information about how Logging Last Resource works, see “ Logging Last Resource Transaction Optimization” in Programming WebLogic JTA.

Note the following:

Administrative Considerations and Limitations for LLR Data Sources

Consider the following requirements and limitations when configuring an LLR-enabled JDBC data source. For more information about how Logging Last Resource works, see “ Logging Last Resource Transaction Optimization” in Programming WebLogic JTA.

Understanding the Emulate Two-Phase Commit Transaction Option

If you need to support distributed transactions with a JDBC data source, but there is no available XA-compliant driver for your DBMS, you can select the Emulate Two-Phase Commit for non-XA Driver option for a data source to emulate two-phase commit for the transactions in which connections from the data source participate. This option is an advanced option on the JDBC Data SourceArrow symbolConfiguration Arrow symbol General tab.

When the Emulate Two-Phase Commit for non-XA Driver option is selected (EnableTwoPhaseCommit is set to true), the non-XA JDBC resource always returns XA_OK during the XAResource.prepare() method call. The resource attempts to commit or roll back its local transaction in response to subsequent XAResource.commit() or XAResource.rollback() calls. If the resource commit or rollback fails, a heuristic error results. Application data may be left in an inconsistent state as a result of a heuristic failure.

When the Emulate Two-Phase Commit for non-XA Driver option is not selected in the Console (EnableTwoPhaseCommit is set to false), the non-XA JDBC resource causes XAResource.prepare() to fail. When there is only one resource participating in a transaction, the one phase optimization bypasses XAResource.prepare(), and the transaction commits successfully in most instances.

Note: There are risks to data integrity when using the Emulate Two-Phase Commit for non-XA Driver option. BEA recommends that you use an XA-compliant JDBC driver or the Logging Last Resource option rather than use the Emulate Two-Phase Commit option. Make sure you consider the risks below before enabling this option.

This non-XA JDBC driver support is often referred to as the "JTS driver" because WebLogic Server uses the WebLogic JTS Driver internally to support the feature. For more information about the WebLogic JTS Driver, see " Using the WebLogic JTS Driver" in Programming WebLogic JDBC.

Limitations and Risks When Emulating Two-Phase Commit Using a Non-XA Driver

WebLogic Server supports the participation of non-XA JDBC resources in global transactions with the Emulate Two-Phase Commit data source transaction option, but there are limitations that you must consider when designing applications to use such resources. Because a non-XA driver does not adhere to the XA/2PC contracts and only supports one-phase commit and rollback operations, WebLogic Server (through the JTS driver) has to make compromises to allow the resource to participate in a transaction controlled by the Transaction Manager.

Consider the following limitations and risks before using the Emulate Two-Phase Commit for non-XA Driver option.

Heuristic Completions and Data Inconsistency

When Emulate Two-Phase Commit is selected for a non-XA resource, (enableTwoPhaseCommit = true), the prepare phase of the transaction for the non-XA resource always succeeds. Therefore, the non-XA resource does not truly participate in the two-phase commit (2PC) protocol and is susceptible to failures. If a failure occurs in the non-XA resource after the prepare phase, the non-XA resource is likely to roll back the transaction while XA transaction participants will commit the transaction, resulting in a heuristic completion and data inconsistencies.

Because of the data integrity risks, the Emulate Two-Phase Commit option should only be used in applications that can tolerate heuristic conditions.

Cannot Recover Pending Transactions

Because a non-XA driver manipulates local database transactions only, there is no concept of a transaction pending state in the database with regard to an external transaction manager. When XAResource.recover() is called on the non-XA resource, it always returns an empty set of Xids (transaction IDs), even though there may be transactions that need to be committed or rolled back. Therefore, applications that use a non-XA resource in a global transaction cannot recover from a system failure and maintain data integrity.

Possible Performance Loss with Non-XA Resources in Multi-Server Configurations

Because WebLogic Server relies on the database local transaction associated with a particular JDBC connection to support non-XA resource participation in a global transaction, when the same JDBC data source is accessed by an application with a global transaction context on multiple WebLogic Server instances, the JTS driver will always route JDBC operations to the first connection established by the application in the transaction. For example, if an application starts a transaction on one server, accesses a non-XA JDBC resource, then makes a remote method invocation (RMI) call to another server and accesses a data source that uses the same underlying JDBC driver, the JTS driver recognizes that the resource has a connection associated with the transaction on another server and sets up an RMI redirection to the actual connection on the first server. All operations on the connection are made on the one connection that was established on the first server. This behavior can result in a performance loss due to the overhead associated with setting up these remote connections and making the RMI calls to the one physical connection.

Only One Non-XA Participant

When a non-XA resource (with Emulate Two-Phase Commit selected) is registered with the WebLogic Server Transaction Manager, it is registered with the name of the class that implements the XAResource interface. Since all non-XA resources with Emulate Two-Phase Commit selected use the JTS driver for the XAResource interface, all non-XA resources (with Emulate Two-Phase Commit selected) that participate in a global transaction are registered with the same name. If you use more than one non-XA resource in a global transaction, you will see naming conflicts or possible heuristic failures.

 


Connection Pool Features

Each JDBC data source has a pool of JDBC connections that are created when the data source is deployed or at server startup. Applications use a connection from the pool then return it when finished using the connection. Connection pooling enhances performance by eliminating the costly task of creating database connections for the application.

The following sections include information about connection pool options for a JDBC data source.

You can see more information and set these and other related options through the:

Selecting a JDBC Driver

When creating a JDBC data source using the Administration Console, you are prompted to select a JDBC driver. The Administration Console provides the driver class name and helps you construct the URL as required by the driver. The driver you select must be in the classpath on all servers on which you intend to deploy the data source. Some but not all JDBC drivers listed in the Administration Console are shipped with WebLogic Server:

All of these drivers are referenced by the weblogic.jar manifest file and do not need to be explicitly defined in a server’s classpath.

Enabling JDBC Driver-Level Features

WebLogic JDBC data sources support the javax.sql.ConnectionPoolDataSource interface implemented by JDBC drivers. You can enable driver-level features adding the property and its value to the Properties attribute in a JDBC data source. Driver-level properties in the Properties attribute are set on the driver's ConnectionPoolDataSource object.

Initializing Database Connections with SQL Code

When WebLogic Server creates database connections in a data source, the server can automatically run SQL code to initialize the database connection. To enable this feature, enter SQL followed by a space and the SQL code you want to run in the Init SQL attribute on the JDBC Data Source: Configuration: Connection Pool page in the Administration Console. If you leave this attribute blank (the default), WebLogic Server does not run any code to initialize database connections.

WebLogic Server runs this code whenever it creates a database connection for the data source, which includes at server startup, when expanding the connection pool, and when refreshing a 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: Init SQL is not a dynamic attribute. When you change the value for Init SQL, you must either undeploy and redeploy the data source or restart the server.

 


Setting Database Security Credentials

The following sections provide information on how to pass security credentials to a DBMS:

Types of Data Source Pools

Weblogic Server provides two types of data source pools based on security privileges:

This section compares methods of passing security credentials to a DBMS.

Table 3-1 Comparing Methods of Passing Security Credentials
Method
Type of Connection Pool
Homogeneous pool of connections.
Homogeneous pool of connections.
Heterogeneous pool of connections.

Using a User Name/Password

The simplest type of credential is to provide the connection pool the user account name and password of the DMS. All the connections in the pool use the same credentials to access a DBMS. See Create JDBC data sources.

Note: You can enter the password as a name-value pair in the Properties field (not permitted for production environments) or you can enter it in the Password field. The value in the Password field overrides any password value defined in the Properties passed to the 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 Password value is encrypted in the configuration file (stored as the password-encrypted attribute in the jdbc-driver-params tag in the module file) and is hidden in the administration console.

Set Client ID On Connection

If the Enable Credential Mapping attribute is enabled on the data source, when an application requests a database connection from the data source, the WebLogic Server instance determines the current WebLogic user ID and then sets the mapped database ID as a light-weight client ID. All the connections in the pool have the same credentials to access a DBMS. Basic configuration steps are:

  1. Select Enable Credential Mapping, see “ Enable Credential Mapping for a JDBC data source” in Administration Console Online Help.
  2. Map the WebLogic user ID and the database ID. See Configure credential mapping for a JDBC data source.

This feature relies on features in the JDBC driver and DBMS. It is only supported for use with Oracle and DB2 databases using a vendor extension method:

Note: Enable Credential Mapping and Enable Identity Based Connection Pooling are mutually exclusive. If you think you need both mechanisms to pass security credentials in your application environment, create separate data sources—one for with Set Client ID On Connection and one with Enable Identity Based Connection Pooling.

Identity-based Connection Pooling

Identity-based connection pooling allows applications to use a JDBC connection with a specific DBMS credential by pooling physical connections with different DBMS credentials.

If the Enable Identity Based Connection Pooling attribute is enabled on the data source, when an application requests a database connection, the WebLogic Server instance selects an existing physical connection or creates a new physical connection with requested DBMS identity based on a map of WebLogic user credentials and DBMS credentials. Basic configuration steps are:

  1. Select Enable Identity Based Connection Pooling, see “ Enable identity-based connection pooling for a JDBC data source” in Administration Console Online Help.
  2. Map WebLogic user credentials and DBMS credentials. See Configure credential mapping for a JDBC data source.
Note: Enable Credential Mapping and Enable Identity Based Connection Pooling are mutually exclusive. If you think you need both mechanisms to pass security credentials in your application environment, create separate data sources—one for with Enable Credential Mapping and one with Enable Identity Based Connection Pooling.
How Heterogeneous Connections are Created

The following section provides information on how heterogeneous connections are created:

  1. At connection pool initialization, the physical JDBC connections are created with the default DBMS credential of the data source.
  2. An application tries to get a connection from a data source.
  3. The current server instance credential is mapped to a DBMS credential. See “ Configure credential mapping for a JDBC data source” in the Administration Console Online Help.

Regardless of how physical connections are created, each physical connection in the pool has its own DBMS credential information maintained by the pool. Once a physical connection is reserved by the pool, it does not change its DBMS credential even if the current thread changes its WebLogic user credential and continues to use the same connection.

Using Identity-based Pooling with Global Transactions

When executing inside a global transaction, an application may change the credential on the current thread and get multiple JDBC connections under different credentials. However, the Identity-based Pooling feature maps multiple logical JDBC connections of a WebLogic JDBC data source inside of a global transaction into a single physical JDBC connection. This means that only one DBMS credential per WebLogic JDBC data source per WebLogic server instance is honored for a global transaction.

Using Identity-based Pooling with LLR

You must make the following changes to use Logging Last Resource (LLR) transaction optimization with Identity-based Pooling:

 


Tuning Data Source Connection Pool Options

By properly configuring the connection pool attributes in JDBC data sources in your WebLogic Server domain, you can improve application and system performance. The following sections include information about tuning options for the connection pool in a JDBC data source:

Increasing Performance with the Statement Cache

When you use a prepared statement or callable statement in an application or EJB, there is considerable processing overhead for the communication between the application server and the database server and on the database server itself. To minimize the processing costs, WebLogic Server can cache prepared and callable statements used in your applications. When an application or EJB calls any of the statements stored in the cache, WebLogic Server reuses the statement stored in the cache. Reusing prepared and callable statements reduces CPU usage on the database server, improving performance for the current statement and leaving CPU cycles for other tasks.

Each connection in a data source has its own individual cache of prepared and callable statements used on the connection. However, you configure statement cache options per data source. That is, the statement cache for each connection in a data source uses the statement cache options specified for the data source, but each connection caches it’s own statements. Statement cache configuration options include:

You can use the Administration Console to set statement cache options for a data source. See “ Configure the statement cache for a JDBC data source” in the Administration Console Online Help.

Statement Cache Algorithms

The Statement Cache Type (or algorithm) determines which prepared and callable statements to store in the cache for each connection in a data source. You can choose from the following options:

LRU (Least Recently Used)

When you select LRU (Least Recently Used, the default) as the Statement Cache Type, WebLogic Server caches prepared and callable statements used on the connection until the statement cache size is reached. When an application calls Connection.prepareStatement(), WebLogic Server checks to see if the statement is stored in the statement cache. If so, WebLogic Server returns the cached statement (if it is not already being used). If the statement is not in the cache, and the cache is full (number of statements in the cache = statement cache size), Weblogic Server determines which existing statement in the cache was the least recently used and replaces that statement in the cache with the new statement.

The LRU statement cache algorithm in WebLogic Server uses an approximate LRU scheme.

Fixed

When you select FIXED as the Statement Cache Type, WebLogic Server caches prepared and callable statements used on the connection until the statement cache size is reached. When additional statements are used, they are not cached.

With this statement cache algorithm, you can inadvertently cache statements that are rarely used. In many cases, the LRU algorithm is preferred because rarely used statements will eventually be replaced in the cache with frequently used statements.

Statement Cache Size

The Statement Cache Size attribute determines the total number of prepared and callable statements to cache for each connection in each instance of the data source. By caching statements, you can increase your system performance. However, you must consider how your DBMS handles open prepared and callable statements. In many cases, the DBMS will maintain a cursor for each open statement. This applies to prepared and callable statements in the statement cache. If you cache too many statements, you may exceed the limit of open cursors on your database server.

For example, if you have a data source with 10 connections deployed on 2 servers, if you set the Statement Cache Size to 10 (the default), you may open 200 (10 x 2 x 10) cursors on your database server for the cached statements.

Usage Restrictions for the Statement Cache

Using the statement cache can dramatically increase performance, but you must consider its limitations before you decide to use it. Please note the following restrictions when using the statement cache.

There may be other issues related to caching statements that are not listed here. If you see errors in your system related to prepared or callable statements, you should set the statement cache size to 0, which turns off statement caching, to test if the problem is caused by caching prepared statements.

Calling a Stored Statement After a Database Change May Cause Errors

Prepared statements stored in the cache refer to specific database objects at the time the prepared statement is cached. If you perform any DDL (data definition language) operations on database objects referenced in prepared statements stored in the cache, the statements may fail the next time you run them. For example, if you cache a statement such as select * from emp and then drop and recreate the emp table, the next time you run the cached statement, the statement may fail because the exact emp table that existed when the statement was prepared, no longer exists.

Likewise, prepared statements are bound to the data type for each column in a table in the database at the time the prepared statement is cached. If you add, delete, or rearrange columns in a table, prepared statements stored in the cache are likely to fail when run again.

These limitations depend on the behavior of your DBMS.

Using setNull In a Prepared Statement

If you cache a prepared statement that uses a setNull bind variable, you must set the variable to the proper data type. If you use a generic data type, as in the following example, data may be truncated or the statement may fail when it runs with a value other than null.

   java.sql.Types.Long sal=null
   .
   .
   .
   if (sal == null)
      setNull(2,int)//This is incorrect
   else
      setLong(2,sal)

Instead, use the following:

   if (sal == null)
      setNull(2,long)//This is correct
   else
      setLong(2,sal)
Statements in the Cache May Reserve Database Cursors

When WebLogic Server caches a prepared or callable statement, the statement may open a cursor in the database. If you cache too many statements, you may exceed the limit of open cursors for a connection. To avoid exceeding the limit of open cursors for a connection, you can change the limit in your database management system or you can reduce the statement cache size for the data source.

Connection Testing Options for a Data Source

To make sure that the database connections in a data source remain healthy, you should periodically test the connections. WebLogic Server includes two basic types of testing:

The following section discusses automatic connection testing options. For more information about manual connection testing, see Testing Data Sources and Database Connections.

To configure automatic testing options for a data source, you set the following options either through the Administration Console or through WLST using the JDBCConnectionPoolParamsBean:

See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console or see “ JDBCConnectionPoolParamsBean” in the WebLogic Server MBean Reference for more details about these options.

For instructions to set connection testing options, see “ Configure testing options for a JDBC data source” in the Administration Console Online Help.

Database Connection Testing Semantics

When WebLogic Server tests database connections in a datasource, it reserves a connection from the datasource, runs a small query on the connection, then returns the connection to the pool in the data source. The query used in testing is determined by the value in Test Table Name. If the value is a table name, the query is select 1 from table_name. If Test Table Name includes a full query starting with SQL followed by space and the query, WebLogic Server uses that query when testing database connections.

If a connection fails the test, WebLogic Server closes and recreates the connection, and then tests the new connection.

Details about the semantics of connection testing is discussed in the following sections:

Connection Testing When Database Connections are Created

When connections are created in a data source, WebLogic Server tests each connection using the query defined by the value in Test Table Name. Connections are created when a data source is deployed, either at server startup or when creating a data source, when increasing capacity to meet demand for connections, or when recreating a connection that failed a connection test.

The purpose of this testing is to ensure that new connections are viable and ready for use when an application requests a connection.

Periodic Connection Testing

If Test Frequency is greater than 0, WebLogic Server periodically tests database connections that are not in use in the data source. The test is based on the query defined in Test Table Name. If a connection fails the test, WebLogic Server closes the connection, recreates the connection, and tests the new connection before returning it to the pool.

Testing Reserved Connections

When Test Connections On Reserve is enabled, when your application requests a connection from the data source, WebLogic Server tests the connection using the query specified in Test Table Name before giving the connection to the application.

Testing reserved connections can cause a delay in satisfying connection requests, but it makes sure that the connection is viable when the application gets the connection. You can minimize the impact of testing reserved connections by tuning Seconds to Trust an Idle Pool Connection. See Minimizing Connection Request Delay with Seconds to Trust an Idle Pool Connection

Minimized 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 a data source typically become defunct. If the data source is configured to test connections on reserve, 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.

To minimize this delay, WebLogic data sources include logic that considers all connections in the data source as dead after a number of consecutive test failures, and closes all connections in the data source. After all connections are closed, when an application requests a connection, the data source creates a connection without first having to test a dead connection. This behavior minimizes the delay for connection requests following the data source’s connection pool flush.

WebLogic Server determines the number of test failures before closing all connections based on the Test Frequency setting for the data source:

Minimized Connection Request Delay After Connection Test Failures

If your DBMS becomes and remains unavailable, the data source will persistently test and try to replace dead connections while trying to satisfy connection requests. This behavior is beneficial because it enables the data source 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 this delay, the WebLogic data sources include logic that disables the data source after 2 consecutive failures to replace a dead connection. When an application requests a connection from a disabled data source, WebLogic Server throws a PoolDisabledSQLException immediately to notify the client that a connection is not available.

For data sources that are disabled in this manner, WebLogic Server periodically runs a refresh process. The refresh process does the following:

You can also manually enable the data source using the Administration Console.

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

Database connection testing during heavy traffic can reduce application performance. To minimize the impact of connection testing, you can set the Seconds To Trust An Idle Pool Connection attribute in the JDBC data source configuration to trust recently-used or recently-tested database connections and skip the connection test.

If Test Reserved Connections is enabled on your data source, 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 Seconds to Trust an Idle Pool Connection since the connection was tested or successfully used and returned to the data source, WebLogic Server skips the connection test before delivering it to an application.

If Test Frequency is greater than 0 for your data source (periodic testing is enabled), WebLogic Server also skips the connection test if the connection was successfully used and returned to the data source within the time specified for Seconds to Trust an Idle Pool Connection.

For instructions to set Seconds to Trust an Idle Pool Connection, see “ Configure testing options for a JDBC data source” in the Administration Console Online Help.

Seconds to Trust an Idle Pool Connection 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.

Database Connection Testing Configuration Recommendations

You should set connection testing attributes so that they best fit your environment. For example, if your application cannot tolerate database connection failures, you should set Seconds to Trust an Idle Pool Connection to 0 and make sure Test Reserved Connections is enabled so that WebLogic Server will test every connection before giving it to an application. If your application is more sensitive delays in getting a connection from the data source and can tolerate an occasional connection failure, you should set Seconds to Trust an Idle Pool Connection to a higher number, set Test Frequency to a low number, and enable Test Reserved Connections. With these settings, your application would rely more on testing connections in the pool when they are not in use rather than when an application requests a connection.

Default Test Table Name

When you create a data source using the Administration Console, the Administration Console automatically sets the Test Table Name attribute for a data source based on the DBMS that you select. The Test Table Name attribute is used in connection testing which is optionally performed periodically or when you create or reserve a connection, depending on how you configure the testing options. For database tests to succeed, the database user used to create database connections in the data source must have access to the database table. If not, you should either grant access to the user (make this change in the DBMS) or change the Test Table Name attribute to the name of a table to which the user does have access (make this change in the WebLogic Server Administration Console).

Table 3-2 Default Test Table Name by DBMS
DBMS
Default Test Table Name (Query)
Adabas for z/OS
SQL call shadow_adabas('select * from employees')
Cloudscape
SQL SELECT 1
DB2
SQL SELECT COUNT(*) FROM SYSIBM.SYSTABLES
FirstSQL
SQL SELECT 1
IMS/TM for z/OS
SQL call shadow_ims('otm','/dis','cctl')
Informix
SQL SELECT COUNT(*) FROM SYSTABLES
Microsoft SQL Server
SQL SELECT 1
MySQL
SQL SELECT 1
Oracle
SQL SELECT 1 FROM DUAL
PointBase
SQL SELECT COUNT(*) FROM SYSTABLES
PostgreSQL
SQL SELECT 1
Progress
SQL SELECT COUNT(*) FROM SYSTABLES
Sybase
SQL SELECT 1

Enabling Connection Creation Retries

WebLogic JDBC data sources include the Connection Creation Retry Frequency option (ConnectionCreationRetryFrequencySeconds in the JDBCConnectionPoolParamsBean) that you can use to specify the number of seconds between attempts to establish connections to the database. If set and if the database is unavailable when the data source is created, WebLogic Server attempts 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. This option applies to connections created when the data source is created at server startup or when the data source is deployed or if the initial capacity is increased. It does not apply to connections created for pool expansion or to replace a defunct connection in the pool.

By default, Connection Creation Retry Frequency is 0 seconds. When the value is set to 0, connection creation retries is disabled and data source creation fails if the database is unavailable.

See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console or see “ JDBCConnectionPoolParamsBean” in the WebLogic Server MBean Reference for more details about this option.

Enabling Connection Requests to Wait for a Connection

JDBC data sources have two attributes that you can set to enable connection requests to wait for a connection from a data source: Connection Reserve Timeout (ConnectionReserveTimeoutSeconds) and Maximum Waiting for Connection (HighestNumWaiters). You use these two attributes together to enable connection requests to wait for a connection without disabling your system by blocking too many threads.

See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console or see “ JDBCConnectionPoolParamsBean” in the WebLogic Server MBean Reference for more details about these options.

Also see “ Enable connection requests to wait for a connection” in the Administration Console Online Help.

Connection Reserve Timeout

When an application requests a connection from a data source, if all connections in the data source are in use and if the data source has expanded to its maximum capacity, the application will get a Connection Unavailable SQL Exception. To avoid this, you can configure the 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 timeout immediately if there is no connection available. If you set Connection Reserve Timeout to 0, a connection request will wait indefinitely. The default value is 10 seconds.

See “ Enable connection requests to wait for a connection” in the Administration Console Online Help.

Limiting the Number of Waiting Connection Requests

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 Maximum Waiting for Connection (HighestNumWaiters) attribute, which limits the number connection requests that can concurrently wait for a connection.

If you set Maximum Waiting for Connection (HighestNumWaiters) to MAX-INT (the default), there is effectively no bound on how many connection requests can wait for a connection. If you set Maximum Waiting for Connection to 0, connection requests cannot wait for a connection. If the maximum number of requests has been met, a SQLException is thrown when an application requests a connection.

Automatically Recovering Leaked Connections

A leaked connection is a connection that was not properly returned to the connection pool in the data source. To automatically recover leaked connections, you can specify a value for Inactive Connection Timeout on the JDBC Data Source: Configuration: Connection Pool page in the Administration Console. When you set a value for Inactive Connection Timeout, WebLogic Server will forcibly return a connection to the data source when there is no activity on a reserved connection for the number of seconds that you specify. When set to 0 (the default value), this feature is turned off.

See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console or see “ JDBCConnectionPoolParamsBean” in the WebLogic Server MBean Reference for more details about this option.

Note that the actual timeout could exceed the configured value for Inactive Connection Timeout. The internal data source maintenance thread runs every 5 seconds. When it reaches the Inactive Connection Timeout (for example 30 seconds), it checks for inactive connections. To avoid timing out a connection that was reserved just before the current check or just after the previous check, the server gives an inactive connection a “second chance.” On the next check, if the connection is still inactive, the server times it out and forcibly returns it to the data source. On average, there could be a delay of 50% more than the configured value.

Avoiding Server Lockup with the Correct Number of Connections

When your applications attempt to get a connection from a data source in which there are no available connections, the data source throws an exception stating that a connection is not available in the data source. To avoid this error, make sure your data source can expand to the size required to accommodate your peak load of connection requests. To increase the maximum number of connections available in the data source, increase the value for Maximum Capacity for the data source on the JDBC Data Source: Configuration: Connection Pool page in the Administration Console.

Limiting Statement Processing Time with Statement Timeout

With the Statement Timeout option on a JDBC data source, you can limit the amount of time that a statement takes to execute on a database connection reserved from the data source. When you set a value for Statement Timeout, WebLogic Server passes the time specified to the JDBC driver using the java.sql.Statement.setQueryTimeout() method. If your JDBC driver does not support this method, it may throw an exception and the timeout value is ignored.

When Statement Timeout is set to -1, (the default) statements do not timeout.

See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console or see “ JDBCConnectionPoolParamsBean” in the WebLogic Server MBean Reference for more details about this option.

Using Pinned-To-Thread Property to Increase Performance

To minimize the time it takes for an application to reserve a database connection from a data source and to eliminate contention between threads for a database connection, you can add the Pinned-To-Thread property in the connection Properties list for the data source, and set its value to true.

When Pinned-To-Thread is enabled, WebLogic Server pins a database connection from the data source to an execution thread the first time an application uses the thread to reserve a connection. When the application finishes using the connection and calls connection.close(), which otherwise returns the connection to the data source, WebLogic Server keeps the connection with the execute thread and does not return it to the data source. When an application subsequently requests a connection using the same execute thread, WebLogic Server provides the connection already reserved by the thread. There is no locking contention on the data source that occurs when multiple threads attempt to reserve a connection at the same time and there is no contention for threads that attempt to reserve the same connection from a limited number of database connections.

Note: In this release, the Pinned-To-Thread feature does not work with multi data sources, Oracle RAC, and IdentityPool. These features rely on the ability to return a connection to the connection pool and reacquire it if there is a connection failure or connection identity does not match.

See JDBC Data Source: Configuration: Connection Pool in the Administration Console Online Help.

Changes to Connection Pool Administration Operations When PinnedToThread is Enabled

Because the nature of connection pooling behavior is changed when PinnedToThread is enabled, some connection pool attributes or features behave differently or are disabled to suit the behavior change:

Additional Database Resource Costs When PinnedToThread is Enabled

When PinnedToThread is enabled, the maximum capacity of the connection pool (maximum number of database connections created in the connection pool) becomes the number of execute threads used to request a connection multiplied by the number of concurrent connections each thread reserves. This may exceed the Maximum Capacity specified for the connection pool. You may need to consider this larger number of connections in your system design and ensure that your database allows for additional associated resources, such as open cursors.

Also note that connections are never returned to the connection pool, which means that the connection pool can never shrink to reduce the number of connections and associated resources in use. You can minimize this cost by setting an additional driver parameter onePinnedConnectionOnly. When onePinnedConnectionOnly=true, only the first connection requested is pinned to the thread. Any additional connections required by the thread are taken from and returned to the connection pool as needed. Set onePinnedConnectionOnly using the Properties attribute, for example:

    Properties="PinnedToThread=true;onePinnedConnectionOnly=true;user=examples"

If your system can handle the additional resource requirements, BEA recommends that you use the PinnedToThread option to increase performance.

If your system cannot handle the additional resource requirements or if you see database resource errors after enabling PinnedToThread, BEA recommends not using PinnedToThread.

 


Deploying Data Sources on Servers and Clusters

To deploy a data source to a cluster or server, you select the server or cluster as a deployment target. When a data source is deployed on a server, WebLogic Server creates an instance of the data source on the server, including the pool of database connections in the data source. When you deploy a data source to a cluster, WebLogic Server creates an instance of the data source on each server in the cluster.

For instructions, see “ Target JDBC data sources” in the Administration Console Online Help.

 


Minimizing Server Startup Hang Caused By an Unresponsive Database

On server startup, WebLogic Server attempts to create database connections in the data sources deployed on the server. If a database is unreachable, server startup may hang in the STANDBY state for a long period of time. This is due to WebLogic Server threads that hang inside the JDBC driver code waiting for a reply from the database server. The duration of the hang depends on the JDBC driver and the TCP/IP timeout setting on the WebLogic Server machine.

To work around this issue, WebLogic Server includes the JDBCLoginTimeoutSeconds attribute on the ServerMBean. When you set a value for this attribute, the value is passed into java.sql.DriverManager.setLoginTimeout(). If the JDBC driver being used to create database connections implements the setLoginTimeout method, attempts to create database connections will wait only as long as the timeout specified.

 


Security for JDBC Data Sources

The following sections provide information on how WebLogic Server uses roles and policies to secure JDBC data sources:

Setting Security Policies for JDBC Resources

You can optionally restrict access to JDBC data sources. In WebLogic Server, security policies answer the question “who has access” to a WebLogic resource. A security policy is created when you define an association between a WebLogic resource and a user, group, or role. A WebLogic resource has no protection until you assign it a security policy. For instructions on how to set up security for all WebLogic Server resources, see “Use roles and policies to secure resources” in Administration Console Online Help. For more information about securing server resources, see Securing WebLogic Resources.

You can protect JDBC operations by assigning Administrator methods which can limit the actions that an administrator may take upon a JDBC data source. See “Java DataBase Connectivity (JDBC) Resources” in Securing WebLogic Resources Using Roles and Policies.

Security Roles for JDBC MBeans

JDBC MBeans allow only the Admin and Deployer roles. The following section provides information on the security roles defined for JDBC MBeans:

See “Default Security Policies for MBeans” in WebLogic Server MBean Reference for information on default security settings for WebLogic Server.

JDBC Domain Configuration MBeans

The following domain configuration JDBC MBeans that have settings that override the default security settings.

See “Domain Configuration MBeans” in WebLogic Server MBean Reference.

JDBC System Module MBeans

The following system module JDBC MBeans that have settings that override the default security settings.

See “System Module MBeans” in WebLogic Server MBean Reference.

 


JDBC Data Source Factories (Deprecated)

In previous releases of WebLogic Server, application-scoped JDBC connection pools relied on JDBC data source factories to provide default connection pool values. JDBC data source factories are deprecated in WebLogic Server 9.2 and are included in the release for backward compatibility only. Application-scoped JDBC connection pools are replaced by JDBC application modules. For more information, see Application Scoping for a Packaged JDBC Module.


  Back to Top       Previous  Next