Generating SQL scripts for SQL Server and configuring new database components

For SQL Server, generate the upgrade script and configure the new database components you require. Drop the messaging engine tables if you want to reuse your previous messaging engine database.

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

For each deployment environment that you are creating, complete the following steps:

Copy the source_migration.properties file and rename it to target_migration.properties. Update the file with the configuration information for the target environment. You need a different migration properties file for each deployment 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 subdirectory:

deployment_environment_name\database_type\database_name.schema_name

The BPMGenerateUpdateSchemaScripts utility generates the following SQL scripts for migrating to IBM® BPM Advanced V8.5:

Table 1. Generated SQL scripts
Source Version	Target Version	Create SQL Scripts	Upgrade SQL Scripts
WebSphere® Process Server V7.0.0.3 or later	`Advanced` (full Process Center and Process Server functionality)	createTablespace_BusinessSpace.sql (not in all environments) createSchema_BusinessSpace.sql (not in all environments) createTable_BusinessSpace.sql (not in all environments) createSchema_CommonDB.sql createSchema_Monitor.sql createSchema_Messaging.sql createTable_ProcessServer.sql createProcedure_ProcessServer.sql createTable_PerformanceDW.sql	upgradeSchema700Unicode_ProcessChoreographer.sql upgradeSchema700Unicode_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema700_ProcessChoreographer.sql upgradeSchema700_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema7003Unicode_ProcessChoreographer.sql upgradeSchema7003Unicode_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema7003_ProcessChoreographer.sql upgradeSchema7003_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema_SchemaStatus.sql preMigrateBusinessSpace700.sql (if you configured Business Space in the source environment migrateBusinessSpaceSchema700.sql (if you configured Business Space in the source environment postMigrateBusinessSpace700.sql (if you configured Business Space in the source environment dropSchema_Observer.sql (optional) dropTablespace_Observer.sql (optional)
WebSphere Process Server V7.0.0.3 or later	`AdvancedOnly` (the IBM BPM equivalent of WebSphere Process Server	createTablespace_BusinessSpace.sql (not in all environments) createSchema_BusinessSpace.sql (not in all environments) createTable_BusinessSpace.sql (not in all environments) createSchema_CommonDB.sql createSchema_Monitor.sql createSchema_Messaging.sql	upgradeSchema700Unicode_ProcessChoreographer.sql upgradeSchema700Unicode_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema700_ProcessChoreographer.sql upgradeSchema700_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema7003Unicode_ProcessChoreographer.sql upgradeSchema7003Unicode_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema7003_ProcessChoreographer.sql upgradeSchema7003_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema_SchemaStatus.sql preMigrateBusinessSpace700.sql (if you configured Business Space in the source environment migrateBusinessSpaceSchema700.sql (if you configured Business Space in the source environment postMigrateBusinessSpace700.sql (if you configured Business Space in the source environment dropSchema_Observer.sql (optional) dropTablespace_Observer.sql (optional)
WebSphere Process Server V7.0.0.2 or earlier	`Advanced` (full Process Center and Process Server functionality)	createTablespace_BusinessSpace.sql (not in all environments) createSchema_BusinessSpace.sql (not in all environments) createTable_BusinessSpace.sql (not in all environments) createSchema_CommonDB.sql createSchema_Monitor.sql createSchema_Messaging.sql createTable_ProcessServer.sql createProcedure_ProcessServer.sql createTable_PerformanceDW.sql	upgradeSchema700Unicode_ProcessChoreographer.sql upgradeSchema700Unicode_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema700_ProcessChoreographer.sql upgradeSchema700_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema620Unicode_ProcessChoreographer.sql upgradeSchema620Unicode_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema620_ProcessChoreographer.sql upgradeSchema620_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema_SchemaStatus.sql preMigrateBusinessSpace700.sql (if you configured Business Space in the source environment migrateBusinessSpaceSchema700.sql (if you configured Business Space in the source environment postMigrateBusinessSpace700.sql (if you configured Business Space in the source environment dropSchema_Observer.sql (optional) dropTablespace_Observer.sql (optional)
WebSphere Process Server V7.0.0.2 or earlier	`AdvancedOnly` (the IBM BPM equivalent of WebSphere Process Server	createTablespace_BusinessSpace.sql (not in all environments) createSchema_BusinessSpace.sql (not in all environments) createTable_BusinessSpace.sql (not in all environments) createSchema_CommonDB.sql createSchema_Monitor.sql createSchema_Messaging.sql	upgradeSchema700Unicode_ProcessChoreographer.sql upgradeSchema700Unicode_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema700_ProcessChoreographer.sql upgradeSchema700_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema620Unicode_ProcessChoreographer.sql upgradeSchema620Unicode_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema620_ProcessChoreographer.sql upgradeSchema620_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema_SchemaStatus.sql preMigrateBusinessSpace700.sql (if you configured Business Space in the source environment migrateBusinessSpaceSchema700.sql (if you configured Business Space in the source environment postMigrateBusinessSpace700.sql (if you configured Business Space in the source environment dropSchema_Observer.sql (optional) dropTablespace_Observer.sql (optional)

Based on the properties that you chose when you created your deployment environment, create the new databases that you require. For Process Server and Performance Data Warehouse databases, run:
```
BPM_home\BPM\dbscripts\SQLServer\Create\createDatabase_CaseInsensitive.sql
```
For other new databases, run:
```
BPM_home\BPM\dbscripts\SQLServer\Create\createDatabase_CaseSensitive.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.
  If your source environment was not configured for Business Space, you must 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 your target environment is IBM BPM Advanced with full Process Center and Process Server functionality , complete the following tasks:
  - Configure a new Process database by running the following scripts:
    - createTable_ProcessServer.sql
    - createProcedure_ProcessServer.sql
  - Configure a new Performance Data Warehouse database by running the following script: createTable_PerformanceDW.sql.
  For example:
```
sqlcmd -U username -P password -e -i DmgrProfile/dbscripts/Upgrade/de1/SQLServer/database.schema/createTablespace_BusinessSpace.sql
sqlcmd -U username -P password -e -i DmgrProfile/dbscripts/Upgrade/de1/SQLServer/database.schema/createSchema_BusinessSpace.sql
sqlcmd -U username -P password -e -i DmgrProfile/dbscripts/Upgrade/de1/SQLServer/database.schema /createTable_BusinessSpace.sql
```
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 created a new common database for the deployment-environment-scoped common database:
  1. Run createSchema_CommonDB.sql to create the event sequencing and failed event tables.
  2. If you have data in the event sequencing table (named PERSISTENTLOCK) in the source environment, export the data from the jdbc/WPSDB data source (JNDI) name in the source and import it into the event sequencing table (PERSISTENTLOCK) in the new deployment-environment-scoped common database component. See your database provider documentation for instructions for extracting data from a table in one database and importing it into another database.
  Remember: In BPM V8.5, the common database is split into two pieces. One is cell-scoped and is used for the entire cell. The other, which includes event sequencing and the failed event manager, is deployment-environment-scoped, and must be configured for each deployment environment.
  
  You are not required to create the event sequencing and failed event tables if you are using the old common database for the deployment-environment-scoped common database.
5. 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.