Preparing Your Database

Preparing your database for Oracle WebLogic Integration requires the use of WebLogic Platform tools and the application of best practices for your RDBMS. For requirements and recommendations that are general to WebLogic Platform, see Supported Configurations for WebLogic Platform

For more information specific to Oracle WebLogic Integration, see the following topics:

• Configuring Your Database
• Related Topics

Configuring Your Database

Oracle WebLogic Integration solutions can be configured for single-server or cluster environments. To configure your database resources:

• For a single-server environment...
• Use the Configuration Wizard as described in Configuring a Single-Server Deployment in Deploying WebLogic Integration Solutions.
• For a cluster environment...
• Use the Configuration Wizard as described in Configuring a Clustered Deployment in Deploying WebLogic Integration Solutions.
• For a production environment...
• Use the SQL scripts described in Configuring a Production Database in Managing WebLogic Integration Solutions.

If your configuration includes DB2:

• Tune the size of the DB2 output buffer to accommodate Trading Partner Management transaction activity. If the output buffer is too small, an asynchronous service queue will throw an exception.

If your configuration includes Sybase:

• Increase the Sybase procedure cache from the default of 3271 to a size that will enable messages of a size typical in your OracleWebLogic Integration application to be exchanged. The procedure cache percent should be set to 25%. This number specifies the percent of memory allocated to the procedure cache after allocating the memory Sybase Server needs for locks, user connections, and the server code itself. If messages exceed the size of the procedure cache, the message may fail with a BEA-463014 exception.
• Create a large transaction log segment and a threshold process that sends notification to the database administrator when the log segments fills to a certain percentage of its size. The database administrator should manually dump the log to prevent it filling up under load.

If you use Sybase for Worklist:

create_dtabase.sql: Creates the database and login. You must create a main WLI database. If you want to use GroupSpace, you must create that database. You must also create a database for each additional content management repository. The devices created by create_devices.sql are used and indexes are placed in their own WEBLOGIC_INDEX segment. An alias is added to the database owner (dbo) user of the database.

Create the database using the WL_HOME\portal\db\sybase\admin\create_database.sql script as a template. This script creates a database with WEBLOGIC_INDEX used by other SQL scripts while creating a domain using the Configuration Wizard.

Note: If OracleWebLogic Portal is not installed then the create_database.sql file is not available in the installation.

Make a copy of this script and edit it to replace <<WEBLOGIC>> with the appropriate database name, database user, and password for each database you create. Also adjust database and log sizes as appropriate.

If the database you are creating is a development database (and therefore database recovery is not a concern), you can uncomment and set the truncate log on checkpoint option to true.

If your applications will use OracleWorkshop for WebLogic for create database or RowSet controls, uncomment and set the DDL in transaction option to true; otherwise create commands will not work properly.

Typical names for the main OracleWebLogic Portal database are:

• Database name: WEBLOGIC
• Database schema user: WEBLOGIC
• Password: WEBLOGIC

When you run the script with these values, it creates the WEBLOGIC database, the WEBLOGIC_INDEX file group, and WEBLOGIC database owner (dbo) user login. An alias is created to make WEBLOGIC the dbo user in the database. It also sets the WEBLOGIC database as the default database for the WEBLOGIC user.

To use GroupSpace, you must create an additional database. Typical names for the GroupSpace database are:

• Database name: WEBLOGIC
• Database schema user: WEBLOGIC
• Passsword: WEBLOGIC
  

If your configuration includes Oracle:

• When you create the tables for a WebLogic Workshop web service being deployed to a production environment, you should store BLOB and CLOB data in one or more tablespaces that do not contain other types of data.
• The following OracleWebLogic Integration system tables have BLOB or CLOB columns:
  • WLI_CALENDAR
  • WLI_PROCESS_DOCUMENT
  • WLI_PROCESS_EVENT
  • WLI_PROCESS_TRACKING
  • WLI_WORKLIST_DATA
  • WLI_MT_CONTENT
