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

Types of WebLogic Server JDBC Data Sources

WebLogic Server provides three types of data sources:

  • Generic Data Sources—Generic data sources and their connection pools provide connection management processes that help keep your system running efficiently.You can set options in the data source to suit your applications and your environment.

  • GridLink Data Sources—An event-based data source that adaptively responds to state changes in an Oracle RAC instance. See Using GridLink Data Sources.

  • Multi data sources—A multi data source is an abstraction around a group of generic data sources that provides load balancing or failover processing. See Configuring JDBC Multi Data Sources.

Creating a JDBC Data Source

You can create JDBC data sources in your WebLogic domain using the Administration Console or the WebLogic Scripting Tool (WLST):

  • "Create a JDBC Data Source" in the Oracle WebLogic Server Administration Console Help.

  • The sample WLST script SAMPLES_HOME\server\examples\src\examples\wlst\online\jdbc_data_source_creation.py, where SAMPLES_HOME refers to the main examples directory of your WebLogic Server installation. See "WLST Online Sample Scripts" in Oracle WebLogic Scripting Tool

The following sections provide an overview of the basics steps used in the data source configuration wizard to create a data source using the Administration console:

JDBC Data Source Properties

JDBC Data Source Properties include options that determine the identity of the data source and the way the data is handled on a database connection.

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.

JNDI Names

You can configure a data source so that it binds to the JNDI tree with a single or 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. For more information, see rss

Selecting a Database Type

Select a DBMS. For information about supported databases, see "Supported Database Configurations" in System Requirements and Supported Platforms for Oracle WebLogic Server at http://www.oracle.com/technology/software/products/ias/files/fusion_certification.html

Selecting a JDBC Driver

When creating a JDBC data source using the Administration Console, you are prompted to select a JDBC driver class. The Administration Console provides most of the more common driver class names and in most cases tries to help you construct the URL as required by the driver. You should verify, however, that the URL is as you want it before asking the console to test it. 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 (and/or are already in the classpath) 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.

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 System Requirements and Supported Platforms for Oracle WebLogic Server at http://www.oracle.com/technology/software/products/ias/files/fusion_certification.html.

Note:

JDBC drivers listed in the Administration Console when creating a data source are not necessarily certified for use with WebLogic Server. JDBC drivers are listed as a convenience to help you create connections to many of the database management systems available.

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 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.When a JDBC driver is updated, configuration requirements may change. The Administration Console uses 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.

Configure 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:

  • For XA drivers, the system automatically selects the Two-Phase Commit protocol for global transaction processing.

  • For non-XA drivers, local transactions are supported by definition, and WebLogic Server offers the following options

    Supports Global Transactions: (selected by default) Select this option if you want to use connections from the data source in global transactions, even though you have not selected an XA driver. See Enabling Support for Global Transactions with a Non-XA JDBC Driver for more information.

    When you select Supports Global Transactions, you must also select the protocol for WebLogic Server to use for the transaction branch when processing a global transaction:

    • Logging Last Resource: With this option, the transaction branch in which the connection is used is processed as the last resource in the transaction and is processed as a local transaction. Commit records for two-phase commit (2PC) transactions are inserted in a table on the resource itself, and the result determines the success or failure of the prepare phase of the global transaction. This option offers some performance benefits and greater data safety than Emulate Two-Phase Commit, but it has some limitations. See Understanding the Logging Last Resource Transaction Option.

      Note:

      Logging Last Resource is not supported for data sources used by a multi data source except when used with Oracle RAC version 10G Release 2 (10GR2) and greater versions as described in Administrative Considerations and Limitations for LLR Data Sources..
    • Emulate Two-Phase Commit: With this option, the transaction branch in which the connection is used always returns success for the prepare phase of the transaction. It offers performance benefits, but also has risks to data in some failure conditions. Select this option only if your application can tolerate heuristic conditions. See Understanding the Emulate Two-Phase Commit Transaction Option.

    • One-Phase Commit: (selected by default) With this option, a connection from the data source can be the only participant in the global transaction and the transaction is completed using a one-phase commit optimization. If more than one resource participates in the transaction, an exception is thrown when the transaction manager calls XAResource.prepare on the 1PC resource.

For more information on configuring transaction support for a data source, see JDBC Data Source Transaction Options.

