Generating SQL scripts for DB2 and configuring new database components

For DB2, generate the upgrade scripts 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.

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.

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

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

2. 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.sh -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	Create SQL Scripts	Upgrade SQL Scripts
   IBM BPM Advanced V8.0.1	createSchema_Monitor.sql createSchema_CommonDB.sql (optional) createSchema_Messaging.sql (optional)	upgradeSchema_ProcessServer.sql upgradeSchema_PerformanceDW.sql
   IBM BPM Advanced V8.0.0	createSchema_Monitor.sql createSchema_CommonDB.sql (optional) createSchema_Messaging.sql (optional)	upgradeSchema_ProcessServer.sql upgradeSchema_PerformanceDW.sql dropSchema_Observer.sql (optional) dropTablespace_Observer.sql (optional)
   IBM BPM Advanced V7.5.1	createSchema_Monitor.sql createSchema_CommonDB.sql (optional) createSchema_Messaging.sql (optional)	upgradeSchema751_BusinessSpace.sql upgradeSchema_SchemaStatus.sql upgradeSchema_ProcessServer.sql upgradeSchema_PerformanceDW.sql dropSchema_Observer.sql (optional) dropTablespace_Observer.sql (optional)
   IBM BPM Advanced V7.5.0	createSchema_Monitor.sql createSchema_CommonDB.sql (optional) createSchema_Messaging.sql (optional)	upgradeSchema7500_ProcessChoreographer.sql upgradeSchema750_BusinessSpace.sql upgradeSchema_SchemaStatus.sql upgradeSchema_ProcessServer.sql upgradeSchema_PerformanceDW.sql dropSchema_Observer.sql (optional) dropTablespace_Observer.sql (optional)

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

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

   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.