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, andschema 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 Configuring and Managing JDBC for Oracle WebLogic Server. For more information about setting the number of database reconnection attempts, see the "Enabling Connection Creation Retries" section in Configuring and Managing JDBC 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.
For more information, see the "Performance Considerations" in Type 4 JDBC Drivers for Oracle WebLogic Server.

Handling JMS Transactions with JDBC Stores

You cannot configure a JDBC store to use a JDBC data source that is configured to support global (XA) transactions. The JDBC store must use a JDBC data source that uses a non-XA JDBC driver. In addition, you cannot enable Logging Last Resource or Emulate Two-Phase Commit in the data source. 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 Programming JMS 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 Configuring and Managing JDBC for Oracle WebLogic Server.

Monitoring a Persistent Store

You can monitor statistics for each existing persistent store and for each open store connection.

Monitoring Stores

Each persistent store is represented at run time by an instance of the PersistentStoreRuntimeMBean, which provides the following options.

Table 6-6 Persistent Store Run-time Options

Option	What It Does
CreateCount	Number of create requests issued to this persistent store.
ReadCount	Number of read requests issued to this persistent store.
UpdateCount	Number of update requests issued by this persistent store.
DeleteCount	Number of delete requests issued by this persistent store.
ObjectCount	Number of objects contained in the persistent store.
Connections	Number of active connections in the persistent store.
PhysicalWriteCount	Number of times the persistent store flushes its data to durable storage.

Monitoring Store Connections

For each open persistent store connection, the persistent store also registers a PersistentStoreConnectionRuntimemMBean, which provides the following options.

Table 6-7 Persistent Store Connection Runtime Options

Option	What It Does
CreateCount	Number of create requests issued to this connection.
ReadCount	Number of read requests issued to this connection.
UpdateCount	Number of update requests issued by this connection.
DeleteCount	Number of delete requests issued by this connection.
ObjectCount	Number of objects contained in the connection.

Table 6-8 defines most of the run-time prefix names of the WebLogic services and subsystems that can create a connection to the persistent store.

Table 6-8 Persistent Store Run-Time Prefix Names

Subsystem/Service	Run-Time Prefix Name
Deployment	weblogic.deploy.internal where internal is the name of the deployment connection
Diagnostic Service	weblogic.diagnostics.internal where internal is the logical name of the diagnostic archive connection
EJB Timer Services	weblogic.ejb.timer.internal where internal uniquely identifies EJB deployments in a server instance
JMS Service	JMS server: weblogic.messaging.jmsServer.internal where internal is the name of the JMS server connection JMS durable subscriber: weblogic.messaging.jmsServer.durablesubs.internal where internal is the name of the durable subscriber connection
JTA Transaction Log (TLOG)	weblogic.transaction.internal where internal is the name of the TLOG connection
Path Service	weblogic.messaging.PathService.internal where internal is the name of the path service connection
SAF Service	SAF agent weblogic.messaging.SAFAgent@server1.internal where internal is the name of the SAF agent's connection SAF durable subscriber: weblogic.messaging.SAFAgent@server1.durablesubs.internal where internal is the name of the durable subscriber connection
Web Services	weblogic.wsee.server.store.internal where internal is the name of the Web Service's connection

Administering a Persistent Store

The WebLogic Store administration utility enables administrators to troubleshoot a WebLogic persistent store. The store utility operates only on a store that is not currently opened by a running server instance. This utility can be run from a Java command line or from WebLogic Scripting Tool (WLST), as described in Store Administration Using a Java Command Line and Store Administration Using WLST.
The most common uses-cases for store administration are for compacting a file store to reduce its size and for dumping the contents of a file store of JDBC store to an XML file for troubleshooting purposes. Examples of these use cases are provided later in this section.
Table 6-9 defines the available store administration commands for Java and WLST.

Table 6-9 Persistent Store Administration Options

