weblogic JDBC Connection Pools--转官方文档

http://docs.oracle.com/cd/E13222_01/wls/docs81/ConsoleHelp/jdbc_connection_pools.html#1106016

 

JDBC Connection Pools

[Attributes and Console Screen Reference for JDBC Connection Pools]

The following sections explain how to configure and manage JDBC connection pools in the Administration Console:

• Configuring JDBC Connection Pools
• Using the JDBC Connection Pool Assistant
• Application-Scoped JDBC Data Sources and Connection Pools
• Connection Pool and Data Source Configuration Guidelines
• Security for JDBC Connection Pools
• Managing JDBC Connection Pools
• Monitoring Connections in a JDBC Connection Pool
• Tuning 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 127-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) 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
• 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.

When you create a connection pool with the JDBC Connection Pool Assistant, many attributes for the connection pool are set with the default value. You may need to change connection pool settings to suit your environment. For example, you may need to increase the maximum number of connections available in the connection pool if your application consistently cannot reserve a connection because all connections in the connection pool are in use.

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 on each server on which the connection pool is deployed. 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. See Setting the Environment for Your Third-Party JDBC Driver in Programming WebLogic JDBC.

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 after the pool is created.

Creating and Configuring a JDBC Connection Pool

• Click to expand the Services and JDBC nodes.

• Right-click the Connection Pools node and select Configure a New JDBC Connection Pool. The JDBC Connection Pool Assistant opens in the right pane.

• In Choose Database, follow these steps:

• Database type, select the DBMS of the database that you want to connect to. If your DBMS is not listed, select Other.

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

• In Define Connection Properties, follow these steps:

• In Name, enter a name for the new connection pool. The name should be unique within the domain.

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

• Click Continue.

• In Test Database Connection, verify the connection properties and then click Test Driver Configuration. WebLogic Server attempts to load the driver and create a direct connection to the database server 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, the Create and Deploy page 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.

• In 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 JDBCConnectionPooltag) 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=dbHost+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 thePassword 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=dbHost+Threads=true+Sqlnet=lcs817+logDir=.+dbgFl=0x15;server=dbHost

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=dbHost+Threads=true+Sqlnet=lcs817+logDir=.+dbgFl=0x15;server=dbHost

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

• Click to expand the Services, JDBC, and Connection Pool nodes.

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

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

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

• 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

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

• Click the connection pool that you want to deploy. A dialog displays in the right pane showing the tabs associated with this instance.

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

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, seeIncreasing Performance with the Statement Cache. To configure the statement cache, follow these steps:

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

• Click the connection pool that you want to configure. A dialog displays in the right pane showing the tabs associated with this instance.

• Click the Configuration tab, then click the Connections tab.

• 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 or the cache size is increased.

See Statement Cache Algorithms for more information.

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

• Click Apply to save your changes.

Adding a Note to a JDBC Connection Pool

• In the left pane, click to the JDBC node to expand it.

• Click the Connection Pools node to expand it and show the list of connection pools defined in your domain.

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

• Click the Notes tab. Type the note in the Notes field.

• Click Apply to save your changes.

 

---

Application-Scoped JDBC Data Sources and Connection Pools

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

Data sources and 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. You deploy each application-scoped connection pool/data source pair as a module in the application.

Each application scoped connection pool that you create must reference a data source factory. The data source factory creates the application-scoped data source and underlying connection pool. Values in the weblogic-application.xml can override the default values provided in the data source factory.

An instance of the data source and connection pool is created with each instance of your application (unless you deploy modules individually). This means an instance of the pool is created with the application on each server that the application is targeted to. It is important to keep this in mind when considering connection pool sizing. For example if each instance of the connection pool has 10 database connections, and the application is deployed on 10 servers, your domain will have 100 connections to the database. You may need to consider database limits, including the maximum number of open cursors.

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

• Configuring and Using Application-Scoped JDBC Connection Pools
• Overview of Application Scoping
• weblogic-application.xml Deployment Descriptor Elements
• Quickstart Guide to Deploying Modules
• Two-Phase Deployment

