1. Administering the WebLogic Persistent Store
  2. Using a JDBC Store

5 Using a JDBC Store

This chapter explains how to configure and monitor the WebLogic Server persistent store, which provides a built-in, high-performance storage solution for WebLogic Server subsystems and services that require persistence.

Creating JDBC-accessible Stores

The following sections provide information on how to configure and use JDBC-accessible stores:

  • JDBC TLog Stores: to persist transaction logs (TLOGs) in a database. See Using a JDBC TLog Store.

  • JDBC Stores: to persist WebLogic Server instance services and subsystem information, excluding TLOGs, in a database. See Using a JDBC Store.

Using a JDBC TLog Store

You can configure a JDBC TLOG store to persist transaction logs to a database, which provides the following benefits:

  • Leverages replication and HA characteristics of the underlying database.

  • Simplifies disaster recovery by allowing the easy synchronization of the state of the database and TLOGs.

  • Improved Transaction Recovery service migration as the transaction logs to do not need to be migrated (copied) to a new location.

Main Steps for Configuring a JDBC TLOG Store

The main steps for creating a JDBC TLOG store are as follows:

  1. Create a JDBC data source, GridLink data source, or multi data source to interface with the JDBC store. See Choosing a Data Source.
  2. Create a JDBC TLOG store and associate it with the JDBC data source, GridLink data source, or multi data source created in Step 1. See Configure the Transaction Log Store in the Oracle WebLogic Server Administration Console Online Help.
  3. Optional. It is highly recommended that you configure the Prefix option to a unique value for each configured JDBC TLOG store.
  4. For high availability, make your data source available to backup servers. See Server Migration when using a JDBC TLOG Store.
Choosing a Data Source

You can choose one of the following data source types, depending on your WebLogic Server license and application needs:

Example of a JDBC TLOG Store

Here's an example of how a JDBC TLOG store may look in the configuration file, using the JDBC data source MyDataSource, and with a logical name specified: 

<server>
  <transaction-log-jdbc-store>
    <data-source>MyDataSource</data-source>
    <prefix-name>TLOG_MS1</prefix-name>
    <create-table-ddl-file>myDDL/myCreateTable.sql</create-table-ddl-file>
    <max-retry-seconds-before-tlog-fail>120</max-retry-seconds-before-tlog-fail>
  </transaction-log-jdbc-store>
</server>

Table 5-1 describes the JDBC TLOG store configuration parameters that can be modified.

Table 5-1 JDBC TLOG Store Configuration Options

Option Required What it Does

Prefix Name

No

The prefix for the JDBC store's table is generally entered in the following format: [[[catalog.]schema.]prefix]

When using multiple JDBC stores, it is required to set this option to a unique value for each configured JDBC store. When no prefix is specified, the JDBC store table name is simply WLStore and the database implicitly determines the schema according the current user of the JDBC connection. Also, two JDBC stores cannot share the same database table. See Using Prefixes with a JDBC Store.

Modifying an existing JDBC store's prefix does not necessarily require a server restart, as described in Modifying Custom Persistent Store Parameters.

Create Table from DDL File

No

Optionally used with supported DDL (data definition language) files to create the JDBC store's database table (WLStore). This option is ignored when the JDBC store's database table already exists. See Creating a JDBC Store Table Using Default and Custom DDL Files.

Deletes Per Batch Maximum

Default is 20.

The maximum number of table rows that are deleted per database call.

Inserts Per Batch Maximum

Default is 20.

The maximum number of table rows that are inserted per database call.

Deletes Per Statement Maximum

Default is 20

The maximum number of table rows that are deleted per database call.

MaxRetrySecondsBeforeTLogFail

Default is 300.

The maximum amount of time, in seconds, WebLogic Server tries to recover from a JDBC TLog store failure. If store remains unusable after this period, WebLogic Server set the health state to HEALTH_FAILED. A value of 0 indicates WebLogic Server does not conduct a retry and and immediately sets the health state as HEALTH_FAILED.

MaxRetrySecondsBeforeTXRollback

Default is 60.

The maximum amount of time, in seconds, WebLogic Server waits before trying to recover from a JDBC TLog store failure while processing a transaction. If store remains unusable after this amount of time, WebLogic Server rolls back the affected transaction. A value of 0 indicates WebLogic Server does not conduct a retry and rolls back the transaction immediately. The practical maximum value is a value less than the current value of MaxRetrySecondsBeforeTLogFail.

RetryIntervalSeconds

Default is 5.

The amount of time, in seconds, WebLogic Server waits before attempting to verify the health of the TLOG store after a store failure has occurred.

N/A

1000

The amount of time, in milliseconds, a JDBC Store reconnect retry or a TLOG-in-DB Store attempts to re-establish a connection to a database, before the Store shuts down, and all the operations waiting on the Store are unblocked.The minimum value that can be configured through the ReconnectRetryPeriodMillis is 200, and the maximum value is 300000. For more information about JDBC Store reconnect retry, see Configuring JDBC Store Reconnect Retry.

