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.
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.
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.
The main steps for creating a JDBC TLOG store are as follows:
Create a JDBC data source, GridLink data source, or multi data source to interface with the JDBC store. See Choosing a Data Source.
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.
Optional. It is highly recommended that you configure the Prefix option to a unique value for each configured JDBC TLOG store.
For high availability, make your data source available to backup servers. See Chapter 6, "Server Migration when using a JDBC TLOG Store."
You can choose one of the following data source types, depending on your WebLogic Server license and application needs:
Generic Data Sources—See "Creating a JDBC Data Source" and "Using Connect-Time Failover with Oracle RAC" in Administering JDBC Data Sources for Oracle WebLogic Server.
GridLink Data Sources—See "Using GridLink Data Sources" in Administering JDBC Data Sources for Oracle WebLogic Server.
Multi data sources—Backed by a fully replicated, zero-latency database, such as Oracle RAC. See "Configuring JDBC Multi Data Sources" and "Using Multi Data Sources with Oracle RAC" in Administering JDBC Data Sources for Oracle WebLogic Server.
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 6-1 describes the JDBC TLOG store configuration parameters that can be modified.
Table 6-1 JDBC TLOG Store Configuration Options
Option | Required | What It Does |
---|---|---|
Yes |
The JDBC data source or multi data source used by this JDBC store to access the store's database table ( 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. |
|
No |
The prefix for the JDBC store's table is generally entered in the following format: 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 Modifying an existing JDBC store's prefix does not necessarily require a server restart, as described in Modifying Custom Persistent Store Parameters. |
|
No |
Optionally used with supported DDL (data definition language) files to create the JDBC store's database table ( |
|
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. |
Default is 20 |
The maximum number of table rows that are deleted per database call. |
|
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. |
|
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 |
|
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. |
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.]
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.
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.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. For more information, see "Transaction Recovery After a Server Fails" in Developing JTA Applications for Oracle WebLogic Server.
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.
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.
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.
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.
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.
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.
The main steps for creating a JDBC store are as follows:
Create a JDBC data source or multi data source to interface with the JDBC store.
Create a JDBC store and associate it with the JDBC data source or multi data source.
It is highly recommended that you configure the Prefix option to a unique value for each configured JDBC store table.
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.
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 6-2 describes the JDBC store configuration parameters that can be modified.
Table 6-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:
|
Data Source |
Yes |
The JDBC data source or multi data source used by this JDBC store to access the store's database table ( 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: 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 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 ( |
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.]
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 6-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.
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.
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.
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 theweblogic/store/io/jdbc/dd
l parameter, the entire jar file is extracted.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.
Save your changes and rename the new DDL appropriately (for example, mydatabase.ddl
)
Create a JDBC store, as explained in "Create JDBC Stores" in the Oracle WebLogic Server Administration Console Online Help.
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.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 OracleLONG 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:
Shut down the server instance that uses the JDBC store.
Delete the current JDBC table, as explained in Managing JDBC Store Tables.
Reboot the server instance.
Create a new JDBC store, as explained in "Create JDBC Stores" in the Oracle WebLogic Server Administration Console Online Help.
In the Create Table from DDL File
field on the General Configuration page, enter the location of:
the oracle_blob.ddl
file as: /oracle_blob.ddl
the oracle_blob_securefile.ddl
file as: /oracle_blob_securefile.ddl
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.
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
Enter the utils.Schema
command, as follows:
$ java utils.Schema url JDBC_driver [options] DDL_file
Note:
To executeutils.Schema
, your CLASSPATH
must contain the weblogic.jar
file.Table 6-4 lists the utils.Schema
command-line arguments.
Table 6-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:
You can also specify the |
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
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.
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.
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.
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.The following settings are recommended when you use a JDBC data source or multi data source for JDBC stores.
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.
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.
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 theWorker 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.
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:
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.
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. For more information on PL/SQL, 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.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;
/
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;
/