Creating SQL Server databases

You can create the required databases for IBM® Business Process Manager either before or after you create the profiles and the deployment environment.

Before you begin

You cannot share databases across multiple installations or deployment environments.
The Process Server and Performance Data Warehouse components require their own separate databases and cannot be configured on the same database as the other IBM Business Process Manager components.
The Process Server and Performance Data Warehouse components require the databases to be case insensitive for SQL Server.
The CommonDB (and legacy WebSphere® Process Server) components require the databases to be case sensitive for SQL Server.
The user who creates the databases cannot be the same user that you plan to assign as the login user for accessing the databases. If you deviate from this requirement, the default schema dbo will be used.
The schema name used for each component should match the user.

About this task

When you use the Deployment Environment wizard to create the deployment environment, you can use the Create Tables option to specify whether database tables are created when the deployment environment is created:

If the Create Tables option is selected, database tables are automatically created at the same time as the deployment environment. Therefore, empty databases must exist before you run the Deployment Environment wizard.
If the Create Tables option is not selected, database table creation is deferred when you create the deployment environment. Therefore, you can create the databases either before or after you run the Deployment Environment wizard. You might find it useful to create the databases after running the wizard because you can use the set of populated scripts, which the wizard generates, to create the databases and database tables at a time that you choose.

The default database names are BPMDB for the Process database, PDWDB for the Performance Data Warehouse database, and CMNDB for the Common database. Usually you require the Process database, the Performance Data Warehouse database, and the Common database.

In an Advanced-only deployment environment, you need only the Common database. For both Advanced and Advanced-only deployment environments, the Common database has two parts: one is scoped to the cell and the other is scoped to the deployment environment. Both parts can be defined to use CMNDB (which is the default) or they can use separate databases.

For more information about creating databases, see the following sections:

Creating the databases before creating profiles or the deployment environment
Creating the databases after creating the profiles and the deployment environment

Creating the databases before creating profiles or the deployment environment

To create the databases before you create the profiles or before you use the Deployment Environment wizard to create your deployment environment, you can use the createDatabase_CaseInsensitive.sql and createDatabase_CaseSensitive.sql templates that are provided with your IBM Business Process Manager installation.

Procedure

Complete the following steps for each database that you want to create:

Navigate to the BPM_HOME/BPM/dbscripts/SQLServer/Create directory and make two copies of the createDatabase_CaseInsensitive.sql file and one copy of the createDatabase_CaseSensitive.sql file.
Complete the following substeps to create the Process database:
1. In the first copy of the createDatabase_CaseInsensitive.sql file, replace @DB_NAME@ with the name that you want to use for the Process database. Save the file.
2. Create the database by running the following command on your local or remote database server. For example:
```
sqlcmd -i createDatabase_CaseInsensitive.sql
```
Complete the following substeps to create the Performance Data Warehouse database:
1. In the second copy of the createDatabase_CaseInsensitive.sql file, replace @DB_NAME@ with the name that you want to use for the Performance Data Warehouse database. Save the file.
2. Create the database by running the following command on your local or remote database server. For example:
```
sqlcmd -i createDatabase_CaseInsensitive.sql
```
Complete the following substeps to create the Common database:
1. In the copied createDatabase_CaseSensitive.sql file, replace @DB_NAME@ with the name that you want to use for the Common database. Save the file.
2. Create the database by running the following command on your local or remote database server. For example:
```
sqlcmd -i createDatabase_CaseSensitive.sql
```

Creating the databases after creating the profiles and the deployment environment

After you create the profiles, you can use the Deployment Environment wizard to create the deployment environment and generate the database scripts. The scripts are populated with the configuration values that you specified in the wizard. You can use some of these scripts to create the databases if you chose to defer the creation of the database tables.

Before you begin

You must have already used the Profile Management Tool, the BPMConfig command, or the manageprofiles utility to create and augment the profiles. You must also have used the Deployment Environment wizard to configure the deployment environment.

Procedure

On the computer where you created the deployment manager profile, navigate to one or more of the following default subdirectories where the SQL database scripts were generated:
- dmgr_profile_root/dbscripts/cell_name/SQLServer/CMNDB/schema_name
- dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/SQLServer/CMNDB/schema_name
- dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/SQLServer/BPMDB/schema_name
- dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/SQLServer/PDWDB/schema_name
These directories contain the createDatabase.sql script that you can use to create the databases.

The number of subdirectories that are generated is dependent on the deployment environment type and the number of databases that were configured in the Deployment Environment wizard.
For each createDatabase.sql file that was generated, run the following command on your local or remote database server to create the Common database (CMNDB), Process database (BPMDB), and Performance Data Warehouse database (PDWDB):
```
sqlcmd -i createDatabase.sql
```
Note: The CMNDB database only needs to be created once, which means that you only need to run the command in one of the two CMNDB output directory paths.