ReconnectRetryIntervalMillis

200

The amount of time in milliseconds, between reconnect attempts, during the connection retry period. The minimum value that can be configured through the ReconnectRetryIntervalMillis is 100, and the maximum value is 10000. See Configuring JDBC Store Reconnect Retry.

For instructions on configuring a JDBC TLOG store using the WebLogic Server Administration Console, see Configure the Transaction Log Store in the Oracle WebLogic Server Administration Console Online Help.

Configuration Guidelines

The following section provides guidelines for configuring JDBC TLOG stores.

  • Only globally-scoped (not application-scoped) data sources can be targeted to a JDBC TLOG store.

  • Only one JDBC TLOG store can be configured per WebLogic Server. Conversely, multiple WebLogic Servers can not share a JDBC TLOG store.

  • You must configure a JDBC TLOG store. The default is to persist TLOG information to the server's default persistent store.

  • You cannot use a data source that is configured to use an XA JDBC driver or is configured to support global transactions. Use a non-XA data source.

  • For general rules on JDBC-accessible stores, see Guidelines for Configuring a JDBC Store.

Additional Considerations

The following section provides additional information on JDBC TLOG store behavior and limitations:

  • The database used to store the TLOG information must be available at server startup. If the database is not available, the WebLogic Server instance will fail to boot.

  • Only the JTA sub-system can use the JDBC TLOG store to persist information about committed transactions coordinated by the server that may not have been completed. No other systems can access the JDBC TLOG store.

  • Using a JDBC TLOG store does not change LLR behavior. A JDBC TLOG store can be used with or without LLR. When used in tandem with LLR transactions, the transaction committing information is stored in a LLR table but the checkpoint records and heuristic logs are stored in the JDBC TLOG store.

  • If the TLOG store is changed from one store type to another or from one location to another, the change takes effect only after reboot and all pending transactions in the old store are not be copied to the new store. You must ensure there are no pending transactions before changing the TLOG store type or location.

  • If the JDBC TLOG store becomes unavailable, the JTA health state transitions to FAILED and any global transactions will fail. In turn, the server life-cycle changes to FAILED. The JTA Transaction Recovery System then attempts to recover from transient runtime errors if possible and resolves any in-doubt transactions. See Server Migration when using a JDBC TLOG Store.

  • If the database used to store TLOG is corrupted and can not be restored, than all pending transaction information is lost.

  • If database tables or rows used by the JDBC TLOG store are locked for some reason in the database, the database administrator must resolve these locks manually. Otherwise, the JTA subsystem is blocked and will be suspended until the lock(s) are released, or encounters an exception due to lock. The JTA subsystem will remain unable to operate correctly until the lock(s) are released or the value of MaxRetrySecondsBeforeTLOGFail is exceeded.

    Note:

    Different databases have different features for locked local transactions. Some databases may have trouble resolving database locks in a timely manner. You may need to contact your database administrator for more information on basic row locking issues that may occur in your application environment.

Server Migration when using a JDBC TLOG Store

WebLogic Server supports both manual and automatic migration of the Transaction Recovery Service when using a JDBC TLOG store. The data source used by the JDBC TLOG store must be targeted on both the primary server instance and a backup server instance. Oracle recommends targeting the data source to all the server instances of a cluster. See Transaction Recovery After a Server Fails in Developing JTA Applications for Oracle WebLogic Server.

Monitoring a JDBC TLOG Store

You can monitor statistics for Transaction Log Store statistics and for each open store connection. For general information on how to monitor persistent stores, see Monitoring the WebLogic Persistent Store.

How to Monitor the JDBC TLOG Store Health State

When you configure WebLogic Server to use a JDBC TLOG store, the store is registered with the Health system as a non-critical subsystem using a name with the following pattern:

PersistentStore.TLOG_servername

where servername is the name of the WebLogic Server instance hosting the primary TLOG store.

You can monitor the JDBC TLOG store health state in the WebLogic Server Administration Console, see Monitor server health in Oracle WebLogic Server Administration Console Online Help.

How to Monitor Transaction Log Store Statistics

You can monitor Transaction Log Store statistics in the WebLogic Server Administration Console, see View transaction log statistics for a server in Oracle WebLogic Server Administration Console Online Help.

How to Monitor Transaction Log Store Connections

You can monitor Transaction Log Store connection statistics in the WebLogic Server Administration Console, see View statistics for TLOG store connections in Oracle WebLogic Server Administration Console Online Help.

Security Considerations

Properly secure your application environment, including the JDBC TLOG store table. Failure to do so may allow a process to:

  • Delete information, maliciously or unintentionally. Such a deletion can cause transaction information to be lost and cause affected global transactions to be completed heuristically.

  • Modify information, maliciously or unintentionally. Such modification can cause unexpected behavior.

  • Read confidential transaction information, such as when transaction starts and what resources are involved.

  • Access the database instance or database machine.

  • Access the network between JTA and the database, potentially intercepting, viewing, or modifying data.

