Upgrading DB2 for z/OS databases

For DB2 for z/OS databases, increase the buffer size for table spaces, initialize new databases, and upgrade your existing schemas and data.

Run the DBUpgrade command to modify your existing database schemas and data for use with IBM Business Automation Workflow V19.0.0.3. The DBUpgrade utility updates the following items to V19.0.0.3:

• System Data toolkit
• Heritage Process Portal process application
• Hiring Sample tutorial process application

Important: Although the DBUpgrade utility installs the System Data toolkit V19.0.0.3, the utility does not automatically update existing dependencies. Therefore, test your applications after migration. If they are not compatible with the new version of Business Automation Workflow, you can update the dependencies so that your applications continue to use the previous version.

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

1. To initialize your new database components, run the create*.sql files that were generated when you ran the BPMConfig -create command.
   1. Copy the create*.sql scripts for the new database components from target_deployment_manager_profile/dbscripts/Upgrade/ 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 you are migrating from IBM BPM Standard V7.5.x, configure the Business Space objects by running the SQL files against the new Business Space database 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, complete the following tasks:

      • Configure the deployment-environment-scoped CommonDB objects by running the following file: createSchema_CommonDB.sql. You can run this file against a new deployment-environment-scoped Common database (if one was created) or against another database on which you want to configure the deployment-environment-scoped CommonDB objects.
      • Configure the new cell-scoped Common database by running the following file: createSchema_CommonDBCellOnlyDB.sql. You can run this file against the new cell-scoped database.
      • Configure the Business Process Choreographer objects by running the files in the following order:
        1. createTablespace_ProcessChoreographer.sql (if it exists)
        2. createSchema_ProcessChoreographer.sql
        You can run these files against a new Business Process Choreographer database (if one was created) or against another database on which you want to configure the Business Process Choreographer objects.
      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 deployment_manager_profile/dbscripts/Upgrade/cell_name.de_name/database_type/Business_Space_database_name.Business_Space_schema_name/createTablespace_BusinessSpace.sql
      db2 -tvf deployment_manager_profile/dbscripts/Upgrade/cell_name.de_name/database_type/Business_Space_database_name.Business_Space_schema_name/createSchema_BusinessSpace.sql
      db2 -tvf deployment_manager_profile/dbscripts/Upgrade/cell_name.de_name/database_type/Business_Space_database_name.Business_Space_schema_name/createTable_BusinessSpace.sql
      db2 connect reset

   3. 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/cell_name.de_name/database_type/Messaging_engine_database_name.Messaging_engine_schema_name.
2. For Process and Performance Data Warehouse databases, complete the following steps for your DB2 for z/OS database:
   1. To ensure that you can successfully run the SQL scripts for the DB2® for z/OS® schema upgrade, alter the following table spaces to increase the buffer pool size to 8K:
      • WLPT33
      • WLPT34
      • WLPT52
      • WLPT53
      • WLPT134
      • WLPT145

      To do so, drop the table spaces and create them again with 8k buffer pools.

      Note: You should move the data to a temporary table space before dropping it. Then, copy it back when you re-create the table space.

      Example SQL to re-create table spaces:

      SET CURRENT SQLID = 'ZASIPS';
      
      DROP TABLESPACE ZACELLDB.WLPT33;
      DROP TABLESPACE ZACELLDB.WLPT52;
      
      CREATE TABLESPACE WLPT33
      IN ZACELLDB
      USING STOGROUP ZADBSTO
      SEGSIZE 32
      LOCKMAX SYSTEM
      LOCKSIZE ROW
      DEFINE YES
      CCSID UNICODE
      BUFFERPOOL BP8K1;
      
      CREATE TABLESPACE WLPT52
      IN ZACELLDB
      USING STOGROUP ZADBSTO
      SEGSIZE 32
      LOCKMAX SYSTEM
      LOCKSIZE ROW
      DEFINE YES
      CCSID UNICODE
      BUFFERPOOL BP8K1;

   2. From the target_deployment_manager_profile/dbscripts/Upgrade/cell_name.de_name/database_type/ProcessServer_database_name directory, obtain the upgradeSchema_ProcessServer.sql script that corresponds to the product version that you are migrating from.
      For example, if you are migrating from IBM Business Process Manager V7.5.x, copy the script named upgradeSchema751_ProcessServer.sql to your working directory.

      Connect to the DB2 for z/OS database, and run the upgradeSchema75x_ProcessServer.sql or upgradeSchema8xx_ProcessServer.sql script against the database by using your preferred tool. Then, run the newly created createProcedure_ProcessServer.sql script to create the stored procedures.

      You can safely ignore SQL errors about creating duplicate indexes.

      You can safely ignore SQL errors with text similar to the following text: ROW NOT FOUND FOR FETCH, UPDATE, OR DELETE, OR THE RESULT OF A QUERY IS AN EMPTY TABLE. SQLCODE=100, SQLSTATE=02000, DRIVER=3.61.96 for the following SQL statement:

      UPDATE LSW_BPD_INSTANCE SET ATTACHMENT_STORE = 3  WHERE BPD_INSTANCE_ID IN (SELECT DISTINCT  BPD_INSTANCE_ID FROM LSW_BPD_INSTANCE_DOCUMENTS)
      UPDATE LSW_BPD_INSTANCE SET  CASE_FOLDER_SERVER_NAME='IBM_BPM_ContentStore'  WHERE NOT CASE_FOLDER_ID IS NULL
      UPDATE LSW_BPD_ACTIVITY_INSTANCE SET ENABLDOC_SERVER_NAME='IBM_BPM_ContentStore'  WHERE NOT ENABLING_DOCUMENT_ID IS NULL
      UPDATE LSW_BPD_INSTANCE SET  STARTING_DOCUMENT_SERVER_NAME='IBM_BPM_ContentStore'  WHERE NOT STARTING_DOCUMENT_ID IS NULL

      The script is generated in deployment_manager_profile/dbscripts/Upgrade/deployment_environment_name/database_type/database_name.schema_name.

   3. From the target_deployment_manager_profile/dbscripts/Upgrade/cell_name.de_name/database_type/PDW_database_name directory, obtain the upgradeSchema_PerformanceDW.sql script that corresponds to the product version that you are migrating from.
      For example, if you are migrating from Business Automation Workflow V7.5.x, copy the script named upgradeSchema751_PerformanceDW.sql to your working directory.

      Connect to the DB2 for z/OS database, and run the upgradeSchemaPerformancexxx_PerformanceDW.sql script against the database by using your preferred tool.

      You can safely ignore SQL errors about creating duplicate indexes.

   4. Check the status of the table spaces.
      When you run the upgradeSchemaxxx_ProcessServer.sql script, you might see the warning message SQL CODE -162, indicating that the table space is under Check Pending status. Use the following command to identify table spaces that are under Check Pending status:

       -DISPLAY DATABASE(PS_DB_NAME) SPACENAM(*) RESTRICT

      Tip: For improved performance, list the table spaces in AREO* status and use REORG to fix them.

      After each table space is identified, the database administrator can use the CHECK DATA utility to fix it.

      If you are using DB2 on a z/OS system, run the following command:

       Command Prefix DISPLAY DATABASE(PS_DB_NAME) SPACENAM(*) RESTRICT