Configuring Application-Scoped Connection Pool Attributes

If you deploy your enterprise application as an archive (.EAR, .WAR, .JAR, or .RAR extension), you configure and make all changes to the application-scoped connection pool for the application in the weblogic-application.xml deployment descriptor. However, if you deploy your application in an exploded archive directory, you can make configuration changes to an application-scoped connection pool in the Administration Console. Changes are made directly in the weblogic-application.xml deployment descriptor and take effect immediately. You do not have to redeploy the application module (application-scoped connection pool).

To change configuration attributes for an application-scoped connection pool (deployed in an exploded archive):

• In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.

• Select the application-scoped data source from the list of application components. In the right pane, tabs display with attributes for the application-scoped connection pool associated with the application-scoped data source.

• Select the Configuration tab, then select the Descriptor tab.

• Make changes as necessary, and click Apply to save your changes. For more information about each attribute that you can change, see Attributes.

Deploying Application-Scoped Connection Pools

When you deploy an application in an environment with several managed servers or one or more clusters, you can choose to deploy all components at once or choose to deploy application components individually. For the latter option, make sure to deploy application-scoped data sources (which includes the associated connection pools) to the same deployment targets as other application components that will use database connections from the connection pool. For example, if Application Module 1 is deployed on Managed Server 1 and Application Module 1 uses connections from Data Source 1, make sure that Data Source 1 is deployed on Managed Server 1.

After you deploy an application-scoped data source, application components access connections from the application-scoped connection pool by looking up the application-scoped data source on the local JNDI tree at java:comp/env. The data source must be available on the local server.

Selecting Deployment Targets for an Application-Scoped Connection Pool

To change deployment targets for an application-scoped data source and connection pool:

• In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.

• Select the application-scoped data source from the list of application components. In the right pane, tabs display with attributes for the application-scoped connection pool associated with the application-scoped data source.

• Select the Targets tab, then select the servers and clusters on which you want to deploy the application-scoped data source and connection pool. Select the same targets as other application modules that use connections from the pool.

Stopping and Redeploying an Application-Scoped Connection Pool

On the Deploy tab, you can view the status of each deployed instance of an application-scoped connection pool. For example, if your application-scoped connection pool is deployed on three servers in your domain, you will see each deployment target listed and the status of the connection pool on that target.You can stop or redeploy an active connection pool or deploy an inactive connection pool. When you stop a connection pool, all database connections are closed. When you redeploy a connection pool, all connections are closed and the connection pool is recreated, which includes recreating the initial number of database connections.

Note: To add deployment targets, select additional targets on the Targets tab. See Selecting Deployment Targets for an Application-Scoped Connection Pool.

To stop, redeploy, or deploy and application-scoped connection pool:

• In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.

• Select the application-scoped data source from the list of application components. In the right pane, tabs display with attributes for the application-scoped connection pool associated with the application-scoped data source.

• Select the Deploy tab, which lists each deployed instance of the connection pool and its status.

• Choose from the following options:

• Click Stop to stop the selected deployed instance of the connection pool.
• Click Redeploy to stop the selected deployed instance of the connection pool and then recreate the connection pool with the initial number of connections.
• Click Deploy to start an inactive deployed instance of a connection pool.

Monitoring Application-Scoped Connection Pools

To monitor an application-scoped connection pool:

• In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.

• Select the application-scoped data source from the list of application components. In the right pane, tabs display with attributes for the application-scoped connection pool associated with the application-scoped data source.

• Select the Monitoring tab. A table displays with information about connections in the selected connection pool.

Manually Administering an Application-Scoped Connection Pool

On the Control tab for an application-scoped connection pool, you can manually shrink or reset the connection pool or clear the statement cache for the connection pool. In many cases, shrink and refresh operations, as well as statement cache management, are handled automatically by WebLogic Server based on settings for the connection pool.

Shrinking an Application-Scoped Connection Pool