See Secure File Store Data.

Using a JDBC Store

The following sections provide an example of a JDBC store, and information about creating a database table for a JDBC store, either using an existing DDL, a custom DDL, and using Oracle blob record columns in a DDL file.

To create a JDBC store, you can directly modify the default JDBCStoreMBean parameters. For instructions on using the WebLogic Server Administration Console to create a JDBC store, see Create JDBC Stores in the Oracle WebLogic Server Administration Console Online Help.

For configuration guidelines on using prefixes with JDBC stores and recommended JDBC data source settings, see Guidelines for Configuring a JDBC Store.

Main Steps for Configuring a JDBC Store

The main steps for creating a JDBC store are as follows:

  1. Create a JDBC data source or multi data source to interface with the JDBC store.
  2. Create a JDBC store and associate it with the JDBC data source or multi data source.
  3. It is highly recommended that you configure the Prefix option to a unique value for each configured JDBC store table.
  4. Associate the JDBC store with the subsystem(s) that will be using it, such as:

    • For JMS servers, select the JDBC store on the General Configuration page.

    • For Store-and-Forward agents, select the JDBC store on the General Configuration page.

    • For a Path Service, select the custom file store on the General Configuration page.

Example of a JDBC Store

Here's an example of how a JDBC store may look in the configuration file, using the JDBC data source MyDataSource, and with a logical name specified: 

<jdbc-store>
  <name>SampleJDBCStore</name>
  <target>yourserver</target>
  <data-source>MyDataSource</data-source>
  <logical-name>Baz</logical-name>
</jdbc-store>

Table 5-2 describes the JDBC store configuration parameters that can be modified.

Table 5-2 JDBC Store Configuration Options

Option Required What It Does

Name

Yes

The name of the JDBC store, which must be unique across all stores in the domain.

Targets

Yes

The server instance, cluster, or migratable target where a JDBC store is targeted. Multiple subsystems can share the same JDBC store, as long as they are all targeted to the same server instance or migratable target.

Note:

  • When using a cluster to host a JMS Server, you must target the JDBC store to the same cluster used by the JMS Server. See Configuring Dynamic Clustered JMS in Administering JMS Resources for Oracle WebLogic Server.

  • When using migratable targets for JMS services, you must target the JDBC store to the same migratable target used by the JMS service. See Service Migration in Administering Clusters for Oracle WebLogic Server.

Data Source

Yes

The JDBC data source or multi data source used by this JDBC store to access the store's database table (WLStore). This data source or multi data source must be targeted to the same target as the JDBC store.

Note: The JDBC store must use a JDBC data source that uses a non-XA JDBC driver and has Supports Global Transactions disabled. This limitation does not remove the XA capabilities of layered subsystems that use JDBC stores. For example, WebLogic JMS is fully XA-capable regardless of whether it uses a file store or any JDBC store.

Prefix Name

No

The prefix for the JDBC store's table is generally entered in the following format: [[[catalog.]schema.]prefix]

When using multiple JDBC stores, it is required to set this option to a unique value for each configured JDBC store. When no prefix is specified, the JDBC store table name is simply WLStore and the database implicitly determines the schema according the current user of the JDBC connection. Also, two JDBC stores cannot share the same database table. See Using Prefixes with a JDBC Store.

Modifying an existing JDBC store's prefix does not necessarily require a server restart, as described in Modifying Custom Persistent Store Parameters.

Logical Name

No

Optionally used with WebLogic Server subsystems, like EJBs, when deploying a module to an entire cluster, but also require a different physical store on each server instance in the cluster. In such a configuration, each physical store would have its own name, but all the persistent stores would share the same logical name.

Create Table from DDL File

No

Optionally used with supported DDL (data definition language) files to create the JDBC store's database table (WLStore). This option is ignored when the JDBC store's database table already exists. See Creating a JDBC Store Table Using Default and Custom DDL Files.

For instructions on configuring a JDBC store using the WebLogic Server Administration Console, see Create JDBC Stores in the Oracle WebLogic Server Administration Console Online Help.

Supported JDBC Drivers

When using a JDBC store, the backing database can be any database that is accessible through a JDBC driver. WebLogic Server detects some drivers for supported databases.

For each of these databases, there are corresponding DDL (data definition language) files within the ORACLE_HOME\wlserver\modules\com.bea.core.store.jdbc_1.0.0.0.jar file, in the weblogic/store/io/jdbc/ddl directory, where ORACLE_HOME is the top-level installation directory of your WebLogic Server installation.

Table 5-3 Supported JDBC Drivers and Corresponding DDL Files

Database DDL Files

IBM DB2

db2.ddl db2v6.ddl

Informix

informix.ddl

Microsoft SQL (MSSQL) Server

mssql.ddl

MySQL

mysql.ddl

Oracle

