WebSphere Enterprise Service Bus, Version 6.2.0 Operating Systems: AIX, HP-UX, i5/OS, Linux, Solaris, Windows


Common database configurations

The Common database configurations contain information about supported database types; scripts and their locations; profile creation configuration actions; installation parameters; types of created tables and user ID privileges.

The Common database is optionally created when you create a WebSphere® ESB profile. This database acts as a repository for various components.

The WebSphere ESB Common database is used by the following product components: You can create the Common database before, during, or after WebSphere ESB profile creation. You can create the Common database using the scripts that are created at product installation time or you can get the database scripts from the product media. These scripts require editing with information from your database administrator. You can also create the database using the Profile Management Tool or using scripts which are generated during profile creation. This second set of scripts include the database information that you provided during the profile creation.

Supported database types

The Common database can use the following database products:
Table 1. Supported database products
Database Types Considerations
Derby Embedded Used as the default database type for standalone profile.
Derby Network Server Used as the default database type in network deployment environment.
DB2 Universal Used as the database in network deployment configurations. Optionally, can be used as the database in stand-alone server configurations.

DB2 for z/OS v8
DB2 for z/OS v9

Important: When creating a profile for a server that uses DB2 for z/OS v9, the server must be able to connect to the DB2® database.
Used as the database in network deployment configurations. Optionally, can be used as the database in stand-alone server configurations.

DB2 UDB for iSeries (Native)
DB2 for i5/OS (Native)

Used as a local database for standalone profile.

DB2 UDB for iSeries (Toolbox)
DB2 for i5/OS (Toolbox)

Used as a remote database for network deployment environment or as a local database for a standalone profile.Used as the database in network deployment configurations. Optionally, can be used as the database in stand-alone server configurations.
DB2 Universal Runtime Client Used as the database in network deployment configurations. Optionally, can be used as the database in stand-alone server configurations.
Informix Dynamic Server  
Microsoft SQL Server (Embedded)  
Microsoft SQL Server (DataDirect)  
Microsoft SQL Server (Microsoft) - Support for the Microsoft SQL Server JDBC Driver, version 1.2 was added in WebSphere Process Server, version 6.2.0.1  

Oracle 9i
Oracle 10g
Oracle 11g

You need sysdba privilege to create the database, tables and schemas. Failure to have the correct sysdba privilege can result in errors creating and accessing the tables and schemas.
Important: On i5/OS, there is a single global database in which you define all schemas for all functional components. You must make sure that all schema names are unique within the logical partition (LPAR).

User ID privileges

The user credentials that you provide in the Profile Management Tool must have the permissions necessary to create table spaces, tables, schemas, indexes, and stored procedures. For the Create new database option, the user identity must have the necessary privileges to create a new database. See "Users and schemas for databases" and "Database privileges" for more information.
Note: If the user running the script has enough authority to create tables, the script will not require an authentication ID within the script.

Database Management Service instances

There is one set of common database tables per cell.

Configuration actions during profile creation

There are four options for installing the Common database:
  • Installer
  • Profile Management Tool
  • Silent installation
  • Scripts
Within each of these options are several more choices.

Installer

Use the Installer if you are going to create your profiles when you install your software. You can install your database products during installation but you are limited in the types of database products you can install. If you want to use another supported database product not in Table 2, you must create your deployment manager using the Profile Management Tool.

Table 2. Installer options
Option Databases you can use
Typical: Stand-alone profile Derby Embedded only
Typical: all other profiles Derby Network Server only
Customized: Stand-alone profile
  • Derby Network Server
  • DB2 Universal
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
Customized: all other profiles
  • Derby Network Server
  • DB2 Universal
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
A typical installation uses default values for configuration parameters and you cannot change these defaults. If you choose a customized installation, the defaults can be changed for your specific requirements. See "InstallingWebSphere Process Server and creating a profile interactively" for more information.

Profile Management Tool

Use the Profile Management Tool to create profiles after you install your software. The Profile Management Tool allows you the options of installing your database before, during or after profile creation. See "Creating the Common database and tables manually after profile creation or augmentation" for more information. For the locations of the database scripts, see "Scripts and their locations".

Table 3 lists the databases supported by typical (default) profiles and customized profiles. The Profile Management Tool typical installation has default values for the database and configuration parameters that cannot be changed. The Profile Management Tool customized installation allows you to change the configuration parameters and to choose a supported database. See "Creating profiles" for more information on creating customized profiles.

Table 3. Profile Management Tool options
Options Databases you can use
Typical: Stand-alone profile Derby Embedded only
Typical: all other profiles Derby Network Server only
Customized: Stand-alone profile Any of the Supported database types
Customized: all other profiles Any of the Supported database types
Profile Management Tool allows you to augment an existing profile from previous versions of
  • WebSphere Process Server
  • WebSphere Application Server
See "Augmenting existing profiles" for more information.

Silent Installations