Java Command	WLST Method	What It Does
help	helpstore	Displays available commands, usage, and examples.
compact	compactstore	Compacts and defragments the space occupied by a file store. This command only works offline and does not work for JDBC stores. Note: Compacting a file store is usually not necessary if you know that file store will likely grow to the current size again. File stores automatically re-use space freed by deleted records and expand only when there is insufficient internal space for new records. Also, file stores do not normally become fragmented as most persistent records are short-lived.
openfile	openfilestore	Opens an existing file store for further operations. If a file store does not exist, a new one is created in an open state using the -create parameter.
openjdbc	openjdbcstore	Opens an existing JDBC store for further operations. If a JDBC store does not exist, a new one is created in an open state
dump	dumpstore	Dumps store or connection contents in a human-readable format to user-specified XML file. The XML file format is the same format used by the diagnostic image of the persistent store.
list	liststore	Lists store names, open stores, or connections in a store.
n/a	getstoreconns	Returns a list of connections in the specified store (for script access)
n/a	getopenstores	Returns a list of opened stores (for script access).
opts	n/a	Lists invocation options for the store administration tool.
verbose	n/a	Controls display of additional information, such as stack traces.
close	closestore	Closes a previously opened store.
quit	exit	Ends the store administration session.

A persistent store can be backed by the file system (file store) or by a JDBC-capable database (JDBC store). Except for the openfile/openfilestore() andopenjdbc/openjdbcstore() options, there is no difference in the options to operate on these two different types of stores.
Most commands and methods work in terms of store names, while others also work in terms of connection names. Store connections are logical groups of records within persistent stores. For example, the JMS and JTA subsystems persist their respective records in different connections in the same store.

Store Administration Using a Java Command Line

To open the persistent store administration utility from a Java command line, type the following:

> java weblogic.store.Admin
> storeadmin->

Accessing Store Administration Help

Type help for detailed descriptions on available store administration commands, as well as examples of typical command usage. For example, the following comprehensive help is provided for the list command, which lists store names, open stores, or connections in a store.

storeadmin->help list
  Command:
    list
  Description:
    lists store names, open stores, or connections in a store
  Usage:
    list [-store storename|-dir dir]
  Examples:
    list #lists all opened stores by storename
    list -store store1 #lists all connections in store1
    list -dir dir1 #lists all storenames found in dir1

Dumping the Contents of a File Store

Here's an example of using a series of store administration commands to ultimately export the contents of a file store named myfilestore into a human-readable XML file format in a temporary directory. This does not include store connection names or the actual record contents, which require the optional -conn and -deep parameters.

> storeadmin-> list -dir .
> storeadmin-> openfile -store myfilestore -dir .
> storeadmin-> dump -store myfilestore -out d:\tmp\filestore1-out
> storeadmin-> close -store myfilestore

The list command shows all the store names in the current directory. The openfile and openjdbc commands must be used to open and/or create a file or JDBC store first before calling certain administration functions, like dump and list (only when listing open stores). After administering an open store, you must close it using the closecommand.

Compacting a File Store

Here is an example of using the compact command to compact the space occupied by a file store in the mystores directory.

> storeadmin->compact -dir c:\mystores -tempdir c:\tmp

Since the compact command can only be used on an unopened file store, none of the stores that have files in the source -dir directory should be open. Also, the temporary-tempdir directory should have at least enough extra space as the source directory and should also not be under the source directory. When compact successfully completes, the newly compacted store files will be in the mystores directory. In addition, a new, uniquely-named directory will be created under tmp containing the original uncompacted store files.

Store Administration Using WLST

The WLST interface has a couple of additional methods (compared to the Java command line) such as getopenstores and getstoreconns, that return relevant Java objects and can be used for scripting in WLST.

Accessing Store Administration Help

To access the persistent store administration utility from WLST, type the following command:

> java weblogic.WLST

Type helpstore() for detailed descriptions on available store administration commands, as well as examples of typical command usage. For example, the following help is provided for the list command, which lists store names, open stores, or connections in a store.

> wls:/offline> helpstore(liststore)
  lists storenames, opened stores, or connections (for interactive access)
  Parameters store and dir cannot both be specified concurrently.

  Usage: liststore(store='null',dir='null')

  @param store [optional] a previously opened JDBC or File store's name.
      If store is specified, all connections in the store are listed.
  @param dir [optional] directory for which to list available store names
      If dir is specified, all store names in the directory are listed.

  If neither store nor dir are specified, all open store names are listed.
  @return 1 on success, 0 on failure

Note that the parameters with an equal sign "=" are optional. For example, the compactstore method can be invoked as either compactstore(dir='storename', tempdir='/tmp') or compactstore(store='storename'), where tempdir takes the default value. Default values for optional parameters are listed in the command-specific help.