• In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.

• Select the application-scoped data source from the list of application components. In the right pane, tabs display with attributes for the application-scoped connection pool associated with the application-scoped data source.

• Select the Control tab. A table displays with a list of deployed instances of the connection pool.

• Click Shrink for the instance of the connection pool that you want to shrink. When you shrink a connection pool, the server reduces the number of database connections in the connection pool to the greater value of either the initial number of connections or the current number of connections in use.

Resetting an Application-Scoped Connection Pool

• In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.

• Select the application-scoped data source from the list of application components. In the right pane, tabs display with attributes for the application-scoped connection pool associated with the application-scoped data source.

• Select the Control tab. A table displays with a list of deployed instances of the connection pool.

• Click Reset for the instance of the connection pool that you want to reset. When you reset a connection pool, the server closes and reopens all available database connections in the connection pool.

Clearing the Statement Cache for an Application-Scoped Connection Pool

• In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.

• Select the application-scoped data source from the list of application components. In the right pane, tabs display with attributes for the application-scoped connection pool associated with the application-scoped data source.

• Select the Control tab. A table displays with a list of deployed instances of the connection pool.

• Click Clear Statement Cache for the instance of the connection pool for which you want to clear the statement cache.

Testing an Application-Scoped Connection Pool

On the 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 Check On Reserve Enabled or Check On Release Enabled is selected on the Configuration—>Descriptor tab. If either of these options is selected, WebLogic Server not only reserves and releases a connection, but also tests the physical database connection. See Check On Reserve Enabled in Attributes.

For more information about connection testing and configuration options, see Connection Testing Options.

To test a connection in an application-scoped connection pool, follow these steps:

• In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.

• Select the application-scoped data source from the list of application components. In the right pane, tabs display with attributes for the application-scoped connection pool associated with the application-scoped data source.

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

• Click the Test Pool button for each instance of the connection pool. Test results are displayed at the top of the pane.

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

Adding a Note to an Application-Scoped Connection Pool

• In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.

• Select the application-scoped data source from the list of application components. In the right pane, tabs display with attributes for the application-scoped connection pool associated with the application-scoped data source.

• Click the Notes tab and enter a note in the text field.

 

---

Connection Pool and Data Source Configuration Guidelines

The following sections provide configuration guidelines and examples for JDBC connection pools and data sources.

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 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 documentation from the JDBC driver vendor. 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 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 127-2 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 Frequency Seconds	900
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 127-3 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 127-4 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 Frequency Seconds	900
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 data source and connection pool as follows:

• Specify the Driver Classname connection pool 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 Additional XA Connection Pool Properties. For information about data source properties for third-party drivers, see the vendor documentation.
• In the data source, make sure that Honor Global Transactions is selected (the default).

See Configuring JDBC DataSources for more details about configuring data sources.

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

Table 127-5 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 Frequency Seconds	900
Test Table Name	dual
Target and Deploy (JDBC Connection Pool --> Target and Deploy)
Targets	myserver

 

The following table shows an example of a data source (Tx Data Source in the config.xml file) configuration using the WebLogic jDriver for Oracle in XA mode.

Table 127-6 WebLogic jDriver for Oracle/XA: Data Source Configuration

Attribute Name	Attribute Value
Configuration Tab (JDBC Data Source --> Configuration)
Name	fundsXferDataSource
JNDI Name	myapp.fundsXfer
Pool Name	fundsXferAppPool
Honor Global Transactions	true (Must be selected when you create the data source.)
Target and Deploy Tab (JDBC Data Source --> Target and Deploy)
Targets	myserver

 

Note: Do not create two Tx Data Sources that point to the same connection pool. If a transaction uses two different Tx Data Sources which are both pointed to the same connection pool, you will get an XA_PROTO error when you try to access the second connection.

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 127-7 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 Frequency Seconds	900
Test Table Name	dual
Target and Deploy (JDBC Connection Pool --> Target and Deploy)
Targets	myserver

 

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