When you install the product silently, you can specify the common database configuration by editing the template response file. See "Installing silently" for more information.

Scripts

There are two location where you can find the default profile scripts. You can locate one set of default scripts on your product media in the following directory media_root/dbscripts.

The other profile default scripts are located in the following directory after installation of WebSphere ESB install_root/dbscripts/CommonDB/dbType.

You can create your common database using scripts before you install WebSphere ESB or during profile creation. See "Creating the Common database manually before product installation" for more information.

If you choose to configure your database with scripts during profile creation, you first need to install WebSphere ESB and select the option to defer database creation. Profile creation collects your database parameters, which are added to default scripts. After you complete your profile creation, you can find the updated scripts in the following location:

profile_root/dbscripts/CommonDB/dbType/dbName

You can then give these scripts to the person who will create your Common database. The scripts are ready to execute, but you can edit them to include any specific requirements. If you try to start WebSphere ESB before creating the database you will receive an error message.

The scripts perform the following steps:
  • Create a new database, if asked (valid only for a local database), depending on your choices in the Database configuration panel in the Profile Management Tool. See Restrictions for details on database commands that are not supported by the Profile Management Tool.
    Note: Even though you can defer creation of the database until after the profile creation is complete, it is necessary to enter valid information in the Database configuration panel of the Profile Management Tool. This information is used to create the data source for the WebSphere ESB.
  • Create the data source on the JDBC provider.
Note: If you are creating the database at the same time as the profile any mistakes in the database parameters that you provide will yield errors in the overall profile creation. However, if you are delaying the database creation, the profile will be created without errors, but the generated database scripts will contain errors and therefore must be edited before the database can be created.
Note: For nodes of a cell, you must select the same database type as the deployment manager profile. The data source is maintained only at the cell level.

SQL scripts

SQL scripts allow you to configure your database before or after profile creation. Tables are created with a deployment manager profile and so there are no SQL scripts executed as part of managed node creation.

SQL scripts for each Common database client can be found in the following locations:
  • media_root/dbscripts on your product media
  • install_root/dbscripts/CommonDB/dbType after you install WebSphere ESB

If you choose to defer creation of the database after you have created the profile, the updated scripts will be found in:

profile_root/dbscripts/feature/dbType/dbName
The SQL script naming convention is:
  • For a component-specific script: createTable_componentName.sql. For example createTable_Recovery.sql.
  • For a component independent script: createTable.sql.
The script naming convention is shown in Table 4.
Table 4. Common database script naming convention
Type of script Script name
Component specific scriptName_componentName.sql
Component independent scriptName.sql

JDBC provider

A new JDBC provider is created depending on the database type. The provider is created in the node scope in a stand-alone profile and at cell level in network deployment environment. JDBC Provider refers to variable JDBC_DRIVER_PATH to locate local JDBC drivers. The variable is specified in cell level and each node level to point to correct local path.

Data source name:
  • WPS DataSource
Data source JNDI name:
  • jdbc/WPSDB

Restrictions

There are several restrictions on the database commands that are available to the user during profile creation.

Create new database is disabled for the following database types:
  • DB2 for z/OS v8
  • DB2 for z/OS v9
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g

Tables

The Common database scripts create only static tables during profile creation. The following table gives a list of all the tables that are created by different components.
Table 5. Tables created by WebSphere ESB components
Component Table names Scripts
Relationship Dynamic table, created at runtime createTable_RelationshipMetadataTable.sql
Common database SchemaVersionInfo createTable_CommonDB.sql
ESB Logger Mediation MSGLOG createTable_ESBLoggerMediation.sql

All the above SQL scripts are executed by commonDBUtility.ant from each component script, such as configRecovery > commonDBUtility > execute createTable_Recovery.sql. When the value delayConfig=true is in the response file, the SQL files are created, but are not run. When this is the case, you will need to run the SQL manually after the configuration.

Note: In the ESB Logger Mediation component, you can configure each message logger primitive to use a different data source and a different database.

Exported scripts

Scripts are created for any option chosen on the Profile Management Tool panel to configure the Common database. The scripts contain only basic creation statements for databases, tables, and indexes. The database administrator must use database native commands to execute these scripts. See "Configuring the Common database using the Profile Management Tool" for more information.

The scripts are called configCommonDB.bat or configCommonDB.sh for Windows® and UNIX-based operating systems, respectively.

Database scripts are exported to the following folder:
profile_root/dbscripts/CommonDB/dbType/dbName

concept Concept topic

Terms of use | Feedback


Timestamp icon Last updated: 21 June 2010


http://publib.boulder.ibm.com/infocenter/dmndhelp/v6r2mx/topic//com.ibm.websphere.wesb620.doc/doc/cins_common_db_specs.html
Copyright IBM Corporation 2005, 2010. All Rights Reserved.
This information center is powered by Eclipse technology (http://www.eclipse.org).