Creating SQL Server databases (deprecated)

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 Automation Workflow installation.

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

1. 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.
2. 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

3. 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

4. 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

5. Complete the following substeps to create the Content database (CPEDB) and manually create the table spaces for it.
   1. Copy the install_root\BPM\dbscripts\SQLServer\Create\createDatabase_ECM.sql file to your working directory.
      • Replace @DB_NAME@ with the name that you want to use for the database (such as CPEDB).
      • Replace @DB_DIR@ with your SQL Server home directory.
      • Replace @DOS_SCHEMA@ with the DOS schema value you choose, such as DOS. Later, you must enter this DOS schema value in the Design Object Store database schema name field on the Configure databases page of the Deployment Environment wizard.
      • Replace @TOS_SCHEMA@ with the TOS schema value you choose, such as TOS. Later, you must enter this TOS schema value in the Target Object Store database schema name field on the Configure databases page of the Deployment Environment wizard.
      • Run the following command to create the Content database:

        createDatabase_ECM.sql

   2. Follow the instructions in Creating users and schemas for SQL Server databases in network environment on Windows to create one login and a user with the same name (such as sqluser) for each database.
      Later, you must enter this name on the Configure databases page of the Deployment Environment wizard.
   3. Create the three required schema files. Copy the install_root\BPM\dbscripts\SQLServer\Create\createSchema_ECM.sql file to three files in your working directory. Name them createSchema_DOS.sql (for the Design Object Store), createSchema_TOS.sql (for the Target Object Store), and createSchema_ICN.sql (for IBM Content Navigator). Later, you must enter these schema names on the Configure databases page of the Deployment Environment wizard.
      In createSchema_DOS.sql:

      • Replace @SCHEMA@ with the DOS schema value you chose in substep a.
      • Replace @ECMClient_DBUSER@ with the user name you created in substep b.

      In createSchema_TOS.sql:

      • Replace @SCHEMA@ with the TOS schema value you chose in substep a.
      • Replace @ECMClient_DBUSER@ with the user name you created in substep b.

      In createSchema_ICN.sql:

      • Replace @SCHEMA@ with the IBM Content Navigator schema value you choose, such as ICN.
      • Replace @ECMClient_DBUSER@ with the user name you created in substep b.
   4. Run the following commands:

      
      createSchema_DOS.sql
      createSchema_TOS.sql
      createSchema_ICN.sql

      Change the schema owner for the three schemas to the user you created instead of the default user (dbo). Open the SQL Server Management Studio and right-click each schema name. On the General tab, click Search for the Schema owner and choose the user name.

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.

1. 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
   • dmgr_profile_root\dbscripts\cell_name.deployment_environment_name\SQLServer\CPEDB\schema_name

   These directories contain the createDatabase.sql and createDatabase_ECM.sql scripts 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.

2. 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.
3. For the Content database, create three copies of the createUser.sql file and name them:

   createUser_DOS.sql
   createUser_TOS.sql
   createUser_ICN.sql

   In the createUser_DOS.sql and createUser_TOS.sql files, uncomment the following lines:

   --EXEC sp_addrolemember 'db_securityadmin', @DB_USER@;
   --EXEC sp_addsrvrolemember @DB_USER@, 'bulkadmin';

   by removing the dashes:

   EXEC sp_addrolemember 'db_securityadmin', @DB_USER@;
   EXEC sp_addsrvrolemember @DB_USER@, 'bulkadmin';

   Tips:

   • In some Content database scripts, there is a @DB_DIR@ variable that should be replaced manually. Use the backslash ("\") to replace the forward slash as the file separator.
   • If you are using the same login user or database user for the ICN, DOS, and TOS databases, modify the SQL scripts to avoid SQL errors about creating duplicate login users or database users.
   To create the Content database, run the following commands on your local or remote database server:

   createDatabase_ECM.sql
   createUser_DOS.sql
   createUser_TOS.sql
   createUser_ICN.sql
   createSchema_ECM_DOS.sql
   createSchema_ECM_TOS.sql
   createSchema_ICN.sql

   Change the schema owner for the three schemas to the user you created instead of the default user (dbo). Open the SQL Server Management Studio and right-click each schema name. On the General tab, click Search for the Schema owner and choose the user name.