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

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

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

 

Using the JDBC Connection Pool Assistant

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

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

  6. 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 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=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 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=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, change the Password attribute. Note that 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.

 

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

    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 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 the weblogic-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 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):

  1. In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.
  2. 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.
  3. Select the Configuration tab, then select the Descriptor tab.
  4. 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:

  1. In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.
  2. 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.
  3. 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:

  1. In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.
  2. 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.
  3. Select the Deploy tab, which lists each deployed instance of the connection pool and its status.
  4. 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:

  1. In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.
  2. 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.
  3. 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

  1. In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.
  2. 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.
  3. Select the Control tab. A table displays with a list of deployed instances of the connection pool.
  4. 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 of either the initial number of connections or the current number of connections in use.

 

Resetting an Application-Scoped Connection Pool

  1. In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.
  2. 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.
  3. Select the Control tab. A table displays with a list of deployed instances of the connection pool.
  4. 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 database connections in the connection pool, including any connections in use.

 

Clearing the Statement Cache for an Application-Scoped Connection Pool

  1. In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.
  2. 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.
  3. Select the Control tab. A table displays with a list of deployed instances of the connection pool.
  4. 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:

  1. In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.
  2. 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.
  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.

 

Adding a Note to an Application-Scoped Connection Pool

  1. In the left pane, click to expand Deployments, Applications, and the deployed application name to display a list of application components.
  2. 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.
  3. 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 that 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.

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.

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.

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 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.
  • In the data source, make sure that Honor Global Transactions is selected (the default).

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

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.

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.

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.

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.

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=publicDatabaseName=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.

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

 

WebLogic jDriver for Oracle/XA Data Source Properties

Table 127-11 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 127-11. 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.

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 127-12 lists the mapping between Oracle's xa_open string fields and 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.

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.

 

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

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.

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:

  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.

 

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

  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 one target, 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:

  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:

 

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

 

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 -1, a connection request will wait indefinitely.

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

  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 configure. 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. Click Show to show the advanced connection options.
  5. In Connection Reserve Timeout, enter the number of seconds that connection requests can wait for a connection.
  6. 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.
  7. 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

  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 configure. 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. Click Show to show the advanced connection options.
  5. 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:

  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. 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 SQL followed 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:

  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 configure. 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. Click Show to show the advanced connection options.
  5. 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:

  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 configure. 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. Click Show to show the advanced connection options.
  5. 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, also specify a value for Maximum Connections Made Unavailable.

  6. Specify a value for Test Table Name: either a table that exists in your database or SQL followed by a space and SQL code.
  7. 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).

DBMS

Default Test Table Name

Cloudscape SELECT 1
DB2 SELECT COUNT(*) FROM SYSIBM.SYSTABLES
Informix SELECT COUNT(*) FROM SYSTABLES
Microsoft SQL Server SELECT COUNT(*) FROM SYSOBJECTS
MySQL SELECT 1
Oracle SELECT 1 FROM DUAL
PointBase SELECT COUNT(*) FROM SYSTABLES
PostgreSQL SELECT 1
Progress SELECT COUNT(*) FROM SYSTABLES
Sybase 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:

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)

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, 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 consider its limitations before you decide to use it. Please note the following restrictions when using the statement cache.

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

 

Calling a Stored Statement After a Database Change May Cause Errors

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

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

These limitations depend on the behavior of your DBMS.

 

Using setNull In a Prepared Statement

When using the WebLogic jDriver for Oracle to connect to the database, if you cache a prepared statement that uses a setNull bind variable, 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.

Skip navigation bar  Back to Top Previous Next