Set Oracle connection caching in the appserver

We can elect to configure an Oracle data source to use Oracle's connection caching feature instead of using the appserver's connection pooling. Connection caching for Oracle databases is similar to connection pooling in the appserver.

Currently, Oracle only supports connection caching with data sources that use the oracle.jdbc.pool.OracleDataSource implementation class, instead of the oracle.jdbc.pool.OracleConnectionPoolDataSource or oracle.jdbc.xa.client.OracleXADataSource classes. By default, the Oracle JDBC providers in the appserver are configured to use the oracle.jdbc.pool.OracleConnectionPoolDataSource for non-XA data sources, or oracle.jdbc.xa.client.OracleXADataSource for XA data sources. To enable Oracle connection caching, configure and use a new JDBC provider in the appserver that implements the oracle.jdbc.pool.OracleDataSource class.

Avoid trouble: Oracle connection caching does not support XA.

 

1. Create a data source and user-defined JDBC provider.

   1. Click Resources > JDBC > Data sources

   2. Select a server from the Scope drop-down list.

   3. Click New.

   4. Enter the name and JNDI name for the data source. Click Next.

   5. Create a new JDBC provider. Select Create new JDBC provider, and click Next.

   6. Define the required properties for the JDBC provider.

      Use the following settings:

      • Database type: User-defined

      • Implementation class name: oracle.jdbc.pool.OracleDataSource

      Click Next.

   7. Enter the class path for ojdbc6.jar, and click Next.

   8. For Data store helper class name, enter com.ibm.websphere.rsadapter.Oracle11gDataStoreHelper. Click Next.

   9. Define the security aliases for this data source, and click Next.

   10. Finish the wizard.

   11. Save the configuration changes.

2. Set the data source createdd.

   1. Click the name of the data source. You will be taken to the configuration panel.

   2. Select Custom properties, and create or modify the properties for this data source. Enter or update the following custom properties:

      Name	Value
      disableWASConnectionPooling	true
      connectionCachingEnabled	true
      connectionCacheName	my_cache_name
      removeExistingOracleConnectionPoolIfExists	true Avoid trouble: The removeExistingOracleConnectionPoolIfExists property must be set to true so the appserver will remove any existing Oracle connection pools with an identical name. Otherwise, the Oracle data source will fail the getConnection method if the pool name that is created has a name that is identical to an existing pool. For example, if we run a test connection, the test connection process will create an Oracle connection pool that will prevent the appserver from working properly at run time.
      URL	Oracle_URL

      Avoid trouble: The order in which the custom properties are set is important. This could be an issue, as the appserver passes the properties as a collection and the order is not guaranteed. If we encounter this issue, contact Oracle and reference Oracle bug #6638862.

3. Click Apply or OK.

4. Save the changes to the appserver's configuration.

5. Restart the appserver.

 

Results

Be aware that Oracle will not display a message if the pool creation fails, and a normal connection will be returned instead. We can confirm that the connection pool is created by running the application, and try to issue a test connection. If the Oracle connection pool was created successfully, the test connection will fail with an exception that the Oracle pool name is already used. Therefore, create the data source and JDBC provider at the server scope.

 

Related tasks

Set Oracle Real Application Cluster (RAC) with the appserver
Set two-phase commit distributed transactions with Oracle RAC
Set a JDBC provider and data source