Table 127-8 Oracle Thin Driver: Data Source Configuration for XA

Attribute Name	Attribute Value
Configuration Tab (JDBC Data Source --> Configuration)
Name	jtaXADS
JNDI Name	jtaXADS
Pool Name	jtaXAPool
Honor Global Transactions	true (Must be selected when you create the data source.)
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.

Note: PointBase Server is an all-Java DBMS product included in the WebLogic Server distribution solely in support of WebLogic Server evaluation, either in the form of custom trial applications or through packaged sample applications provided with WebLogic Server. Non-evaluation development and/or production use of the PointBase Server requires a separate license be obtained by the end user directly from PointBase.

Table 127-9 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 Frequency Seconds	900
Test Table Name	users
Target and Deploy (JDBC Connection Pool --> Target and Deploy)
Targets	myserver

 

 

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

Table 127-10 PointBase: Data Source Configuration for XA

Attribute Name	Attribute Value
Configuration Tab (JDBC Data Source --> Configuration)
Name	jtaXADS
JNDI Name	JTAXADS
Pool Name	demoXAPool
Honor Global Transactions	true (Must be selected when you create the data source.)
Target and Deploy Tab (JDBC Data Source --> Target and Deploy)
Targets	myserver

 

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 or on the JDBC Connection Pool --> Configuration --> Connections tab in the Administration Console. 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.

For additional XA connection pool properties, see Advanced Attributes.

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 select the Keep XA Connection Till Transaction Complete option on the JDBC Connection Pool --> Configuration --> Connections tab.

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

NeedTxCtxOnClose

Some JDBC XA drivers require a distributed transaction context when closing various JDBC objects (result sets, statements, connections, and so forth). To force WebLogic Server to swallow SQL exceptions that are thrown while closing JDBC objects without a transaction context, you select the Need Transaction Context On Close option on the JDBC Connection Pool --> Configuration --> Connections tab.

newXAConnForCommit

Some JDBC XA drivers require that a dedicated XA connection be used for commit/rollback processing of distributed transactions. To force WebLogic Server to use a separate XA connection when committing or rolling back a transaction, you select the New XA Connection For Commit option on the JDBC Connection Pool --> Configuration --> Connections tab.

XAEndOnlyOnce

Some DBMSs and JDBC XA drivers require that XAResource.end() is called only once for each pending XAResource.start(). To prevent WebLogic Server from callingXAResource.end(TMSUSPEND) and XAResource.end(TMSUCCESS) in succession, you select the XA End Only Once option on the JDBC Connection Pool --> Configuration --> Connections tab.

KeepLogicalConnOpenOnRelease

Some DBMSs and JDBC XA drivers require that you keep a logical JDBC connection open while transaction processing continues (although the physical XA connection can returned to the XA connection pool). To keep a logical connection open while transaction operations are running, you select the Keep Connection Open On Release option on the JDBC Connection Pool --> Configuration --> Connections tab.

SupportsLocalTransaction

With some XA JDBC drivers, you can execute SQL statements without starting a global transaction. For other drivers prevent any SQL operations with a global transaction. If the XA JDBC driver used in your connection pool supports SQL operations without a global transaction, select the Supports Local Transaction option on the JDBC Connection Pool --> Configuration --> Connectionstab.

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 When Using a Non-XA Driver in Global Transactions.

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 defaultEnableTwoPhaseCommit = 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 theXAResource.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 127-11 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 Frequency Seconds	900
Test Table Name	dual
Target and Deploy (JDBC Connection Pool --> Target and Deploy)
Targets	myserver

 

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

Table 127-12 WebLogic j Driver for Oracle: Data Source Configuration

Attribute Name	Attribute Value
Configuration Tab (JDBC Data Source --> Configuration)
Name	fundsXferDataSource
JNDI Name	myapp.fundsXfer
Pool Name	fundsXferAppPool
Honor Global Transactions	true (Must be selected when you create the data source.)
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. For more information about securing server resources, see Securing 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.