oracle.ddl oracle_blob.ddl oracle_blob_securefile.ddl

Sybase

sysbase.ddl

The DDL files are actually text files containing the SQL commands (terminated by semicolons) that create the JDBC store's database table (WLStore). Therefore, if you are using a database that is not included in this list, you can copy and edit any one of the existing DDL files to suit your specific database, as described in Creating a JDBC Store Table Using a Custom DDL File.

Creating a JDBC Store Table Using Default and Custom DDL Files

The JDBC Store Configuration page provides an optional Create Table from DDL File option, through which you can access a pre-configured DDL file that is used to create the JDBC store's backing table (WLStore). This option is ignored when the JDBC store's backing table already exists. It is mainly used to specify a custom DDL file created for an unsupported database, or when upgrading JMS JDBC store tables from a prior release to a current JDBC Store table.

If a DDL file name is not specified in the Create Table from DDL File field, and the JDBC store detects that its backing table does not already exist, the JDBC store automatically creates the table by executing a pre-configured DDL file that is specific to the database vendor (see Supported JDBC Drivers).

If a DDL file name is specified in the Create Table from DDL File field, and the JDBC store detects that its backing table does not already exist, the JDBC store searches for the specified DDL file in the file path first, and then, if not found, searches for the DDL file in the CLASSPATH. Once found, the SQL within the DDL file is executed to create the JDBC store's backing table. If the configured file is not found and the table doesn't already exist, the JDBC store will fail to boot.

Creating a JDBC Store Table Using a Custom DDL File

