Upgrading DB2 databases

For DB2, initialize new databases and upgrade your existing schemas and data so that your databases work with the new version of IBM Business Automation Workflow.

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. Copy the whole folder target_deployment_manager_profile\dbscripts\Upgrade\ to your database computer.
2. If you did not create a new messaging engine database and instead you plan to reuse your previous messaging engine database and schema, you must manually drop the existing messaging engine tables for each one.
   Tip: The messaging engine table names use the SIB prefix.
   If you see any of the following error messages, follow the instructions in the message to increase the default log file size.

   CWMCB0350E: Database CMN172DB does not have defined a sufficient logfile size. Use UPDATE DB CFG FOR CMN172DB USING LOGFILSIZ 16384 DEFERRED to change. Actual size = 4096, minimum size = 16384.
   CWMCB0350E: Database PS172DB does not have defined a sufficient logfile size. Use UPDATE DB CFG FOR PS172DB USING LOGFILSIZ 16384 DEFERRED to change. Actual size = 4096, minimum size = 16384.
   CWMCB0350E: Database PDW172DB does not have defined a sufficient logfile size. Use UPDATE DB CFG FOR PDW172DB USING LOGFILSIZ 16384 DEFERRED to change. Actual size = 4096, minimum size = 16384.
   CWMCB0350E: Database BPC172DB does not have defined a sufficient logfile size. Use UPDATE DB CFG FOR BPC172DB USING LOGFILSIZ 16384 DEFERRED to change. Actual size = 4096, minimum size = 16384.

3. On the database computer, upgrade all schemas. To see which schemas are upgraded, go to the directory where you copied the Upgrade folder and see the upgradeSchemaScriptsHelp_de_name.txt file.

   Go to the directory where you copied the Upgrade folder and run the upgradeSchemaAll command. There is a different upgradeSchemaAll command for each deployment environment in the source.

   upgradeSchemaAll_de_name.bat

   You are prompted to enter the user name and password for each database connection. This command initializes the new database components and upgrades the schemas of all the existing databases except for the Workflow Server and Performance Data Warehouse databases. Those two databases are upgraded later by the DBUpgrade command.

   Alternatively, if you want to run the SQL scripts manually, use an SQL session and run the scripts in the sequence listed in the Upgrade_folder\upgradeSchemaScriptsHelp_de_name.txt file and use the following parameters and commands.

   db2 -s -t -v -z log name  -f  script_name

   where:

   • -s specifies that the script is to exit as soon as the first error occurs
   • -t specifies that the statement is to be printed on the command line so that it can be logged in a log file
   • -v specifies that the output from the command is to be sent to a log file that can be used for error checking
   • -z specifies that the semicolon (;) is to be treated as a delimiter for the commands in the file
   • -f specifies the input file name
   Important: If you are migrating from IBM BPM Express, you must run the table creation file for IBM Content Navigator. The file is in Upgrade_folder\cell_name.DE_name\db_type\icn_database.icn_schema\createTable_ICN.sql.

   You might see warning messages when you run the scripts to upgrade the Business Space database telling you that the result of a query is an empty table or that no row was found for FETCH, UPDATE, or DELETE. These messages can safely be ignored.

   The result.log files are found in Upgrade_folder\cell_name or cell_name.de_name\database_type\database_name.schema_name.

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. To upgrade the databases to V19.0.0.3, run the DBUpgrade utility on the deployment manager computer in the target environment. The DBUpgrade command automatically upgrades the schema and data for Workflow Server and Performance Data Warehouse.
   Tip: By default, DBUpgrade upgrades both the schema and data for Workflow Server and Performance Data Warehouse databases. For instructions for running the schema update separately, see the DBUpgrade troubleshooting topic.
   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.bat -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.bat -propertiesFile "C:\BPM V19.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.bat 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.

6. If you did not increase the default log file size, increase it by using the following SQL statement, where @DB_NAME@ is the name of your Workflow Server database.

   UPDATE DB CFG FOR @DB_NAME@ USING LOGFILSIZ 16384 DEFERRED;