Configure Connection Properties

Connection Properties are used to configure the connection between the data source and the DBMS. Typical attributes are the database name, host name, port number, user name, and password.

Note:

You can use a Single Client Access Name (SCAN) address to represent the host name.

Test Connections

Test Database Connection allows you to test a database connection before the data source configuration is finalized using a table name or SQL statement. If necessary, you can test additional configuration information using the Properties and System Properties attributes.

Target the Data Source

You can select one or more targets to deploy your new JDBC data source. If you don't select a target, the data source will be created but not deployed. You will need to deploy the data source at a later time.

Configuring 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:

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

Enabling Connection-based System Properties

WebLogic JDBC data sources support setting driver properties using the value of system properties. The value of each property is derived at runtime from the named system property. You can configure connection-based system properties using the Administration Console by editing the System Properties attribute of your data source configuration.

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.

Configuring Oracle Parameters

WebLogic Server provides several attributes that provide improved Data Source performance when using Oracle drivers, for more information, see Advanced Configuration for Oracle Drivers.

Configuring an ONS Client

Configuring an ONS client changes a generic data source to a GridLink data source. For more detailed configuration information and additional environment requirements, see Using GridLink Data Sources.

Tuning Generic Data Source Connection Pools

By properly configuring the connection pool attributes in JDBC data sources in your WebLogic Server domain, you can improve application and system performance. For more information, see Tuning Data Source Connection Pools.

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:

  • Homogeneous—Regardless of the end user of the application, all connections in the pool use the same security credentials to access the DBMS.

  • Heterogeneous—Allows applications to use a JDBC connection with a specific DBMS credential by pooling physical connections with different DBMS credentials.

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

Using a User Name/Password

Homogeneous pool of connections.

Set Client ID On Connection

Homogeneous pool of connections.

Identity-based Connection Pooling

Heterogeneous pool of connections.


Using a User Name/Password

The simplest type of credential is to provide the connection pool a user account name and password for the DBMS. All the connections in the pool then use the same credentials to access a DBMS. See "Create JDBC data sources" in Oracle WebLogic Server Administration Console Help.

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. Oracle 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 Set Client ID On Connection 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 Set Client ID On Connection, see "Enable Set Client ID On Connection for a JDBC data source" in Oracle WebLogic Server Administration Console Help.

    Note:

    Credential mapping to map the WebLogic user ID and the database ID is only supported on the Oracle database with the Oracle Thin driver. This feature is not supported with the Oracle DMS driver.
  2. Map the WebLogic user ID and the database ID. See "Configure credential mapping for a JDBC data source" in the Oracle WebLogic Server Administration Console Help.

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:

  • oracle.jdbc.OracleConnection.setClientIdentifier(String id)

  • com.ibm.db2.jcc.DB2Connection.setDB2ClientUser(String user)

    Note:

    Set Client ID On Connection 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 Oracle WebLogic Server Administration Console Help.

  2. Map WebLogic user credentials and DBMS credentials. See "Configure credential mapping for a JDBC data source" in the Oracle WebLogic Server Administration Console Help.

    Note:

    Set Client ID On Connection 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.

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 Oracle WebLogic Server Administration Console Help.

  • If no match is found, the default DBMS credential is used.

    Note:

    The default DBMS credential should have minimum DBMS privileges, such as the ability to execute XA transactions and perform connection test operations.
  • If a match is found, it is used to find physical connections matching the DBMS credential.

    • If a match is found, the connection is reserved and returned to the application.

    • If no match is found, a connection is created or reused based on the maximum capacity of the pool:

      • If the maximum capacity has not been reached, a new connection is created with the DBMS credential, reserved, and returned to the application.

      • If the pool has reached maximum capacity, based on the least recently used (LRU) algorithm, a physical connection is selected from the pool and destroyed. A new connection is created with the DBMS credential, reserved, and returned to the application.

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:

  • You must configure a custom schema for LLR using a fully qualified LLR table name. All LLR connections will then use the named schema rather than the default schema when accessing the LLR transaction table.

  • Use database specific administration tools to grant permission to access the named LLR table to all users. By default, the LLR table is created during boot by the user configured for the connection in the data source. In most cases, the database will only allow access to this user and not allow access to mapped users.

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.