Testing a JDBC Connection Pool

On the JDBC Connection Pool—>Testing tab, you can manually 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.

Also see Connection Testing Options for more information about connection testing options and the default value for Test Table Name.

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

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

• Click the connection pool that you want to deploy. A dialog displays in the right pane showing the tabs associated with this instance.

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

• Click the Test Pool button for each instance of the connection pool. Test results are displayed at the top of the pane.

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

Resetting All Connections in a JDBC Connection Pool

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

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

• Click the connection pool that you want to reset. A dialog displays in the right pane showing the tabs associated with this connection pool.

• Click the Control tab. The control tab lists each server on which the connection pool is deployed.

• 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 value of either the initial capacity or the number of connections currently in use.

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

• Click the connection pool that you want to reset. A dialog displays in the right pane showing the tabs associated with the connection pool.

• Click the Control tab. The control tab lists each server on which the connection pool is deployed.

• 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. An application will get an exception if it requests a connection or tries to use a connection it reserved before the connection pool was suspended.
• Force Suspend—which marks the pool as disabled, blocks any new requests for a connection from the connection pool, and closes and recreates connections currently in use.

Connections in a suspended connection pool remain intact. The connections are not recreated when you resume the connection pool, except when the connection pool was Force Suspended.

To suspend a connection pool, follow these steps:

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

• Click the connection pool that you want to suspend. A dialog displays in the right pane showing the tabs associated with the connection pool.

• Click the Control tab. The control tab lists each server on which the connection pool is deployed.

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

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

• Click the connection pool that you want to resume. A dialog displays in the right pane showing the tabs associated with the connection pool.

• Click the Control tab. The control tab lists each server on which the connection pool is deployed.

• 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 one target, you must un-deploy on each deployment target.

Note: If connections are currently in use, the shutdown operation will fail and the connection pool will go into the Suspended state. You must resume the connection pool to restore normal operations.

If you want to force the connection pool to shut down, force suspend the connection pool and then follow the steps below.

To shut down a connection pool, follow these steps:

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

• Click the connection pool that you want to shut down. A dialog displays in the right pane showing the tabs associated with this instance.

• Click the Target and Deploy tab.

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

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

• Click the connection pool that you want to destroy. A dialog displays in the right pane showing the tabs associated with the connection pool.

• Click the Control tab. The control tab lists each server on which the connection pool is deployed.

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

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

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

• Click the Control tab. The control tab lists each server on which the connection pool is deployed.

• 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 Connections in a JDBC Connection Pool

• In the left pane, click to the JDBC node to expand it.

• Click the Connection Pools node to expand it and show the list of connection pools defined in your domain.

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

• Click the Monitoring tab. A table displays with information about connections in the selected JDBC connection pool for each server on which the connection pool is deployed.

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. This section includes the following information:

• Enabling Connection Requests to Wait for a Connection
• Automatically Recovering Leaked Connections
• Initializing Database Connections with SQL Code
• Connection Testing Options
• Increasing Performance with the Statement Cache
• Increasing Performance with the PinnedToThread JDBC Connection Pool Property

Enabling Connection Requests to Wait for a Connection

On the JDBC—>Connection Pool—>Configuration—>Connections tab, there are two attributes that you can set to enable connection requests to wait for a connection from a connection pool: Connection Reserve Timeout and Maximum Waiting for Connection.

Connection Reserve Timeout

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

• If you set Connection Reserve Timeout to 0, a connection request will wait indefinitely.
• If you set Connection Reserve Timeout to -1, a connection request will timeout immediately.

See Connection Reserve Timeout for more attribute details.

Maximum Waiting for Connection

Note that 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 attribute, which limits the number connection requests that can concurrently wait for a connection.

If you set Maximum Waiting for Connection to 0, the feature is disabled and connection requests will not be able to wait for a connection.

See Maximum Waiting for Connection for more attribute details.

To Enable a Connection Request to Wait for a Connection

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

• Click the connection pool that you want to configure. A dialog displays in the right pane showing the tabs associated with this instance.

