IBM BPM, V8.0.1, All platforms > Install IBM BPM > Plan for IBM BPM > Plan the database configuration > Plan the component-specific database configurations

Plan 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 common database acts as a repository for various components. The IBM BPM common database is used by the following product components:

You can create the common database before, during, or after you create the IBM BPM profile.

You can create the common database after you configureIBM BPM. You can use the database design tool to define your database configuration, and to generate database scripts that can be used to create and populate the database with all the required database objects.


Supported database types

The common database can use the following database products:

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 ND configurations. Optionally, can be used as the database in stand-alone server configurations.
DB2 Data Server Used as the database in ND configurations. Optionally, can be used as the database in stand-alone server configurations.

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

DB2 for z/OS

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 ND 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 pmt.sh 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. See Database privileges and User ID or schema name privileges.


Database Management Service instances

For a ND 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:

Within each of these options are several more choices.

Installer

Use the Installer if you are going to create your profiles when 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, use pmt.sh to create your dmgr.

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 pmt.sh to create profiles after 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.

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 pmt.sh 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 pmt.sh 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 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 BPM or during profile creation.

If you choose to configure your database manually after profile creation, first install IBM BPM and indicate in pmt.sh 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.

You can use pmt.sh 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 BPM 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 pmt.sh.

    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 pmt.sh. This information is used to create the data source for the IBM BPM.

  • Create the data source on the JDBC provider.

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 dmgr profile. The data source is maintained only at the cell level.

For custom (managed) nodes of a cell, you must select the same database type as the dmgr 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 dmgr profile so no SQL scripts are run while the managed node is created.

You can find SQL scripts for each common database client in the following location:

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:

The following table shows the script naming convention.

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 ND 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:

Data source JNDI name:

Restrictions

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

Create new database is disabled for the following database types:

This means that the dbCreateNew property in the response file must have a value of false.

To create databases and storage groups, see Create the DB2 databases, storage groups, and tables using SPUFI or DSNTEP2.


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.

Tables created by IBM BPM components
Component Table names Scripts
Recovery FAILEDEVENTS
FAILEDEVENTBOTYPES FAILEDEVENTMESSAGE
createTable_Recovery.sql
Mediation MEDIATION_TICKETS createTable_mediation.sql
Relationship Dynamic table, created at run time createTable_Relationship
MetadataTable.sql (create table space, for DB2 for z/OS only)
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 run 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, 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 pmt.sh 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 run these scripts.

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

Scripts are created regardless of the value (true or false) that you assign to the delayConfig property in the response file. The scripts contain only basic creation statements for database tables and table spaces. The database administrator can use a database tool of choice to run these scripts.

Database scripts are exported to the

profile_root/dbscripts/CommonDB/ dbType/ dbName directory.

configuration_file_system_root app_server_root/profiles/default/dbscripts/CommonDB/ dbType/ dbName directory.

Plan the component-specific database configurations


Related tasks:
Create database design files for DB2
Create database design files for Oracle
Create database design files for DB2 for z/OS
Create database design files for SQL Server