To use a different database from those listed in Supported JDBC Drivers, you can copy and edit any one of the existing DDL template files to suit your specific database.

  1. Use the JAR utility supplied with the JDK to extract the DDL files to the /weblogic/store/io/jdbc/ddl directory using the following command:
    jar xf com.bea.core.store.jdbc_1.0.0.0.jar /weblogic/store/io/jdbc/ddl

    Note:

    If you omit the weblogic/store/io/jdbc/ddl parameter, the entire jar file is extracted.

  2. Edit the DDL file for your database. An SQL command can span several lines and is terminated with a semicolon (;). Lines beginning with pound signs (#) are comments.
  3. Save your changes and rename the new DDL appropriately (for example, mydatabase.ddl)
  4. Create a JDBC store, as explained in Create JDBC Stores in the Oracle WebLogic Server Administration Console Online Help.
  5. Use the Create Table from DDL File option on the General Configuration page to specify your custom DDL file (for example, /mydatabase.ddl).

    Note:

    On Windows systems, for full path names always include the drive letter.

Enabling Oracle BLOB Record Columns

For Oracle databases, you can use the oracle_blob.ddl or oracle_blob_securefile.ddl file to create a JDBC store table with a BLOB record column type rather than the default LONG RAW record column type. The oracle_blob.ddl is used to create Oracle basic file BLOBs and the oracle_blob_securefile.ddl file is used to create Oracle secure file BLOBs. Both files types are pre-configured and supplied in the WebLogic CLASSPATH, as described in Supported JDBC Drivers.

Oracle Database 11g Release 2 includes a zero copy I/O performance enhancement for Secure Files and a logical cache for BLOBs. Use of these enhancements can improve throughput with a JDBC store when message sizes are large and when network connections to the database are slow. The Oracle LONG RAW datatype is typically better performing than BLOBS when using a fast connection to the database.

Note:

If you need to preserve data already in a Oracle LONG RAW column, but still want to switch the column to BLOB, do not use this method. Instead, consult the Oracle documentation for the SQL ALTER TABLE command.

To use the Oracle BLOB DDL with a JDBC store:

  1. Shut down the server instance that uses the JDBC store.

  2. Delete the current JDBC table, as explained in Managing JDBC Store Tables.

  3. Reboot the server instance.

  4. Create a new JDBC store, as explained in Create JDBC Stores in the Oracle WebLogic Server Administration Console Online Help.

  5. In the Create Table from DDL File field on the General Configuration page, enter the location of:

    • the oracle_blob.ddl file as: /weblogic/store/io/jdbc/ddl/oracle_blob.ddl

    • the oracle_blob_securefile.ddl file as: /weblogic/store/io/jdbc/ddl/oracle_blob_securefile.ddl

  6. Click Finish to create the JDBC store's backing table.

When using Oracle BLOBS, you may be able to improve performance by tuning the ThreeStepThreshold value.

When the JDBC store schema contains an Oracle BLOB column (basic file or secure file), the JDBC store populates the BLOB data using one of the following implementations based on the size of the BLOB data:

  • The JDBC store inserts a row with BLOB data directly into the store table in a single step. Because only a single step is involved, JDBC batch insert is also adopted and performs best when the data size is small. This implementation is used when the BLOB data to be inserted is less than or equal to the value of the ThreeStepThreshold.

  • The JDBC store inserts a row with BLOB data into the store table in three steps using the Oracle LOB API. This implementation provides better performance when the data size is large. This implementation is used when the BLOB data to be inserted is greater than the value of the ThreeStepThreshold.

The default value of ThreeStepThreshold is 200K.

Managing JDBC Store Tables

The JDBC utils.Schema utility allows you to regenerate a new JDBC store database table (WLStore) by deleting the existing version. Running this utility is usually not necessary, since WebLogic Server automatically creates this table for you. However, if your existing JDBC store database table somehow becomes corrupted, you can delete it using the utils.Schema utility.

The utils.Schema utility is a Java program that takes command-line arguments to specify the following:

  • JDBC driver

  • Database connection information

  • Name of a file containing the SQL Data Definition Language (DDL) commands that create the database table

Using the utils.Schema Utility to Delete a JDBC Store Table

Enter the utils.Schema command, as follows: 

$ java utils.Schema url JDBC_driver [options] DDL_file

Note:

To execute utils.Schema, your CLASSPATH must contain the weblogic.jar file.

Table 5-4 lists the utils.Schema command-line arguments.

Table 5-4 Command-line arguments for utils.Schema

Argument Description

url

Database connection URL. This value must be a colon-separated URL as defined by the JDBC specification.

JDBC_driver

Full package name of the JDBC Driver class.

options

Optional command options.

If required by the database, you can specify:

  • The user name and password as follows:

    -u <username> -p <password>

  • The Domain Name Server (DNS) name of the JDBC database server as follows:

    -s <dbserver>

You can also specify the -verbose option, which causes utils.Schema to echo SQL commands as they are executed.

DDL_file

The full pathname of the DDL text file containing the SQL commands that you want to execute. For more information, see Supported JDBC Drivers.

For example, the following command deletes a JDBC table named MYWLStore in an Oracle server named DEMO, with the user name user1 and password foobar: 

$ echo "drop MYWLStore;" > drop.ddl

$ java utils.Schema 
jdbc:weblogic:oracle:DEMO \
weblogic.jdbc.oci.Driver -u user1 -p foobar -verbose \
drop.ddl

Configuring JDBC Store Reconnect Retry

The JDBC Store reconnect retry period indicates the time period when a WebLogic JDBC Store or a TLOG in-DB Store retries to connect to a database, before the Store shuts down and requires a restart. You can configure the Store retry period through Command line system properties, MBeans and WLST.

Using WLST and JMX MBeans

The JDBC Store reconnect retry period controls the length of the time period required by a JDBC Store reconnect retry or a TLOG-in-DB Store to retry database requests before a Store shuts down, and requires a restart. The JDBC Store reconnect retry interval controls the length of the time in milliseconds between reconnect attempts during the connection retry period. You can configure the JDBC Store reconnect retry period and interval by using the ReconnectRetryPeriodMillis and ReconnectRetryIntervalMillis attributes available in the JDBCStoreMBean and TransactionLogJDBCStoreMBean. For more information about the Retry attributes, see MBean Reference for Oracle WebLogic Server.

Using Command Line System Properties

You can configure the retry period and interval for custom JDBC Store reconnect retry and TLOG-in-DB Stores by setting system properties on the WebLogic Server command line. The -DwebLogic.store.jdbc.RecnnectRetryPeriodMillis=<millis> and -Dweblogic.store.jdbc.ReconnectRetryIntervalMillis=<millis> option specifies the JDBC Store reconnect retry in period and interval available in the WebLogic Server domain.

The -Dweblogic.store.jdbc.ReconnectRetryPeriodMillis=<millis> system property overrides legacy properties-Dweblogic.store.jdbc.IORetryDelayMillis=<millis> or -Dweblogic.jms.store.JMSJDBCIORetryDelay=<seconds>, if they are set on the same command line. If the retry period property is not set, then the legacy properties will take effect.

Deprecation Note: All JDBC Store reconnect retry command line properties are deprecated as of 12.2.1.0 and may be removed in a future release. In 12.2.1.0 and later. Oracle recommends setting these values through MBean attributes instead.

Important Tuning Considerations for Reconnect Retry

It is important to consider the following when configuring a JDBC Store reconnect retry period:

  • The total elapsed time before a Store fails may sometimes be more than double the configured retry period.

  • It is advisable to configure more tolerant retry periods of up to 15 seconds or more instead of the maximum, since a retry period that is set to too long may lock up WebLogic Server applications and client applications during this period.

  • JDBC Store reconnect retry tuning should be configured to align with transaction tuning.

    • Consider tuning JTA transaction time outs to be higher than the retry period so that the application transactions that involve a JDBC Store do not time out waiting for a JDBC Store to successfully recover from a database failure. The default transaction time out for a domain is 30 seconds and is tunable via the TimeoutSeconds attribute on the JTAMBean. In addition, transaction time outs are tunable on a per application basis.

    • WebLogic's transaction system will temporarily stop allowing a JDBC Store to participate in transactions if the JDBC Store is unresponsive for more than the JTAMBean MaxXACallMillis attribute (default is 1200000 millis/two minutes). Once JTA decides a Store is unresponsive, it will not attempt to allow the Store to participate in transactions until after MaxResourceUnavailableMillis has passed (default is 1800000 millis/30minutes). It is therefore advisable to ensure that MaxXACallMillis is at least twice the JDBC Store reconnect retry period.

    • If the retry period is configured on a TLOG-in-DB Store, it should be set to less than half of the TransactionLogJDBCStoreMBean MaxRetrySecondsBeforeTLOGFail setting. otherwise, the TLOG-in Store may delay failure longer than the TLOGFail setting.

  • A JDBC Store reconnect retry period is configured in milliseconds while some transaction settings are configured in seconds.

Configuring a JDBC Store Connection Caching Policy

By default, a WebLogic JDBC Store obtains two JDBC connections from its Data Source, and caches these connections throughout a Store's lifetime. You can optionally tune the JDBC Store's Connection Caching Policy to reduce the number of cached JDBC connections.

Using WLST and JMX MBeans

The JDBC Store Connection Caching Policy setting controls how many JDBC connections it caches. The Connection Caching policy can be configured by using the ConnectionCachingPolicy attribute available in the JDBCStoreMBean and TransactionLogJDBCStoreMBean. The valid values for ConnectionCachingPolicy attribute are - DEFAULT, MINIMAL and NONE. For more information about the valid values, see MBean Reference for Oracle WebLogic Server and Table 5-5.

Note:

The NONE policy requires additional tuning to avoid deadlocking a server. For more information about tuning the NONE JDBC Store Connection Caching Policy, see Important Tuning Considerations for the NONE Connection Caching Policy.
Using a Command Line Parameter

You can configure the JDBC Store Connection Caching Policy by setting a system property on the WebLogic Server command line. The -Dweblogic.store.jdbc.ConnectionCachingPolicy option specifies the WebLogic JDBC Store Connections available in the WebLogic Server domain. For more information about the valid values that can be set for this Policy, see Table 5-5.

Note:

JDBC Store Connection Caching Behavior

A JDBC Store's Connection Caching behavior is determined by the combination of its Connection Caching Policy setting and Worker Count setting.

Table 5-5 JDBC Store Connection Caching Policy behavior

Connection Caching Policy Cached Connections when Worker count=1 Cached Connections when WorkerCount>1 Description

DEFAULT

2

2+ Worker Count

By default, each JDBC Store instance caches two database connections for the life of store.

If the JDBC Store worker-count is configured to be more than two, the store opens an additional connection for each worker.

MINIMAL

1

1+WorkerCount

Each JDBC store instance caches a single database connection for the life of the store. If the JDBC worker- count is configured to be two or higher, the store opens one connection for each worker.

The performance of this setting may be less than DEFAULT for low concurrency messaging scenarios.

NONE

0

N/A

Each JDBC store instance obtains a connection from its data source as needed, and releases the connection when finished.

The NONE setting is not compatible with a JDBC Store worker-count of two or more, and will result in a configuration validation exception. The performance of this setting will be lesser than DEFAULT or MINIMAL.

Warning: To avoid the risk of dead-locking a WebLogic Server, Oracle strongly recommends configuring a dedicated data source for NONE connection-caching-policy JDBC stores.

Important Tuning Considerations for the NONE Connection Caching Policy

It is important to consider the following tuning considerations when a JDBC Store Connection Caching Policy is set to NONE:

  • Use a dedicated Data Source to avoid deadlocks - It is strongly advised to configure JDBC Stores to use a dedicated Data Source when the JDBC Store Connection Caching Policy is set to NONE. ANONE policy JDBC Store may deadlock a server or eventually fail if it is configured to share the Data Source with applications or non-store services.

    For example, consider an application that performs the following steps:

    1. Obtains a Data Source connection.

    2. Sends a persistent JMS message through a JMS Server with a NONE policy, JDBC Store that uses the same Data Source.

    3. Closes the Data Source connection.

  • It is possible that step 1 can obtain the last available connection in the Data Source connection pool, and therefore in step 2, the JMS send call will block until the NONE policy JDBC Store is able to get a connection from the same pool. This is a problem because step 2 will potentially never get a connection no matter how long it waits since it is possible that all other applications are also blocked in step 2 (and therefore no application can get to step 3 in order to free up a connection). This problem in turn can cause a server or client to have many stuck threads and/or cause a JDBC Store to ultimately shutdown once it waits too long to try and get a connection.

  • Tune a large enough Data Source connection pool - A JDBC Store uses multiple concurrent connections when its dependent services (such as JMS) first initialize. Hence, the Data Source pool must be configured so that it can grow somewhat larger than the number of JDBC Stores that are using the pool.

  • Enable and tune Data Source connection testing - Enabling Data Source connection testing helps provide JDBC Store resiliency during database access failures. But, note that if performance is a concern, then frequent Data Source connection testing should be avoided, since it lowers performance of a NONE policy JDBC Store. In general, it is advisable to enable the Data Source Test Connection on Reserve setting, and set the Data Source Seconds to Trust and Idle Pool Connection value higher than zero, and lower than the JDBC Store Connection Retry Interval Millis value. See Connection Testing Options for a Data Source in Administering JDBC Data Sources for Oracle WebLogic Server.

  • Monitor and tune Prepared Statement Caching performance - A JDBC Store configured with NONE may yield poor performance if its Data Source or JDBC driver Prepared Statement Cache size is too small. To check if cache misses are lowering performance, monitor your prepared statement cache activity when under load. This monitoring should show frequent cache hits and few cache misses, but if you see many cache misses then increase your cache size.

  • Monitor Oracle RAC with GridLink performance and potentially tune accordingly. If a NONE policy JDBC Store yields poor performance in comparison to other policies when using Oracle RAC with a GridLink driver, the potential workarounds are:

    Note:

    The NONE policy may yield measurably lower performance than the MINIMAL or DEFAULT policies even if all of the above considerations are carefully followed.

Guidelines for Configuring a JDBC Store

The following sections provide guidelines for using JDBC store prefixes, recommended WebLogic JDBC data source settings for JDBC stores, and handling JMS transactions with JDBC stores.

Using Prefixes with a JDBC Store

The JDBC store database contains a database table, named WLStore, that is generated automatically and is used internally by WebLogic Server. The JDBC store provides an optional Prefix Name parameter, which can be used to provide more precise access to the database table.

It is always a best practice to configure a prefix for the JDBC WLStore table name, especially when:

  • The database requires fully-qualified names. (You should verify this with your database administrator.)

  • There is more than one JDBC store instance sharing a database, since no two JDBC stores can share the same table.

  • There are many tables in the database. Setting the prefix reduces the number of tables the JDBC store must search through to find its table during boot.

JDBC Store Table Rules

To avoid potential data loss, follow these rules:

  • Each JDBC store table name must be unique.

  • If multiple JDBC stores share a table, the behavior is undefined and data loss is likely.

  • There is no procedure for combining two database tables into a single table.

Prefix Name Format Guidelines

For most databases, the Prefix Name option for the JDBC store's backing database table should be set in the following format for each configured JDBC store, which will result in a valid table name when prepended to the JDBC store table name:

[[[catalog.]schema.]prefix]

Note that each period in the [[[catalog.]schema.]prefix] format is significant. Generally, catalog identifies the set of system tables being referenced by the DBMS, and schema generally corresponds to ID of the table owner (username). When no prefix is specified, the JDBC store table name is simply WLStore and the database implicitly determines the schema according the current user of the JDBC connection.

For example, in a production database, the database administrator could maintain a unique table for the Sales department, as follows:

[[[Production.]JMSAdmin.]Sales]

The resulting table will be created in the Production catalog, under the JMSAdmin schema, and will be named SalesWLStore.

For some DBMS vendors, such as Oracle, there is no catalog to set or choose, so the format simplifies to [[schema.]prefix]. For more information, refer to your DBMS documentation for instructions on fully-qualified table names, but note that the syntax specified by the DBMS may differ from the format required for this option.

Caution:

If the Prefix Name setting is changed, but the WLStore database table already exists in the database, take care to preserve existing table data. In this case, the existing database table must be renamed by a database administrator to match the new configured table name.

Recommended JDBC Data Source Settings for JDBC Stores

The following settings are recommended when you use a JDBC data source or multi data source for JDBC stores.

Automatic Reconnection to Failed Databases

WebLogic Server provides robust JDBC data sources that can automatically reconnect to failed databases after they come back online, without requiring you to restart WebLogic Server. To take advantage of this capability, and make your use of JDBC stores more robust, configure the following options on the JDBC data source associated with the JDBC store:

TestConnectionsOnReserve="true"
TestTableName="SYSTABLES"
ConnectionCreationRetryFrequencySeconds="600"

For more information about JDBC default Test Table Names, see Connection Testing Options for a Data Source in the Administering JDBC Data Sources for Oracle WebLogic Server. For more information about setting the number of database reconnection attempts, see the Enabling Connection Creation Retries section in Administering JDBC Data Sources for Oracle WebLogic Server.

Required Setting for Oracle DB2 Type 4 JDBC Drivers

For data sources used as a JDBC store that use the Oracle Type 4 JDBC driver for DB2, the BatchPerformanceWorkaround property must be set to "true" due to internal JMS batching requirements.

Handling JMS Transactions with JDBC Stores

The JDBC store must use a JDBC data source that uses a non-XA JDBC driver and has Supports Global Transactions disabled. This limitation does not remove the XA capabilities of layered subsystems that use JDBC stores. For example, WebLogic JMS is fully XA-capable regardless of whether it uses a file store or any JDBC store.

Because the JDBC store implements the XAResource interface, it acts as it's own resource manager and handles the transactions above the JDBC driver level. That is, the store itself implements the XAResource and handles the transactions without depending on the database (even when the messages are stored in the database).

This means that whenever you are using a JDBC store and a database (even if it is the same database where the JMS messages are stored), then it is two-phase commit transaction.

For more information about using JMS transactions with a JDBC store, see Using Transactions with WebLogic JMS in Developing JMS Applications for Oracle WebLogic Server.

From a performance perspective, you may also boost your performance as follows:

  • Ensure that the JDBC data source used for the database work exists on the same server instance as the JMS destination—the transaction will still be two-phase, but it will be handled with less network overhead.

  • Use file stores rather than JDBC stores.

  • Configure multiple services to share the same store if they will commonly be invoked within the same transaction.

  • If an application directly performs database operations in addition to invoking store services (such as JMS) within the same transaction, consider using a JDBC data source with Logging Last Resource (LLR) enabled for the database operations.

    With the LLR optimization, the transaction will follow the two-phase commit protocol, but the database operations will be handled in a single local transaction, which may improve overall transaction performance. For more information on using the LLR optimization, see Understanding the Logging Last Resource Transaction Option in Administering JDBC Data Sources for Oracle WebLogic Server.

Enabling I/O Multithreading for JDBC Stores

Under heavy JDBC store I/O loads, you can improve performance by configuring a JDBC store to use multiple JDBC connections to concurrently process I/O operations.

Note:

Enabling I/O multithreading under light loads may actually reduce performance. Oracle recommends that you tune your applications appropriately.

To enable I/O multithreading, set the Worker Count attribute to an integer value greater than 1. The default value of this configuration property is 1 and disables this option. The Worker Count attribute specifies the number of worker threads the JDBC store uses to process store I/O. Each worker thread acquires one JDBC connection from the configured data source pool when the store is opened. In many cases, benefits of multithreading tends to decrease after 4 concurrent threads. When using a slow connection to the database, multithreading may not improve performance.

Note:

If you set the Worker Count to a value where there are not enough connections available in the connection pool, the JDBC store will fail to open.

You can tune the workload for each worker thread by changing the value of the Worker Preferred Batch Size attribute. Increasing the value of this attribute incrementally increases the workload assigned to each worker thread. The workload consists of store I/O requests, which are grouped and pushed to each JDBC worker thread for processing. If the size of individual I/O requests is commonly very large (for example, requests to store 1 MB JMS messages), then tune the value of Worker Preferred Batch Size to a smaller value for better performance.

Rebuilding the Store Table Index for an Oracle Database

When I/O multithreading is enabled, multiple JDBC connections are used to concurrently process store I/O operations which can result in database resource contention. To reduce contention on Oracle databases, Oracle recommends rebuilding the primary key index into a reverse key index when I/O multithreading is used. If you use and then disable I/O multithreading, Oracle recommends rebuilding the primary key index as a non-reverse index. For more information on reverse key indexes, see Indexes and Index-Organized Tables in Oracle Database Concepts.

Use the following basic steps to rebuild the Store table index for Oracle database:

  1. Login to the Oracle database under the Store schema name. The Store schema name may or may not be the same as the data source user name.

  2. Use the PL/SQL script found in Build a Reverse Index for an Oracle Database or Build a Non-Reverse Index for an Oracle Database to rebuild the Store table index as needed. Replace <Store Table Name> in each script with the Store table name as described in Using Prefixes with a JDBC Store. See Execution of PL/SQL Subprograms in Oracle Database Concepts.

    Note:

    Oracle recommends reverse indexes for I/O multithreading and non-reverse indexes for single threaded I/O.

Build a Reverse Index for an Oracle Database

To rebuild the Store table index as a reverse index for an Oracle database, run the following PL/SQL block as the store database user:

declare
  idx           user_ind_columns.index_name%TYPE;
  alter_stmt    VARCHAR2(200);
begin
  select index_name into idx from user_ind_columns where table_name =
 <Store Table Name> and column_name = 'ID';
  alter_stmt := 'alter index ' || idx || ' rebuild reverse';
  dbms_output.put_line(alter_stmt);
  execute immediate alter_stmt;
end;
/
Build a Non-Reverse Index for an Oracle Database

To rebuild a reverse Store table index as a non-reverse index for Oracle database, run the following PL/SQL block as the store database user: 

declare
  idx           user_ind_columns.index_name%TYPE;
  alter_stmt    VARCHAR2(200);
begin
  select index_name into idx from user_ind_columns where table_name =
 <Store Table Name> and column_name = 'ID';
  alter_stmt := 'alter index ' || idx || ' rebuild noreverse';
  dbms_output.put_line(alter_stmt);
  execute immediate alter_stmt;
end;
/
Reducing Contention in a Non-Oracle Database

For non-Oracle databases, refer to the database provider's documentation on how to reduce the contention.