• Click the Configuration tab, then click the Connections tab.

• Click Show to show the advanced connection options.

• In Connection Reserve Timeout, enter the number of seconds that connection requests can wait for a connection.

• In Maximum Waiting for a Connection, enter the maximum number of connection requests that can wait for a connection from the connection pool while blocking threads.

• Click Apply.

Automatically Recovering Leaked Connections

A leaked connection is a connection that was not properly returned to the connection pool. To automatically recover leaked connections, you can specify a value for Inactive Connection Timeout on the JDBC—>Connection Pool—>Configuration—>Connections tab. When you set a value for Inactive Connection Timeout, WebLogic Server will forcibly return a connection to the connection pool 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 Inactive Connection Timeout for more attribute details.

Note that the actual timeout could exceed the configured value for Inactive Connection Timeout. The internal connection pool 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 connection pool. On average, there could be a lag of 50% more than the configured value.

Enabling Automatic Leaked Connection Recovery

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

• Click the connection pool that you want to configure. A dialog displays in the right pane showing the tabs associated with this instance.

• Click the Configuration tab, then click the Connections tab.

• Click Show to show the advanced connection options.

• In Inactive Connection Timeout, enter the number of seconds of inactivity after which a connection will forcibly be returned to the connection pool.

Viewing Leaked Connections

After enabling automatic leaked connection recovery, you can view statistics about connections that leaked from a connection pool:

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

• Right-click the connection pool that you suspect is leaking connections and select View Leaked Connections. If any connections leaked and were recovered, information about the application that reserved the connection is displayed in the right pane.

Initializing Database Connections with SQL Code

When WebLogic Server creates database connections in a connection pool, the server can automatically run SQL code to initialize the database connection. To enable this feature, enter SQLfollowed by a space and the SQL code you want to run in the Init SQL attribute on the JDBC—>Connection Pool—>Configuration—>Connections tab. 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 connection pool, which includes at server startup, when expanding the connection pool, and when refreshing a connection.

To initialize a database connection with SQL code:

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

• Click the connection pool that you want to configure. A dialog displays in the right pane showing the tabs associated with this instance.

• Click the Configuration tab, then click the Connections tab.

• Click Show to show the advanced connection options.

• In Init SQL, enter SQL followed by a space and the SQL code you want to run to initialize database connections.

Connection Testing Options

To make sure that the database connections in a connection pool remain healthy, you should periodically test the connections. WebLogic Server includes two basic types of testing: automatic testing that you configure with options on the JDBC—>Connection Pool—>Configuration—>Connections tab and manual testing that you can do to trouble-shoot a connection pool from the JDBC—>Connection Pool—>Testing tab. The following section discusses automatic connection testing options. For more information about manual connection testing, see Testing a JDBC Connection Pool.

On the JDBC—>Connection Pool—>Configuration—>Connections, you use the following settings to configure connection testing:

• Test Frequency—Use this attribute to specify the number of seconds between tests of unused connections. The server tests unused connection and reopens any faulty connections. You must also set the Maximum Connections Made Unavailable and a Test Table Name.
• Test Reserved Connections—Select this option to test each connection before giving to a client. This may add a slight delay to the request, but it guarantees that the connection is healthy. You must also set a Test Table Name.
• Test Created Connections—Select this option to test each database connection after it is created. This applies to connections created at server startup and when the connection pool is expanded. You must also set a Test Table Name.
• Test Released Connections—Select this option to test connections when they are returned to the connection pool. You must also set a Test Table Name.
• Maximum Connections Made Unavailable—Use this option to limit the number idle connections that the server will test. For example, if you have 10 connections in your connection pool and five are in use, if the server were to begin testing all five connections that are not in use, there would be no connections available to fill a connection request. If you set the Maximum Connections Made Unavailable attribute to 3, there would still be two connections available to fill a connection request.
• Test Table Name—Use this attribute to specify a table name to use in a connection test. You can also specify SQL code to run in place of the standard test by entering SQL followed by a space and the SQL code you want to run as a test. Test Table Name is required to enable any database connection testing.

