Generating SQL scripts for DB2 and configuring new database components

For DB2, generate the upgrade scripts. If you are migrating to IBM® BPM Standard, create and configure a Business Space database. If you are migrating to IBM BPM Advanced, configure the new database components you require, based on the properties that you chose when you created your deployment environment.

Figure 1. Sample environment after new schemas are created. The source environment is not running and the databases are not in use. The databases contain new and existing schemas. The target is not running but contains a deployment environment.

The details of the diagram are provided in the figure caption.

Run the BPMGenerateUpdateSchemaScripts command from the target environment to generate the SQL scripts to update the schemas. Then, initialize your new database components by running the creation script or scripts that are generated. You will run the update scripts later, in the "Updating existing databases" step.

Procedure

Copy the source_migration.properties file and rename it to target_migration.properties. Update the file with the configuration information for the target environment. Check all the properties and edit them if required, following the instructions in the file. Ensure that the value of the target.config.property.file property is set to the full path of the configuration properties file that you used to create your target environment. You must also set the value of source.product.version.
The file is found in BPM_home\util\migration\resources\migration.properties

Run the BPMGenerateUpgradeSchemaScripts command-line utility from the target environment to generate the SQL scripts. Run the command using the following syntax:

BPM_home\bin\BPMGenerateUpgradeSchemaScripts.bat -propertiesFile migration_properties_file

where:

migration_properties_file is the full path to the migration properties file in which you specified the configuration information for the target environment. The file is found in BPM_home/util/migration/resources/migration.properties.

You should specify Yes if you had the Reporting function configured and want to remove the database schema now.

The SQL scripts are generated in the target_deployment_manager_profile\dbscripts\Upgrade directory, under the following subdirectories:

Available for any target environment: deployment_environment_name\database_type\database_name.schema_name
Available for an IBM BPM Advanced target environment only: cell_name\database_type\database_name.schema_name

The BPMGenerateUpdateSchemaScripts utility generates the following SQL scripts:

Table 1. Generated SQL scripts
Source Version	Target Version	Create SQL Scripts	Upgrade SQL Scripts
WebSphere® Lombardi Edition V7.1.0, 7.1.2, or 7.2.0	IBM BPM Standard V8.5	createTablespace_BusinessSpace.sql (not in all environments) createSchema_BusinessSpace.sql (not in all environments) createTable_BusinessSpace.sql createSchema_Monitor.sql createSchema_Messaging.sql	upgradeSchema_ProcessServer.sql upgradeSchema_PerformanceDW.sql
WebSphere Lombardi Edition V7.1.0, 7.1.2, or 7.2.0	IBM BPM Advanced V8.5	createTablespace_BusinessSpace.sql (not in all environments) createSchema_BusinessSpace.sql (not in all environments) createTable_BusinessSpace.sql createSchema_CommonDB.sql createSchema_CommonDBCellOnlyDB.sql createTablespace_ProcessChoreographer.sql (not in all environments) createSchema_ProcessChoreographer.sql createSchema_Monitor.sql createSchema_Messaging.sql	upgradeSchema_ProcessServer.sql upgradeSchema_PerformanceDW.sql

Based on the properties that you chose when you created your deployment environment, create the new databases that you require. For each new database, make any required changes and run the following script:
```
BPM_home\BPM\dbscripts\DB2\Create\createDatabase.sql
```
To initialize your new database components, run the create SQL scripts that you generated in the previous step. You will run the upgrade scripts later, in the "Upgrading existing databases" step.
1. Copy the create scripts for the new database components from target_deployment_manager_profile\dbscripts\Upgrade\deployment_environment_name\database_type\database_name.schema_name to your database computer.
2. Run the scripts for each of your new database components.
  Configure a new Business Space database by running the scripts in the following order:
  1. createTablespace_BusinessSpace.sql (if it exists)
  2. createSchema_BusinessSpace.sql (if it exists)
  3. createTable_BusinessSpace.sql
  If you are migrating to IBM BPM Advanced, you must also complete the following tasks:
  - Configure a new deployment-environment-scoped common database by running the following script: createSchema_CommonDB.sql.
  - Configure a new cell-scoped common database by running the following script: createSchema_CommonDBCellOnlyDB.sql.
  - Configure a new Business Process Choreographer database by running the scripts in the following order:
    1. createTablespace_ProcessChoreographer.sql (if it exists)
    2. createSchema_ProcessChoreographer.sql
  For example:
```
db2 connect to CMNDB USER username USING password
db2 -tvf profiles/DmgrProfile/dbscripts/Upgrade/de1/DB2/CMNDB.schema/createTablespace_BusinessSpace.sql
db2 -tvf profiles/DmgrProfile/dbscripts/Upgrade/de1/DB2/CMNDB.schema/createSchema_BusinessSpace.sql
db2 -tvf profiles/DmgrProfile/dbscripts/Upgrade/de1/DB2/CMNDB.schema/createTable_BusinessSpace.sql
db2 connect reset
```
3. To configure additional tables for V8.5, run createSchema_Monitor.sql. This file is in deployment_manager_profile\dbscripts\Upgrade\deployment_environment_name\database_type\Business_Space_database_name.Business_Space_database_schema.
4. If you do not want the messaging engine tables to be re-created automatically, run the createSchema_Messaging.sql file. If you want to reuse your previous messaging engine database and schema, you must manually drop the existing messaging engine tables.
  Tip: The messaging engine table names use the SIB prefix.
  - If the Create tables option is enabled on the messaging store, the messaging tables will be re-created automatically when the server is restarted.
  - If the Create tables option is not enabled on the messaging store, you must run the createSchema_Messaging.sql script to re-create the tables manually. This file is in deployment_manager_profile\dbscripts\Upgrade\deployment_environment_name\database_type\messaging_engine_database_name.messaging_engine_database_schema.