Generating SQL scripts for DB2 for z/OS and configuring new database components

For DB2 for z/OS, generate the upgrade scripts and drop the messaging engine tables if you want to reuse your previous messaging engine database. You must create new messaging engine tables manually because DB2 for z/OS messaging engine tables cannot be created during server startup.

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.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	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 createTablespace_Monitor.sql createSchema_Monitor.sql createSchema_Messaging.sql createTable_ProcessServer.sql createProcedure_ProcessServer.sql createTable_PerformanceDW.sql	upgradeSchema700DB2zOSV7_ProcessChoreographer.sql upgradeSchema700DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeTablespace700DB2zOSV7_ProcessChoreographer.sql upgradeTablespace700DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema7003DB2zOSV7_ProcessChoreographer.sql upgradeSchema7003DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeTablespace7003DB2zOSV7_ProcessChoreographer.sql upgradeTablespace7003DB2zOSV7_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 createTablespace_Monitor.sql createSchema_Monitor.sql createSchema_Messaging.sql	upgradeSchema700DB2zOSV7_ProcessChoreographer.sql upgradeSchema700DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeTablespace700DB2zOSV7_ProcessChoreographer.sql upgradeTablespace700DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema7003DB2zOSV7_ProcessChoreographer.sql upgradeSchema7003DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeTablespace7003DB2zOSV7_ProcessChoreographer.sql upgradeTablespace7003DB2zOSV7_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 createTablespace_Monitor.sql createSchema_Monitor.sql createSchema_Messaging.sql createTable_ProcessServer.sql createProcedure_ProcessServer.sql createTable_PerformanceDW.sql	upgradeSchema700DB2zOSV7_ProcessChoreographer.sql upgradeSchema700DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeTablespace700DB2zOSV7_ProcessChoreographer.sql upgradeTablespace700DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema620DB2zOSV7_ProcessChoreographer.sql upgradeSchema620DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeTablespace620DB2zOSV7_ProcessChoreographer.sql upgradeTablespace620DB2zOSV7_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 createTablespace_Monitor.sql createSchema_Monitor.sql createSchema_Messaging.sql	upgradeSchema700DB2zOSV7_ProcessChoreographer.sql upgradeSchema700DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeTablespace700DB2zOSV7_ProcessChoreographer.sql upgradeTablespace700DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeSchema620DB2zOSV7_ProcessChoreographer.sql upgradeSchema620DB2zOSV7_ProcessChoreographerArchive.sql (if you configured Business Process Choreographer Archive in the source environment) upgradeTablespace620DB2zOSV7_ProcessChoreographer.sql upgradeTablespace620DB2zOSV7_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 any new databases that you require.
1. Use FTP to transfer the database scripts, which were generated by the BPMConfig script, to the z/OS® system that contains the DB2® for z/OS installation. When you ran BPMConfig to create the deployment environment, the files for creating each of the databases were generated into separate subdirectories for ease of execution. The default location of the database scripts is:
  - DMGR_PROFILE/dbscripts/de_name/DB2zOS/cluster_database_name
  Transfer the createDatabase.sh script as an ASCII text file, and transfer the database schema files in binary mode. Also ensure that you retain the directory structure when you transfer the files.
2. Create the required buffer pools. For more information, see Sample DB2 commands for allocating buffer pools.
3. DB2 system administrator (SYSADM) Create the physical databases and storage groups, and grant DBADM authority to a WebSphere user that is identified as the owner of the databases:
  - Create any cluster-scoped databases and storage groups that you require, and grant the WebSphere administrator DBADM access to the databases.
    If your target environment is an Advanced deployment environment, create new databases for Process Server and Performance Data Warehouse because these components require their own separate databases and cannot be configured on the same database as the other IBM Business Process Manager components. The default database names are BPMDB for the Process database, and PDWDB for the Performance Data Warehouse database.
    
    If you do not have Business Space configured in the source environment, you can create a new database for the Business Space component, or you can use one of your existing databases.
    
    You can create a new Common database for the deployment environment or you can use one of your existing databases.
    
    Tip: To help you determine which databases need to be created, review the database property settings that were specified in the BPMConfig properties file that was used to create the target deployment environment.
  To create each database that you require, you can use the createDatabase.sql files that were generated by the BPMConfig script, and which are on the z/OS system that contains the DB2 installation. These files contain the relevant CREATE and GRANT statements for the databases. Use the DB2 command line processor to run the relevant createDatabase.sql files on the database server; for example:
```
db2 -tvf cluster_scoped_subdir/createDatabase.sql
```
To initialize your new database components, run the create*.sql files that were generated when you ran the BPMGenerateUpdateSchemaScripts utility.
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. Connect to each database and run the customized SQL files against the database. You can use the BPMConfig properties file to help you determine the databases against which to run the SQL.
  If your source environment was not configured for Business Space, configure the Business Space objects by running these SQL files against a new Business Space database (if one was created) or against another database on which you want to configure the Business Space objects. Run the files 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 the Process database by running these files against the new Process database. Run the files in the following order:
    1. createTable_ProcessServer.sql
    2. createProcedure_ProcessServer.sql
  - Configure the Performance Data Warehouse database by running the following file against the new Performance Data Warehouse database: createTable_PerformanceDW.sql.
  For example, you can connect to the Business Space database and run the SQL as follows:
```
db2 connect to BSpace_database_name USER user_name USING password
db2 -tvf zos_directory_path/createTablespace_BusinessSpace.sql
db2 -tvf zos_directory_path/createSchema_BusinessSpace.sql
db2 -tvf zos_directory_path/createTable_BusinessSpace.sql
db2 connect reset
```
3. To configure additional tables to support IBM Business Monitor capability in V8.5, run these SQL files in the following order:
  1. createTablespace_Monitor.sql
  2. createSchema_Monitor.sql
  You can run these files against the database that is configured with Business Space capability. These files are in deployment_manager_profile/dbscripts/Upgrade/deployment_environment_name/database_type/Business_Space_database_name.Business_Space_schema_name.
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. To configure the CommonDB component at the deployment environment level, run the createSchema_CommonDB.sql file against the cluster-scoped database. This SQL creates the event sequencing and failed event tables.
6. To create the messaging engine tables, complete the following steps:
  1. If you are reusing your previous messaging engine database and schema, manually drop the existing messaging engine tables.
    Tip: The messaging engine table names use the SIB prefix.
  2. Run the createSchema_Messaging.sql file to re-create the tables manually on the database where you want to configure Messaging. This file is in deployment_manager_profile/dbscripts/Upgrade/deployment_environment_name/database_type/Messaging_engine_database_name.Messaging_engine_schema_name.