You should set connection testing attributes so that they best fit your environment.For more details about these attributes, see Attributes.

To enable a database connection testing:

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

• Click the connection pool that you want to configure. A dialog displays in the right pane showing the tabs associated with this instance.

• Click the Configuration tab, then click the Connections tab.

• Click Show to show the advanced connection options.

• Select or specify a value for at least one of the following attributes:

• Test Frequency
• Test Reserved Connections
• Test Created Connections
• Test Released Connections

If you specify a value for Test Frequency, you must also specify a value for Maximum Connections Made Unavailable.

• Specify a value for Test Table Name: either a table that exists in your database or SQL followed by a space and SQL code.

• Click Apply.

Default Test Table Name

When you create a connection pool, the JDBC Connection Pool Assistant automatically sets the Test Table Name attribute for a connection pool based on the DBMS of the JDBC driver that you select. The Test Table Name attribute is used in connection testing which is optionally performed periodically or when you create, reserve, or release a connection, depending on how you configure the connection pool. For database tests to succeed, the database user used to create database connections in the connection pool 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 127-1 Default Test Table Name by DBMS

DBMS	Default Test Table Name (Query)
Cloudscape	SQL SELECT 1
DB2	SQL SELECT COUNT(*) FROM SYSIBM.SYSTABLES
Informix	SQL SELECT COUNT(*) FROM SYSTABLES
Microsoft SQL Server	SQL SELECT COUNT(*) FROM SYSOBJECTS
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 COUNT(*) FROM SYSOBJECTS

 

 

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 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)
• getStatementCacheSize()
• setStatementCacheSize(int cacheSize)

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

This issue occurs consistently when using the WebLogic jDriver for Oracle. The WebLogic jDriver for Oracle converts data types according to Table B-5 in the JDBC specification. This issue may also 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 statement cache size for the connection pool.

Increasing Performance with the PinnedToThread JDBC Connection Pool Property

To minimize the time it takes for an application to reserve a database connection from a connection pool and to eliminate contention between threads for a database connection, you can add thePinnedToThread property in the connection Properties list for the connection pool, and set its value to true. You set Properties on the JDBC Connection Pool —> Configuration —> General tab in the Administration Console. See JDBC Connection Pool --> Configuration --> General. The result in the config.xml file may look like the following:

<JDBCConnectionPool 
        DriverName="com.pointbase.jdbc.jdbcUniversalDriver"
        InitialCapacity="1"
        Name="demoPool"
        Password="{3DES}4oIxnuEUXInhlPfGNdsQ3A=="
        Properties="PinnedToThread=true;user=examples"
        Targets="examplesServer"
        TestConnectionsOnReserve="true" 
        TestTableName="SYSTABLES"
        URL="jdbc:pointbase:server://localhost/demo"
/>

Note: Line breaks added for readability

When PinnedToThread is enabled, WebLogic Server pins a database connection from the connection pool 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 connection pool, WebLogic Server keeps the connection with the execute thread and does not return it to the connection pool. 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 connection pool 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. If an application concurrently reserves more than one connection from the connection pool using the same execute thread, WebLogic Server creates additional database connections and pins them to the thread.

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.

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:

• Maximum Capacity (MaxCapacity in config.xml) is ignored. The number of connections in a connection pool equals the greater of either the initial capacity or the number of connections reserved from the connection pool.
• Shrinking does not apply to connection pools with PinnedToThread enabled because connections are never returned to the connection pool. Effectively, they are always reserved.
• When you Reset a connection pool (see Resetting All Connections in a JDBC Connection Pool), the reset connections from the connection pool are marked as Test Needed. The next time each connection is reserved, WebLogic Server tests the connection and recreates it if necessary. Connections are not tested synchronously when you reset the connection pool. This feature requires that Test Connections on Reserve is enabled and a Test Table Name or query is specified.

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.