3 Configuring JDBC Data Sources
This chapter provides information on how to configure and tune JDBC data sources.
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.
Active GridLink (AGL) data sources—A datasource that provides a connection pool that spans one or more nodes in one or more Oracle RAC clusters. It supports dynamic load balancing of connections across the nodes and handles events that indicates nodes that are added and removed from the cluster(s). See Chapter 5, "Using Active 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 Online Help.
The sample WLST script
EXAMPLES_HOMErepresents the directory in which the WebLogic Server code examples are configured. See "WLST Online Sample Scripts" in Understanding the 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. To avoid naming conflicts, data source names should also be unique among other configuration object names, such as servers, applications, clusters, and JMS queues, topics, and servers. For JDBC application modules packaged in an application, data source names must be unique among JDBC data sources that are similarly scoped.
You can configure a data source so that it binds to the JNDI tree with a single or multiple names. For more information, see "Developing JNDI Applications for Oracle WebLogic Server."
Selecting a Database Type
Select a DBMS. For information about supported databases, see "Supported Configurations" in What's New in Oracle WebLogic Server.
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:
Oracle Thin Driver
Oracle Thin Driver XA
Oracle Thin Driver non-XA
Third-party JDBC drivers (see Chapter 14, "Using JDBC Drivers with WebLogic Server"):
WebLogic-branded DataDirect drivers for the following database management systems (see Using WebLogic-branded DataDirect Drivers):
Microsoft SQL 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
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 Configurations" in What's New in Oracle WebLogic Server.
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 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.
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.prepareon 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.
You can use a Single Client Access Name (SCAN) address to represent the host name. When using Oracle RAC 11.2 and higher, consider the following:
If the Oracle RAC
REMOTE_LISTENERyour data source connects to is set to
SCAN, the data source connection url can only use a SCAN address.
If the Oracle RAC
REMOTE_LISTENERyour data source connects to is set to
List of Node VIPs, the data source connection url can only use a list of VIP addresses.
If the Oracle RAC
REMOTE_LISTENERyour data source connects to is set to
Mix of SCAN and List of Node VIPs, the data source connection url can use both SCAN and VIP addresses.
For more information on using SCAN addresses, see "Introduction to Automatic Workload Management" in Real Application Clusters Administration and Deployment Guide 11g Release 2 (11.2).
Configuring Connection Properties for Oracle BI Server
If you selected Oracle BI Server as your DBMS, configure the additional connection properties on the Connection Properties page as described in "Connection String" in Oracle Business Intelligence Publisher Administrator's and Developer's Guide.
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
System Properties attributes.
Target the Data Source
You can select one or more targets to which 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 before getting connections.
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.
Certain Oracle JDBC extensions, and possibly other non-standard methods available from other drivers may durably alter a connection's behavior in a way that future users of the pooled connection will inherit. WebLogic Server attempts to protect connections against some types of these calls when possible.
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:
JDBC Data Source: Configuration: Connection Pool page in the Administration Console. See "JDBC Data Source: Configuration: Connection Pool" in the Oracle WebLogic Server Administration Console Online Help
JDBCConnectionPoolParamsBean, which is a child MBean of the JDBCDataSourceBean
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
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. Alternatively, you can specify simply a table name without
SQL and the statement
SELECT COUNT(*) FROM tablename is used. 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. An Oracle DBMS example:
SQL alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
or an Informix DBMS:
SQL SET LOCK MODE TO WAIT
The SQL statement is executed using
JDBC Statement.execute(). Options that you can set using InitSQL vary by DBMS. See the documentation for your database vendor for supported statements. If you want to execute multiple statements, you may want to create a stored procedure and execute it. The syntax is vendor specific. For example, to execute an Oracle stored procedure:
SQL CALL MYPROCEDURE()
Advanced Connection Properties
The following sections highlight some important advanced connection properties.
Define Fatal Error Codes
You can define fatal error codes that indicate that the database server with which the data source communicates is no longer accessible on a connection. The connection is marked invalid and taken out of the pool but the data source is not suspended. These errors include deployment errors that cause a server to fail to boot and connection errors that prevent a connection from being put back in the connection pool.
When specified as the exception code within a SQLException (retrieved by
sqlException.getErrorCode()), it indicates that a fatal error has occurred, the connection is no longer good, and it is removed from the connection pool. For Oracle databases the following fatal error codes are predefined within WLS and do not need to be placed in the configuration file:
end-of-file on communication channel
not connected to ORACLE
ORACLE initialization or shutdown in progress
ORACLE not available
immediate shutdown in progress - no operations are permitted
shutdown in progress - connection is not permitted
For DB2, the following fatal error codes are predefined: -4498, -4499, -1776, -30108, -30081, -30080, -6036, -1229, -1224, -1035, -1034, -1015, -924, -923, -906, -518, -514, 58004.
For Informix, the following fatal error codes are predefined: -79735, -79716, -43207, -27002, -25580, -4499, -908, -710, 43012.
To define fatal error codes in the Administration Console, see "Define Fatal Error Codes" in Oracle WebLogic Server Administration Console Online Help.
Enabling Edition-Based Redefinition
Edition-based redefinition is an Oracle database feature that enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time. To use this feature, the data source must be configured with:
SQL ALTER SESSION SET EDITION = name
in the Init SQL parameter in Connection Pool tab of a datasource configuration. You can also use WLST and update the
InitSQL attribute in MBean Reference for Oracle WebLogic Server.
This SQL statement is executed for each newly created physical database connection. See "Edition-Based Redefinition" in the Oracle Database Advanced Application Developer's Guide.
Configuring Oracle Parameters
WebLogic Server provides several attributes that provide improved Data Source performance when using Oracle drivers, for more information, see Advanced Configurations for Oracle Drivers and Databases.
Configuring an ONS Client
Configuring an ONS client changes a generic data source to an AGL data source. For more detailed configuration information and additional environment requirements, see Using Active 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.