• The following archive tables include BLOB or CLOB columns:
  • WLI_DOCUMENT_DATA
  • WLI_TASK_ARCHIVING
  • WLI_TASK_DATA_ARCHIVING
  • The BLOB and CLOB data for all of these tables should be segregated from other data and should not be stored in the <USERS tablespace. Storing BLOB or CLOB data in the USERS tablespace can exhaust the tablespace's storage capacity.
  • You can use the following table creation scripts as a template for creating conversation tables with a new tablespace for the BLOB data:

  CREATE TABLE <*tableName*>(
  CG_ID varchar(768) not null,
  LAST_ACCESS_TIME number(19,0),
  CG_DATA BLOB,
  PRIMARY KEY (CG_ID)
  )
  LOB("CG_DATA") STORE AS ( TABLESPACE <*blobTablespace*> )
  

  • The entity bean Exclusive concurrency strategy should be used to minimize Oracle lock conflicts in a cluster environment. (Entity beans in environments using other types of databases should be configured to use the Database concurrency strategy.) Exclusive is the default concurrency strategy.
  • Tune your database connection processes and set the WebLogic execution process Threadcount to a number appropriate for your application load. If there are too few connection processes available to handle concurrent messages being sent to the database, ORA-27101 exceptions occur. For information about configuring WebLogic execution thread pools, see Creating Execute Queues in the WebLogic Server Performance and Tuning. Contact your Oracle DBA for assistance in tuning your database connection processes.
  • When trading partners engage in conversations involving large numbers of messages exchanged and persisted in runtime as well as archive tables, the cursors available in Oracle databases may be insufficient and exceptions similar to the following may be thrown:

  java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1 ORA-0100: maximum open cursors exceeded

  • To avoid this situation, you can set the statement cache size for each JDBC connection pool to zero. To configure the statement cache, follow these steps:
  1. Launch the OracleWebLogic Server Administration Console. Click ‘Lock and Edit’ in the left side pane to make any changes..
  2. In the left pane, click Services, JDBC, Data Sources to display the list of data sources in the current domain
  3. Click the data source, you want to configure.
  4. Click the Configuration tab, then the Connection Pool tab
  5. In Statement Cache Size, enter 0 (zero) as the number of statements to cache per connection pool instance.
  6. Click Save to save your changes. Activate your changes.
  7. Repeat Step 2 through 6 for each data source.
• For more information about JDBC Data Source, see Configure JDBC Data Sources in the WebLogic Administration Console Online Help.
• The Trading Partner Management repository implements the NOTES field in its database tables as a LONG data type and the WLI_TPM_XML_ELEMENT as a TEXT data type on Oracle databases. The use of these data types may obstruct certain database management functions:
• Utilities such as import and export require that each row fits in memory, including the full length of any LONG value. Since LONG values can be up to 2 GB in length, this may effectively prevent import and export from working.
• The move utility cannot be used on tables that contain LONG fields.
• There is no way to segregate LONG data into their own tablespace.
• Tables with LONG columns cannot be replicated using Oracle's replication services.
• Tables with LONG columns cannot be partitioned.

If your configuration includes Pointbase:

• When you use the Configuration Wizard in Custom mode and specify Pointbase ports other than the default (9093), you must edit the startWebLogic script (startWebLogic.cmd for Windows) and add the -port=XXXX option (where XXXX represents the correct port value) in the line that starts the startPointBase scripts after the startPointBase command.
• If the size of the data stored in process variables will exceed 4 MB, manually create or modify the conversation state tables in the database so that the column size is larger than the size of the data stored in process variables. During development, these tables are typically autocreated by WebLogic Workshop with names based on the application, package, and filename of the source (for example, JWS_WLIPROD_BPM_ORDERPROCESSOR). If the data stored in a process variable exceeds the size of the conversation state table column size, all conversations are terminated in the current process.

If your configuration includes MS SQL, you need to setup the MS SQL DB with JTA as follows:

1. Copy the sqljdbc.dll and instjdbc.sql files from the WL_HOME\server\lib directory to the SQL_Server_Root/bin directory of the MS SQL Server database server, where WL_HOME is the directory in which WebLogic server is installed, for example, c:\bea\wlserver_10.3.
2. From the database server, use the ISQL utility to run the instjdbc.sql script. The system administrator must back up the master database before
   running instjdbc.sql.

At the command prompt, use the following syntax to run instjdbc.sql:
ISQL -Usa -Psa_password -Sserver_name -ilocation\instjdbc.sql
where:

• sa_password is the password of the system administrator.
• server_name is the name of the server on which SQL Server resides.
• location is the full path to instjdbc.sql. (You copied this script to the SQL_Server_Root/bin directory in step 1.)

Alternately copy the text from instjdbc.sql and execute in the MS SQL Server Query analyzer.

Note: The instjdbc.sql script generates many messages. In general, these messages can be ignored. However, the system administrator should scan the output for any messages that may indicate an execution error. The last message should indicate that instjdbc.sql ran successfully. The script fails when there is insufficient space available in the master database to store the JDBC XA procedures, or to log changes to existing procedures.

3. Start the DTC (Distributed Transaction Coordinator) service for the Microsoft SQL Server database.
4. Create the database using the WL_HOME\portal\db\sql_server\admin\create_database.sql script as a template. This script creates a database with WEBLOGIC_INDEX used by other SQL scripts while creating a domain using the Configuration Wizard.

Note: If Oracle WebLogic Portal is not installed then the create_database.sql file is not available in the installation.

Related Topics

• System Configuration in Using the WebLogic Integration Administration Console.
• Learn how to manage the WebLogic Integration datastore.
• Using the Trading Partner Bulk Loader in Managing WebLogic Integration Solutions
• Learn how to import, export, and delete Trading Partner Management data.