3. If you are migrating from IBM BPM Standard V8.0.x, complete the following steps for the Business Space database:
   1. Ensure that the scripts have write permissions. If the tool that you want to use to view, edit, and run the scripts requires the scripts to be in EBCDIC format, rather than ASCII format, convert the files to EBCDIC.
      Important: After you convert the files from ASCII to EBCDIC, check that no SQL statements exceed 72 characters in length, and fix if necessary. Longer lines can lead to line truncation and invalid statements when you run the scripts.
   2. Connect to the database and run the customized scripts against the database by using your preferred tool; for example, the DB2 command line processor, SPUFI, or in a batch job.
      You can also use one of these methods to run the scripts:

      • Run the SQL scripts using the upgradeSchema.sh file that was generated along with the SQL scripts.
      • Run the SQL scripts directly using an SQL session.
      To run the SQL scripts directly, run the scripts in the following sequence:

      • Run all upgradeTablespac* scripts before you run any upgradeSchema* scripts.
      • Run the upgradSchema_SchemaStatus.sql script before you run any other "upgradeSchema*" SQL scripts.

      The options are embedded in the SQL scripts. Additional options are not required.

      Note: If you had the Business Process Choreographer Reporting function configured, it was removed during runtime migration. However, the associated data was not automatically removed from the database. If you determine that you no longer need this data, run the dropSchema_Observer.sql script and then the dropTablespace_Observer.sql script using an SQL session with special configuration.
4. Copy the sample migration.properties file and rename it to target_migration.properties. Update the file with the configuration information for the target environment.
   Check all the target properties and edit them if required, following the instructions in the sample file. The sample file is in install_root/util/migration/resources/migration.properties.
   Specify target environment values for the following properties:

   • admin.username and admin.password: Use the cellAdmin user or the WebSphere primary administrative user name.
   • bpm.home: The installation root of your target product. Make sure that the file separators are forward slashes (/). Use the full path. Do not use the tilde character (˜) to stand for the home directory. For example:

     bpm.home=/opt/IBM/BPM
     bpm.home=C:/IBM/BPM

   • profile.name: The target deployment manager profile.
   • target.config.property.file: The full path of the configuration properties file that you used to create your target environment.

   You must specify the source cluster names, not target cluster names, for the following properties:

   • source.application.cluster.name
   • source.messaging.cluster.name
   • source.support.cluster.name
   • source.web.cluster.name
5. Go to the target_install_root/util/dbUpgrade directory and set the database.is.db2zos property to true in the upgrade.properties file.
   For example:

   database.is.db2zos=true

6. To upgrade the databases to V19.0.0.3, run the DBUpgrade utility on the server in the target environment.
   Important: Ensure that your deployment manager and all the managed nodes in the source environment have been stopped before running this utility.

   install_root/bin/DBUpgrade.sh -propertiesFile target_migration_properties_file

   where:

   • target_migration_properties_file is the full path to the migration properties file in which you specified the configuration information for the target environment.
   For example:

   install_root/bin/DBUpgrade.sh -propertiesFile /opt/BPMV19.0.0.3/util/migration/resources/target_migration.properties

   The command displays each database upgrade action as it runs. After all the upgrades are finished, you see a message similar to the following message:

   All upgrade steps have been completed successfully.

   The log location is listed in the output. If there are errors or exceptions, they appear in the log.

   If you are migrating from IBM Business Process Manager V7.5.x and you get an out-of-memory error indicating too many or too large data records, you can try to increase the heap size of the JVM for the DBUpgrade command. Open the DBUpgrade.sh file in install_root/bin and find -Xmx2048m in this file. It indicates that the maximum JVM heap size is 2048 megabytes. You can increase this value to update the heap size.

   If necessary for troubleshooting, you can run DBUpgrade -generateSQLOnly to generate the SQL files without running them. You can then edit the generated SQL files manually and run DBUpgrade -omitSQLGeneration to upgrade the database using the modified files.