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

For DB2 for z/OS, 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. 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.

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.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	Create SQL Scripts	Upgrade SQL Scripts
   IBM BPM Advanced V8.0.1	createSchema_CommonDB.sql createTablespace_Monitor.sql createSchema_Monitor.sql createSchema_Messaging.sql	upgradeSchema801_ProcessServer.sql upgradeSchema801_PerformanceDW.sql
   IBM BPM Advanced V8.0.0	createSchema_CommonDB.sql createTablespace_Monitor.sql createSchema_Monitor.sql createSchema_Messaging.sql	upgradeSchema800_ProcessServer.sql upgradeSchema800_PerformanceDW.sql dropSchema_Observer.sql dropTablespace_Observer.sql
   IBM BPM Advanced V7.5.1	createSchema_CommonDB.sql createTablespace_Monitor.sql createSchema_Monitor.sql createSchema_Messaging.sql	upgradeSchema751_BusinessSpace.sql upgradeSchema_SchemaStatus.sql upgradeSchema751_ProcessServer.sql upgradeSchema751_PerformanceDW.sql dropSchema_Observer.sql (optional) dropTablespace_Observer.sql (optional)
   IBM BPM Advanced V7.5.0	createSchema_CommonDB.sql createTablespace_Monitor.sql createSchema_Monitor.sql createSchema_Messaging.sql	upgradeSchema7500_ProcessChoreographer.sql upgradeSchema7500DB2zOSV7_ProcessChoreographer.sql upgradeSchema750_BusinessSpace.sql upgradeSchema_SchemaStatus.sql upgradeSchema750_ProcessServer.sql upgradeSchema750_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 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.

        For example, you might want to create a new deployment-environment-scoped Common database.

        Tip: To help you determine which databases need to be created, you can 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

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

      You can then connect to each database and run the customized SQL files against the database.

   2. 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.
      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_Monitor.sql
      db2 -tvf zos_directory_path/createSchema_Monitor.sql
      db2 connect reset

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