JDBC Connection Pools

 

---

Configuring JDBC Connection Pools

A connection pool contains a group of JDBC connections that are created when the connection pool is registered—when starting up WebLogic Server or when deploying the connection pool to a target server or cluster. Connection pools use a JDBC driver to create physical database connections. Your application borrows a connection from the pool, uses it, then returns it to the pool by closing it.

Figure 11-1 Connection Pool Architecture

 

All of the settings you make with the Administration Console are static; that is, all settings persist even after you stop and restart WebLogic Server. You can create dynamic connection pools—those that you expect to use and delete while the server is running—using the command line (see Commands for Managing JDBC Connection Pools in the WebLogic Server Command Reference at ../admin_ref/cli.html#jdbc) or programmatically using the API (see Creating a Connection Pool Dynamically in Programming WebLogic JDBC).

Connection pool settings are persisted in the config.xml file, including settings for dynamically created connection pools (until you programmatically delete the connection pool). For information about entries in the config.xml file, see the JDBCConnectionPool section of the Configuration Reference Guide.

Related Information

• Creating and Configuring a JDBC Connection Pool
• JDBC DataSources
• Read more about Connection Pools in Programming WebLogic JDBC.

 

---

Using the JDBC Connection Pool Assistant

You use the JDBC Connection Pool Assistant to create JDBC connection pools. The JDBC Connection Pool Assistant helps you create and deploy a connection pool by prompting you for database and driver information and then constructing the connection attributes required by your JDBC driver, such as the driver class name and the database URL.

Notes: JDBC drivers listed in the JDBC Connection Pool Assistant are not necessarily certified for use with WebLogic Server. In keeping with the goal of the JDBC Connection Pool Assistant, JDBC drivers are listed as a convenience to help you create a connection to many of the database management systems available.

You must install JDBC drivers in order to use them to create database connections in a connection pool. Drivers are listed in the JDBC Connection Pool Assistant with known required configuration options to help you configure a connection pool. The JDBC drivers in the list are not necessarily installed. Driver installation can include setting system Path, Classpath, and other environment variables.

When a JDBC driver is updated, configuration requirements may change. The JDBC Connection Pool Assistant 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 displayed in Step 3 of the JDBC Connection Pool Assistant or in the property pages for the connection pool.

Creating and Configuring a JDBC Connection Pool

1. Click to expand the Services and JDBC nodes.
2. Right-click the Connection Pools node and select Configure a New JDBC Connection Pool. The JDBC Connection Pool Assistant opens in the right pane.
3. In Step 1 - Choose database, follow these steps:
   1. Database type, select the DBMS of the database that you want to connect to. If your DBMS is not listed, select Other.
   2. In Database driver, select the JDBC driver you want to use to connect to the database. The list includes common JDBC drivers for the selected DBMS. Click Continue.

   Note: You must install JDBC drivers in order to use them to create database connections in a connection pool. Drivers are listed in the JDBC Assistant with known required configuration options to help you configure a connection pool. Driver installation also includes setting system Path, Classpath, and other environment variables.

4. In Step 2 - Define connection properties, follow these steps:
   1. In Name, enter a name for the new connection pool. The name should be unique within the domain.
   2. Under Connection Properties, provide the information requested. The required attributes vary by the DBMS and JDBC driver you selected in the previous step. Many attributes include a common default value. Verify these values for your environment.
   3. Click Continue.
5. In Step 3 - Test database connection, verify the connection properties and then click Test Connection. WebLogic Server attempts to ping your database using the connection properties you provided. The JDBC driver must be installed and configured on the server (on the Administration server in multi-server environments) for the test to succeed.

   If the test is successful, Step 4 - Create and deploy is displayed. If the test is unsuccessful, an error message is displayed at the top of the page. Check the values on the page and correct any errors, then test the connection again.

   You can click Skip this Step to skip the test and continue configuring the connection pool. Note that if you create and deploy a connection pool with errors, the connection pool configuration will be created, but the connection pool will not actually be deployed to servers or clusters. Also, when you restart servers, the servers will start with errors.

6. In Step 4 - Create and deploy, select the servers and clusters on which you want to deploy the connection pool. If you only have one server in your domain, the connection pool is automatically deployed to the server. Click Create and Deploy to complete the process.

In most cases, you should create a data source to use with a connection pool. To create a data source, see Creating and Configuring a JDBC Data Source.

Database Passwords in Connection Pool Configuration

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

• Password. Use this field to set the database password. This value overrides any password value defined in the Properties passed to the tier-2 JDBC Driver when creating physical database connections. The value is encrypted in the config.xml file (stored as the Password attribute in the JDBCConnectionPool tag) and is hidden on the administration console.
• Open String Password. Use this field to set the password in the open string that the transaction manager in WebLogic Server uses to open a database connection. This value overrides any password defined as part of the open string in the Properties field. The value is encrypted in the config.xml file (stored as the XAPassword attribute in the JDBCConnectionPool tag) and is hidden on the Administration Console. At runtime, WebLogic Server reconstructs the open string with the password you specify in this field. The open string in the Properties field should follow this format:

openString=Oracle_XA+Acc=P/userName/+SesTm=177+DB=demoPool+Threads=true=Sqlnet=dvi0+logDir=.

Note that after the userName there is no password.

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

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

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

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

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

Note: The value for Password and Open String Password do not need to be the same.

Cloning a JDBC Connection Pool

1. Click to expand the Services, JDBC, and Connection Pool nodes.
2. Right-click the connection pool you want to clone and select Clone poolname. A dialog displays in the right pane showing the tabs associated with cloning a connection pool. All attribute values except Name are the same as those in cloned pool, including Connection attributes and deployment targets.
3. Enter a new Name. Optionally, you can modify the URL, Driver Classname, and Properties attribute fields. For more information about connection pool general attributes, see Attributes.
4. Click Clone to create a connection pool with the attributes you specified on the General tab and with cloned values on all other tabs. The new connection pool is added under the Connection Pools node in the left pane.
5. Optionally, click the remaining tabs for the connection pool and change the attribute fields. Click Apply to save any changes you make.

Deploying a JDBC Connection Pool to One or More Servers or Clusters

1. In the left pane, click to expand the Services, JDBC, and Connection Pools nodes to display the list of connection pools in the current domain.
2. Click the connection pool that you want to deploy. A dialog displays in the right pane showing the tabs associated with this instance.
3. Click the Target and Deploy tab and select the servers or clusters on which you want to deploy the connection pool. Click Apply to save your changes.

When deploying a JDBC connection pool on a cluster, in most cases you should deploy the connection pool to the entire cluster. You should deploy the related data source to the same targets.

Testing a JDBC Connection Pool

On the JDBC Connection Pool—>Testing tab, you can test a JDBC connection in a connection pool on each server on which the connection pool is deployed.

When you test a connection pool, WebLogic Server reserves and releases a connection from the connection pool.

To make the test more meaningful, make sure that Test Reserved Connections or Test Released Connections is selected on the Configuration—>Connections tab (under Advanced Options). If either of these options is selected, WebLogic Server not only reserves and releases a connection, but also tests the physical database connection. See Test Reserved Connections in Attributes.

To see a description of the information displayed on the JDBC connection Pool—>Testing tab, see Attributes.

To test a connection in a connection pool, follow these steps:

1. In the left pane, click to expand the Services, JDBC, and Connection Pool nodes to display the list of connection pools in the current domain.
2. Click the connection pool that you want to deploy. A dialog displays in the right pane showing the tabs associated with this instance.
3. Click the Testing tab. The Testing tab displays a list of instances of the selected connection pool. Each server on which the connection pool is deployed is listed. Each server can have only one instance of a connection pool.
4. Click the Test Pool button for each instance of the connection pool. Test results are displayed at the top of the pane.
5. Optionally, click the Test pool on all servers button to test all instances of the connection pool. This button is only available if you have more than one instance of the connection pool in your domain.

Configuring the Statement Cache for a JDBC Connection Pool

On the JDBC Connection Pool—>Configuration—>Connections tab, you can configure statement cache attributes for a connection pool. For more information about the statement cache, see Increasing Performance with the Statement Cache. To configure the statement cache, follow these steps:

1. In the left pane, click to expand the Services, JDBC, and Connection Pool nodes to display the list of connection pools in the current domain.
2. Click the connection pool that you want to deploy. A dialog displays in the right pane showing the tabs associated with this instance.
3. Click the Configuration tab, then click the Connections tab.
4. In Statement Cache Type, select one of the following options:
   • LRU - After the statementCacheSize is met, the Least Recently Used statement is removed when a new statement is used.
   • Fixed - The first statementCacheSize number of statements is stored and stay fixed in the cache. No new statements are cached unless the cache is manually cleared.

   See Statement Cache Algorithms for more information.

5. In Statement Cache Size, enter the number of statements to cache per connection per connection pool instance. The default value is 10. See Statement Cache Size for more information.
6. Click Apply to save your changes.

Adding a Note to a JDBC Connection Pool

1. In the left pane, click to the JDBC node to expand it.
2. Click the Connection Pools node to expand it and show the list of connection pools defined in your domain.
3. Click the connection pool to which you want to add a note. A dialog displays in the right pane showing tabs with attributes for the connection pool.
4. Click the Notes tab. Type the note in the Notes field.
5. Click Apply to save your changes.

 

---

Application-Scoped JDBC Connection Pools

When you package your enterprise applications, you can include the weblogic-application.xml supplemental deployment descriptor, which you use to configure application scoping. Within the weblogic-application.xml file, you can configure JDBC connection pools that are created when you deploy the enterprise application.

An instance of the connection pool is created with each instance of your application. This means an instance of the pool is created with the application on each node that the application is targeted to. It is important to keep this in mind when considering pool sizing.

Connection pools created in this manner are known as application-scoped connection pools, app scoped pools, application local pools, app local pools, or local pools, and are scoped for the enterprise application only. That is, they are isolated for use by the enterprise application.

For more information about application scoping and application scoped resources, see:

• Overview of Application Scoping
• weblogic-application.xml Deployment Descriptor Elements
• Packaging Enterprise Applications
• Two-Phase Deployment

 

---

Connection Pool and Data Source Configuration Guidelines

Drivers Supported for Local Transactions

JDBC 2.0 drivers that support the JDBC Core 2.0 API (java.sql), such as the WebLogic jDriver for Oracle. The API allows you to create the class objects necessary to establish a connection with a data source, send queries and update statements to the data source, and process the results.

Drivers Supported for Distributed Transactions Using XA

Any JDBC driver that supports JDBC 2.0 distributed transactions standard extension interfaces (javax.sql.XADataSource, javax.sql.XAConnection, javax.transaction.xa.XAResource), such as the WebLogic jDriver for Oracle/XA.

Drivers Supported for Distributed Transactions without XA

Any JDBC driver that supports JDBC 2.0 Core API but does not support JDBC 2.0 distributed transactions standard extension interfaces (non-XA). Only one non-XA JDBC driver can participate in a distributed transaction. See Configuring Non-XA JDBC Drivers for Distributed Transactions

Configuring JDBC Drivers for Local Transactions

To configure JDBC drivers for local transactions, set up the JDBC connection pool as follows:

• Specify the Driver Classname attribute as the name of the class supporting the java.sql.Driver interface.
• Specify the data properties. These properties are passed to the specific Driver as driver properties.

For more information on WebLogic two-tier JDBC drivers, refer to the BEA documentation for the specific driver you are using: Using WebLogic jDriver for Oracle and Using WebLogic jDriver for Microsoft SQL Server. If you are using a third-party driver, refer to Using Third-Party JDBC XA Drivers with WebLogic Server in Programming WebLogic JTA and the vendor-specific documentation. The following tables show sample JDBC connection pool and Data Source configurations using the WebLogic jDrivers.

The following table shows a sample connection pool configuration using the WebLogic jDriver for Oracle.

Note: The following configuration examples use a Password attribute. The Password attribute value overrides any password defined in Properties (as a name/value pair). This attribute is passed to the 2-tier JDBC driver when creating physical database connections. The value is stored in an encrypted form in the config.xml file and can be used to avoid storing passwords in clear text in that file.

Table 11-1 WebLogic jDriver for Oracle: Connection Pool Configuration

Attribute Name	Attribute Value
General Tab (JDBC Connection Pool --> Configuration --> General)
Name	myConnectionPool
URL	jdbc:weblogic:oracle
Driver Classname	weblogic.jdbc.oci.Driver
Properties	user=scott;server=localdb
Password	tiger (This value overrides any password defined in Properties as a name value pair)
Connections Tab (JDBC Connection Pool --> Configuration --> Connections)
Initial Capacity	1
Max Capacity	15
Capacity Increment	1
Shrink Period	15
Test Table Name	dual
Target and Deploy (JDBC Connection Pool --> Target and Deploy)
Targets	myserver

The following table shows a sample Data Source configuration using the WebLogic jDriver for Oracle.

Table 11-2 Data Source Configuration

Attribute Name	Attribute Value
Configuration Tab (JDBC Data Source --> Configuration)
Name	myDataSource
JNDI Name	myconnection
Pool Name	myConnectionPool
Row Prefetch Size	48
Stream Chunk Size	256
Target and Deploy Tab (JDBC Data Source --> Target and Deploy)
Targets	myserver

The following table shows a sample connection pool configuration using the IBM Informix JDBC Driver.

Table 11-3 IBM Informix JDBC Driver: Connection Pool Configuration

Attribute Name	Attribute Value
General Tab (JDBC Connection Pool --> Configuration --> General)
Name	myConnectionPool
URL	jdbc:informix-sqli:ifxserver:1543
Driver Classname	com.informix.jdbc.IfxDriver
Properties	informixserver=ifxserver;user=informix
Password	informix (Displayed as ******)
Connections Tab (JDBC Connection Pool --> Configuration --> Connections)
Initial Capacity	1
Max Capacity	15
Capacity Increment	1
Login Delay Seconds	1
Shrink Period	15
Target and Deploy (JDBC Connection Pool --> Target and Deploy)
Targets	myserver

Configuring XA JDBC Drivers for Distributed Transactions

To allow XA JDBC drivers to participate in distributed transactions, configure the JDBC connection pool as follows:

• Specify the Driver Classname attribute as the name of the class supporting the javax.sql.XADataSource interface.
• Make sure that the database properties are specified. These properties are passed to the specified XADataSource as data source properties. For more information on data source properties for the WebLogic jDriver for Oracle, see WebLogic jDriver for Oracle/XA Data Source Properties. For information about data source properties for third-party drivers, see the vendor documentation.

The following table shows an example of a JDBC connection pool configuration using the WebLogic jDriver for Oracle in XA mode.

Table 11-4 WebLogic jDriver for Oracle/XA: Connection Pool Configuration

Attribute Name	Attribute Value
General Tab (JDBC Connection Pool --> Configuration --> General)
Name	fundsXferAppPool
URL	(none required)
Driver Classname	weblogic.jdbc.oci.xa.XADataSource
Properties	user=scott;server=localdb
Password	tiger (This value overrides any password defined in Properties as a name value pair)
Connections Tab (JDBC Connection Pool --> Configuration --> Connections)
Initial Capacity	1
Max Capacity	15
Capacity Increment	1
Shrink Period	15
Test Table Name	dual
Target and Deploy (JDBC Connection Pool --> Target and Deploy)
Targets	myserver

The following table shows an example of a Tx Data Source configuration using the WebLogic jDriver for Oracle in XA mode.

Table 11-5 WebLogic jDriver for Oracle/XA: Tx Data Source

Attribute Name	Attribute Value
Configuration Tab (JDBC Data Source --> Configuration)
Name	fundsXferDataSource
JNDI Name	myapp.fundsXfer
Pool Name	fundsXferAppPool
Target and Deploy Tab (JDBC Data Source --> Target and Deploy)
Targets	myserver

You can also configure the JDBC connection pool to use a third-party vendor's driver in XA mode. In such cases, the data source properties are set via reflection on the XADataSource instance using the JavaBeans design pattern. In other words, for property abc, the XADataSource instance must support get and set methods with the names getAbc and setAbc, respectively.

The following attributes are an example of a JDBC connection pool configuration using the Oracle Thin Driver.

Table 11-6 Oracle Thin Driver: Connection Pool Configuration

Attribute Name	Attribute Value
General Tab (JDBC Connection Pool --> Configuration --> General)
Name	jtaXAPool
URL	jdbc:oracle:thin:@server:port:sid
Driver Classname	oracle.jdbc.xa.client.OracleXADataSource
Properties	user=scott
Password	tiger (This value overrides any password defined in Properties as a name value pair)
Connections Tab (JDBC Connection Pool --> Configuration --> Connections)
Initial Capacity	1
Max Capacity	15
Capacity Increment	1
Shrink Period	15
Test Table Name	dual
Target and Deploy (JDBC Connection Pool --> Target and Deploy)
Targets	myserver

The following table shows an example of a Tx Data Source configuration using the Oracle Thin Driver.

Table 11-7 Oracle Thin Driver: Tx Data Source Configuration

Attribute Name	Attribute Value
Configuration Tab (JDBC Data Source --> Configuration)
Name	jtaXADS
JNDI Name	jtaXADS
Pool Name	jtaXAPool
Target and Deploy Tab (JDBC Data Source --> Target and Deploy)
Targets	myserver

The following table shows an example of a JDBC connection pool configuration for distributed transactions using the PointBase JDBC driver.

Table 11-8 PointBase: Connection Pool Configuration

Attribute Name	Attribute Value
General Tab (JDBC Connection Pool --> Configuration --> General)
Name	demoXAPool
URL	jdbc:pointbase:server://localhost/demo
Driver Classname	com.pointbase.xa.xaDataSource
Properties	user=public DatabaseName=jdbc:pointbase:server://localhost/demo
Password	public (Displayed as ******)
Connections Tab (JDBC Connection Pool --> Configuration --> Connections)
Initial Capacity	1
Max Capacity	15
Capacity Increment	1
Supports Local Transaction	true
Shrink Period	15
Test Table Name	users
Target and Deploy (JDBC Connection Pool --> Target and Deploy)
Targets	myserver

Configure the Tx Data Source for use with a PointBase driver as follows.

Table 11-9 PointBase: Tx Data Source Configuration

Attribute Name	Attribute Value
Configuration Tab (JDBC Data Source --> Configuration)
Name	jtaXADS
JNDI Name	JTAXADS
Pool Name	demoXAPool
Target and Deploy Tab (JDBC Data Source --> Target and Deploy)
Targets	myserver

WebLogic jDriver for Oracle/XA Data Source Properties

Table 11-10 lists the data source properties supported by the WebLogic jDriver for Oracle. The JDBC 2.0 column indicates whether a specific data source property is a JDBC 2.0 standard data source property (S) or a WebLogic Server extension to JDBC (E).

The Optional column indicates whether a particular data source property is optional or not. Properties marked with Y* are mapped to the corresponding fields of the Oracle xa_open string (value of the openString property) as listed in Table 11-10. If they are not specified, their default values are taken from the openString property. If they are specified, their values should match those specified in the openString property. If the properties do not match, a SQLException is thrown when you attempt to make an XA connection.

Mandatory properties marked with N* are also mapped to the corresponding fields of the Oracle xa_open string. Specify these properties when specifying the Oracle xa_open string. If they are not specified or if they are specified but do not match, an SQLException is thrown when you attempt to make an XA connection.

Property Names marked with ** are supported but not used by WebLogic Server.

Table 11-10 Data Source Properties for WebLogic jDriver for Oracle/XA

Property Name	Type	Description	JDBC 2.0 standard/extension	Optional	Default Value
databaseName**	String	Name of a particular database on a server.	S	Y	None
dataSourceName	String	A data source name; used to name an underlying XADataSource.	S	Y	Connection Pool Name
description	String	Description of this data source.	S	Y	None
networkProtocol**	String	Network protocol used to communicate with the server.	S	Y	None
password	String	A database password.	S	N*	None
portNumber**	Int	Port number at which a server is listening for requests.	S	Y	None
roleName**	String	The initial SQL role name.	S	Y	None
serverName	String	Database server name.	S	Y*	None
user	String	User's account name.	S	N*	None
openString	String	Oracle's XA open string.	E	Y	None
oracleXATrace	String	Indicates whether XA tracing output is enabled. If enabled (true), a file with a name in the form of xa_poolnamedate.trc is placed in the directory in which the server is started.	E	Y	true

Table 11-11 lists the mapping between Oracle's xa_open string fields and data source properties.

Table 11-11 Mapping of xa_open String Names to JDBC Data Source Properties

Oracle xa_open String Field Name	JDBC 2.0 Data Source Property	Optional
acc	user, password	N
sqlnet	ServerName

Note: You must specify Threads=true in Oracle's xa_open string.

For a complete description of Oracle's xa_open string fields, see your Oracle documentation.

Additional XA Connection Pool Properties

When using connections from a connection pool in distributed transactions, you may need to set additional properties for the connection pool so that the connection pool handles the connection properly within WebLogic Server in the context of the transaction. You set these properties in the configuration file (config.xml) within the JDBCConnectionPool tag. By default, all additional properties are set to false. You set the properties to true to enable them.

In many cases, WebLogic Server automatically sets the proper value for these properties internally so that you do not have to set them manually.

KeepXAConnTillTxComplete

Some DBMSs require that you start and end a transaction in the same physical database connection. In some cases, a transaction in WebLogic Server may start in one physical database connection and end in another physical database connection. To force a connection pool to reserve a physical connection and provide the same connection to an application throughout transaction processing until the transaction is complete, you set KeepXAConnTillTxComplete="true". For example:

<JDBCConnectionPool KeepXAConnTillTxComplete="true" DriverName="com.sybase.jdbc2.jdbc.SybXADataSource" CapacityIncrement="5" InitialCapacity="10" MaxCapacity="25" Name="demoXAPool" Password="{3DES}vIF8diu4H0QmdfOipd4dWA==" Properties="User=dbuser;DatabaseName=dbname;ServerName=server_name_or_IP_address;PortNumber=serverPortNumber;NetworkProtocol=Tds;resourceManagerName=Lrm_name_in_xa_config;resourceManagerType=2" />

Note: This property is required to support distributed transactions with DB2 and Sybase.

Configuring Non-XA JDBC Drivers for Distributed Transactions

When configuring the JDBC connection pool to allow non-XA JDBC drivers to participate with other resources in distributed transactions, select the Emulate Two-Phase Commit for non-XA Driver attribute (EnableTwoPhaseCommit in the JDBCTxDataSource MBean) for the JDBC Tx Data Source. This parameter is ignored by resources that support the XAResource interface. Note that only one non-XA connection pool may participate in a distributed transaction. See Emulating Two-Phase Commit for more information.

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 rather than use this option. Make sure you consider the risks below before enabling this option. See Limitations and Risks.

Non-XA Driver/Single Resource

If you are using only one non-XA driver and it is the only resource in the transaction, leave the Emulate Two-Phase Commit for non-XA Driver option unselected in the Console (accept the default EnableTwoPhaseCommit = false). In this case, the Transaction Manager performs a one-phase optimization.

Non-XA Driver/Multiple Resources

If you are using one non-XA JDBC driver with other XA resources, select Emulate Two-Phase Commit in the Administration Console (EnableTwoPhaseCommit = true).

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. This mechanism ensures that there is only one participant in the transaction, as commit() throws a SystemException in this case. 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.

The following table shows configuration attributes for a sample JDBC connection pool using a non-XA JDBC driver.

Table 11-12 WebLogic jDriver for Oracle: Connection Pool Configuration

Attribute Name	Attribute Value
General Tab (JDBC Connection Pool --> Configuration --> General)
Name	fundsXferAppPool
URL	jdbc:weblogic:oracle
Driver Classname	weblogic.jdbc.oci.Driver
Properties	user=scott;server=localdb
Password	tiger (Displayed as ***** when typed, hidden thereafter; this value overrides any password defined in Properties as a name value pair)
Connections Tab (JDBC Connection Pool --> Configuration --> Connections)
Initial Capacity	0
Max Capacity	5
Capacity Increment	1
Shrink Period	15
Test Table Name	dual
Target and Deploy (JDBC Connection Pool --> Target and Deploy)
Targets	myserver

The following table shows configuration attributes for a sample Tx Data Source using a non-XA JDBC driver.

Table 11-13 WebLogic j Driver for Oracle: Tx Data Source Configuration

Attribute Name	Attribute Value
Configuration Tab (JDBC Data Source --> Configuration)
Name	fundsXferDataSource
JNDI Name	myapp.fundsXfer
Pool Name	fundsXferAppPool
Emulate Two-Phase Commit for non-XA Driver	selected (EnableTwoPhaseCommit = true)
Target and Deploy Tab (JDBC Data Source --> Target and Deploy)
Targets	myserver

 

---

Security for JDBC Connection Pools

You can optionally restrict access to JDBC connection pools. 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 Protecting WebLogic Resources.

 

---

Managing JDBC Connection Pools

From the JDBC Connection Pool property tabs in the Administration Console, you can manage the connections pools in your domain. The following sections provide detailed instructions for manually performing management tasks on JDBC connection pools.

Resetting All Connections in a JDBC Connection Pool

When you reset a connection pool, WebLogic Server shuts down and recreates all database connections in the connection pool.

1. In the left pane, click to expand the Services, JDBC, and Connection Pools nodes to display the list of connection pools in the current domain.
2. Click the connection pool that you want to reset. A dialog displays in the right pane showing the tabs associated with this connection pool.
3. Click the Control tab. The control tab lists each server on which the connection pool is deployed.
4. Click Reset for each server on which you want to reset all connections in the connection pool.

Shrinking a JDBC Connection Pool

If you configure a connection pool so that it can add database connections as demand for connections increases, you can click the Shrink button on the Control tab to manually shrink the connection pool. When you shrink a connection pool, WebLogic Server reduces the number of connections in the pool to the greater of either the initial capacity or the number of connections currently in use.

1. In the left pane, click to expand the Services, JDBC, and Connection Pools nodes to display the list of connection pools in the current domain.
2. Click the connection pool that you want to reset. A dialog displays in the right pane showing the tabs associated with the connection pool.
3. Click the Control tab. The control tab lists each server on which the connection pool is deployed.
4. Click Shrink for each server on which you want to shrink the connection pool instance.

Suspending a JDBC Connection Pool

When you suspend a connection pool, you make the connections in the pool unavailable for applications to use. WebLogic Server provides the following options for suspending a connection pool:

• Suspend—which marks the pool as disabled and blocks any new requests for a connection from the connection pool. If connections are currently in use, the suspend operation will fail and will put the connection pool into an unknown state. To correct that state, you must shut down the pool by undeploying it. See Shutting Down a JDBC Connection Pool.
• Force Suspend—which marks the pool as disabled and prevents applications from using connections from the connection pool, including those connections currently in use.

Connections is a suspended connection pool remain intact. The connections are not recreated when you resume the connection pool.

To suspend a connection pool, follow these steps:

1. In the left pane, click to expand the Services, JDBC, and Connection Pool nodes to display the list of connection pools in the current domain.
2. Click the connection pool that you want to suspend. A dialog displays in the right pane showing the tabs associated with the connection pool.
3. Click the Control tab. The control tab lists each server on which the connection pool is deployed.
4. For each server listed, choose one of the following options:
   • Click Suspend to block new requests to reserve a connection from the connection pool and mark the connection pool as disabled. If connections are currently in use, this operation will fail.
   • Click Force Suspend to block new requests to reserve a connection from the connection pool and to stop all current use of connections from the connection pool. This operation also marks the connection pool as disabled.

Resuming a JDBC Connection Pool

After manually suspending a connection pool, you can re-enable it by clicking Resume on the JDBC Connection Pool—>Control tab. You cannot use the Resume functionality to restart a connection pool that failed to start properly.

Follow these instructions.

1. In the left pane, click to expand the Services, JDBC, and Connection Pool nodes to display the list of connection pools in the current domain.
2. Click the connection pool that you want to resume. A dialog displays in the right pane showing the tabs associated with the connection pool.
3. Click the Control tab. The control tab lists each server on which the connection pool is deployed.
4. Click the Resume button for the instance of the connection pool that you want to re-enable. This option is only available for connection pools that were successfully suspended.

Shutting Down a JDBC Connection Pool

To shut down an instance of a connection pool, you can un-deploy the connection pool on the server. This operation closes all physical database connections in the connection pool. To shut down the connection pool on more than on target, you must un-deploy on each deployment target.

Follow these steps:

1. In the left pane, click to expand the Services, JDBC, and Connection Pools nodes to display the list of connection pools in the current domain.
2. Click the connection pool that you want to shut down. A dialog displays in the right pane showing the tabs associated with this instance.
3. Click the Target and Deploy tab.
4. Clear the check box for the servers or clusters on which you want to shut down the connection pool. Click Apply to save your changes.

See the following related information:

• Resetting All Connections in a JDBC Connection Pool
• Suspending a JDBC Connection Pool

Restarting a JDBC Connection Pool

To restart a connection pool after shutting it down by undeploying it (see Shutting Down a JDBC Connection Pool), you re-deploy the connection pool to servers and clusters. See Deploying a JDBC Connection Pool to One or More Servers or Clusters for instructions.

Destroying or Deleting a JDBC Connection Pool

When you destroy a JDBC connection pool, all database connections in all instances of the connection pool are closed and the connection pool configuration is removed from the domain.

Note: When you destroy a connection pool, you destroy all instances of the connection pool, not just the instance for which you clicked the Destroy button.

There are two Destroy options for connection pools in WebLogic Server:

• Destroy—Closes all database connections in all instances of the connection pool and permanently deletes the connection pool configuration from the domain. If connections in the connection pool are in use, the operation will fail.
• Force Destroy—Forcibly closes all database connections in all instances of the connection pool, even if connections are in use, and permanently deletes the connection pool configuration from the domain.

To destroy a connection pool, follow these steps:

1. In the left pane, click to expand the Services, JDBC, and Connection Pools nodes to display the list of connection pools in the current domain.
2. Click the connection pool that you want to destroy. A dialog displays in the right pane showing the tabs associated with the connection pool.
3. Click the Control tab. The control tab lists each server on which the connection pool is deployed.
4. For any server listed, choose one of the following options:
   • Click Destroy to close all database connections and delete the connection pool. This action applies to all servers. If a connection is in use, the operation will fail.
   • Click Force Destroy to forcibly close all database connections and delete the connection pool. This action applies to all servers.

Clearing the Statement Cache for a JDBC Connection Pool

To clear the statement cache for all connections in a connection pool, follow these steps:

1. In the left pane, click to expand the Services, JDBC, and Connection Pools nodes to display the list of connection pools in the current domain.
2. Click the connection pool for which you want to clear the statement cache. A dialog displays in the right pane showing the tabs associated with this connection pool.
3. Click the Control tab. The control tab lists each server on which the connection pool is deployed.
4. Click Clear Statement Cache for each server on which you want to clear the statement cache for all connections in the connection pool. Repeat for each instance of the connection pool as required.

For more information about the statement cache for a connection pool, see Increasing Performance with the Statement Cache.

 

---

Monitoring a JDBC Connection Pool

Monitoring Connections in a JDBC Connection Pool

1. In the left pane, click to the JDBC node to expand it.
2. Click the Connection Pools node to expand it and show the list of connection pools defined in your domain.
3. Click the connection pool for which you want to see database connection information. A dialog displays in the right pane showing tabs with attributes for the connection pool.
4. Click the Monitoring tab and then click the Monitor all Active Pools text link. A table displays with information about connections in the selected JDBC connection pool.

For details about the information displayed, see JDBC Connection Pool --> Monitoring.

 

---

Tuning Connection Pools

By properly configuring connection pools in your WebLogic Server domain, you can improve application and system performance.

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 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 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 connection pool has its own individual cache of prepared and callable statements used on the connection. However, you configure statement cache options per connection pool. That is, the statement cache for each connection in a connection pool uses the statement cache options specified for the connection pool. Statement cache configuration options include:

• Statement Cache Type—The algorithm that determines which statements to store in the statement cache. See Statement Cache Algorithms.
• Statement Cache Size—The number of statements to store in the cache for each connection. The default value is 10. See Statement Cache Size.

You can use the following methods to set statement cache options for a connection pool:

• Using the Administration Console (preferred). See Configuring the Statement Cache for a JDBC Connection Pool.
• Using the WebLogic management API. See the following methods in the Javadocs for WebLogic Classes:
• getStatementCacheType()
• setStatementCacheType(string type)
• getPreparedStatementCacheSize()
• setPreparedStatementCacheSize(int cacheSize)
• Directly in the configuration file (typically config.xml).

To set the prepared statement cache size for a connection pool using the configuration file, before starting the server, open the config.xml file in an editor, then add an entry for the PreparedStatementCacheSize attribute in the JDBCConnectionPool tag. For example:

    <JDBCConnectionPool CapacityIncrement="5"
        DriverName="com.pointbase.jdbc.jdbcUniversalDriver"
        InitialCapacity="5" MaxCapacity="20" Name="demoPool"
        Password="{3DES}ANfMduXgaaGMeS8+CR1xoA=="
        PreparedStatementCacheSize="10" StatementCacheType="LRU"
        Properties="user=examples"
        RefreshMinutes="0" ShrinkPeriodMinutes="15"
        ShrinkingEnabled="true" Targets="examplesServer"
        TestConnectionsOnRelease="false"
        TestConnectionsOnReserve="false"
        URL="jdbc:pointbase:server://localhost/demo"/>

You can also manually clear the statement cache for a connection pool. See Clearing the Statement Cache for a JDBC Connection Pool.

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 connection pool. You can choose from the following options:

• LRU (Least Recently Used)
• Fixed

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.prepare statement, 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 connection pool. 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 connection pool 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 prepared 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 prepared statement cache size to 0, which turns off prepared 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

When using the WebLogic jDriver for Oracle to connect to the database, 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, 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) 

This issue occurs consistently when using the WebLogic jDriver for Oracle. It may occur when using other JDBC drivers.

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 prepared statement cache size for the connection pool.