Planning to configure the common database

The common database configurations contain information about supported database types, script names and their locations, profile creation configuration actions, installation parameters, types of created tables, and user ID privileges.

The IBM® Business Process Manager common database is used by the following product components:
You can create the common database before, during, or after you create the IBM Business Process Manager profile.

Supported database types

The common database can use the following database products:
Table 1. Supported database products
Database Types Considerations
DB2® Express® Used as the default database type for a stand-alone profile.
DB2 Universal Used as the database in network deployment configurations. Optionally, can be used as the database in stand-alone server configurations.
DB2 Data Server 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.
Microsoft SQL Server (Microsoft)  
Oracle You need system database administrator privileges to create the database, tables, and schemas. If you do not have these privileges, you might receive errors when you create or access the tables and schemas.

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 ID must have the necessary privileges to create a database. If the user who is running the script has the authority to create tables, the script does not require an authentication ID within the script. For more information, see "Users and schemas for databases" and "Database privileges".

Database Management Service instances

For a network deployment environment, there is one set of common database tables per cell.

Configuration actions during profile creation

You can use one of the following options to install 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 that you can install. To use a supported database product that is not in Table 2, you must use the Profile Management Tool to create your deployment manager.
Table 2. Installer options
Option Databases you can use
Typical: stand-alone profile DB2 Express
Typical: all other profiles DB2 Express
Customized: stand-alone profile
  • DB2 Universal
  • DB2 Data Server
  • Oracle
Customized: all other profiles
  • DB2 Universal
  • DB2 Data Server
  • Oracle

A typical installation uses default values for configuration parameters and you cannot change these defaults. If you choose a customized installation, you can change the defaults for your specific requirements.

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.
Table 3 lists the databases supported by typical (default) profiles and customized profiles.
Table 3. Profile Management Tool options
Options Databases you can use
Typical: stand-alone profile
  • DB2 DataServer
  • DB2 Universal
  • DB2 for z/OS
  • Microsoft SQL Server
  • Oracle

DB2 for z/OS, Oracle and Microsoft SQL databases must exist so that the Profile Management Tool can configure them.
Typical: all other profiles
  • DB2 DataServer
  • DB2 Universal
  • DB2 for z/OS
  • Microsoft SQL Server
  • Oracle

DB2 for z/OS, Oracle and Microsoft SQL databases must exist so that the Profile Management Tool can configure them.
Customized: stand-alone profile Any of the Supported database types
Customized: all other profiles Any of the Supported database types
Silent Installations
When you install the product silently, you can specify the common database configuration by editing the template response file.
Scripts
You can use scripts to create your common database before you install IBM Business Process Manager or during profile creation.

If you choose to configure your database manually after profile creation, you must first install IBM Business Process Manager and indicate in the Profile Management Tool that you do not want to run the scripts as part of profile creation. The Profile Management Tool updates the default scripts with the database parameters that you specify, and writes updated scripts out to the profile_root/dbscripts/CommonDB/dbType/dbName directory.

Tip: You can use the Profile Management Tool to change the directory to which updated scripts are written.

The scripts are ready to run, but you can edit them to include any specific requirements. You can then give these scripts to the person who should create your common database. If you try to start IBM Business Process Manager before creating the database, you receive an error message.

When you run the scripts, you also perform the following tasks:
  • Create a database, if appropriate (valid only for a local database), depending on your choices in the Database configuration panel in the Profile Management Tool.
    Important: Although you can defer creating the database until the profile creation is complete, you must enter valid information in the Database configuration panel of the Profile Management Tool. This information is used to create the data source for the IBM Business Process Manager.
  • Create the data source on the JDBC provider.
Important: If you create the database at the same time as the profile, and if you introduce mistakes in the database parameters, errors occur in the profile. If you delay creating the database, the profile is created without errors, but the generated database scripts contain errors and you must correct them before you can create the database. For custom (managed) 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.
Note: For custom (managed) 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

Use SQL scripts to configure your database before or after you create the profile. Tables are created with a deployment manager profile so no SQL scripts are executed while the managed node is created.

You can find SQL scripts for each common database client in the following location:
  • install_root/dbscripts/CommonDB/dbType after you install IBM Business Process Manager

If you choose to defer creation of the database after you create the profile, you can find the updated scripts in the profile_root/dbscripts/feature/dbType/dbName directory.

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 following table shows the script naming convention.
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 Java™ Database Connectivity (JDBC) provider is created depending on the database type. The provider is created in the node scope in a stand-alone profile and at the cell level in a network deployment environment. The JDBC provider refers to the JDBC_DRIVER_PATH variable to locate local JDBC drivers. The variable is specified at the cell level and each node level points to the correct local path.

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

Restrictions

Several restrictions exist for the database commands that are available during profile creation.

Create new database is disabled for the following database types:
  • DB2 for z/OS
  • Oracle
  • Microsoft SQL Server

Tables

The common database scripts create only static tables during profile creation. The following table contains a list of all the tables that are created by different components.
Table 5. Tables created by IBM Business Process Manager components
Component Table names Scripts
Recovery FAILEDEVENTS
FAILEDEVENTBOTYPES FAILEDEVENTMESSAGE		 createTable_Recovery.sql
Mediation MEDIATION_TICKETS createTable_mediation.sql
Relationship Dynamic table, created at runtime createTable_Relationship
MetadataTable.sql
Application Scheduler WSCH_LMGR WSCH_
LMPR WSCH_TASK WSCH_TREG		 createTable_AppScheduler.sql
Customization (selector/business rule group) BYTESTORE
BYTESTOREOVERFLOW APPTIMESTAMP		 createTable_customization.sql
Common database SchemaVersionInfo createTable_CommonDB.sql
Persistent LockManager PERSISTENTLOCK createTable_lockmanager.sql
ESB Logger Mediation MSGLOG createTable_ESBLogger
Mediation.sql

All the SQL scripts in the previous table are executed by the commonDBUtility.ant file 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 they are not run. In this case, you must run the SQL manually after the configuration.

In the WebSphere Enterprise Bus 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 that you selected 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. For more information, see "Configuring the common database using the Profile Management Tool".

The names of the scripts are configCommonDB.bat for Windows, and configCommonDB.sh for UNIX-based operating systems.

Database scripts are exported to the

profile_root/dbscripts/CommonDB/dbType/dbName directory.

Parent topic: Planning your component-specific database configurations