Manually upgrading the product databases

IBM BPM, V8.0.1, All platforms > Migrating and upgrading your IBM BPM environment > Migrating from previous versions > Migrating your IBM BPM Advanced V7.5.x or WebSphere Process Server V7.x or V6.2.x runtime > Runtime migration subprocedures > Migrating databases
Manually upgrading the product databases

Use this procedure to upgrade the product databases or product database schema if they are not updated automatically as part of starting the server.

Use this procedure to generate and run the SQL scripts that are required to upgrade the product databases or product database schema manually.

Procedure

Run the BPMGenerateUpgradeSchemaScripts command-line utility to generate SQL scripts for each database that is to be upgraded.
To obtain a list of databases, open the DatabaseInfo.txt file in the snapshot_directory. Run the BPMGenerateUpgradeSchemaScripts command for each component in the file.

On a UNIX system, ensure that all extracted files have execute permission for the logged-in user before running the BPMGenerateUpgradeSchemaScripts command.
If they do not, use the chmod command to grant execute permission for all extracted files.
The DatabaseInfo.txt file lists the deployment targets with the corresponding component mapping to a database schema. The values are defined as name- value pairs, where name is DeploymentTargetType_ DeploymentTargetName_ ComponentName and value is DatabaseName. SchemaName.
To upgrade the database for a specific component, check for the component name in the first column and run the script against the corresponding DatabaseName. SchemaName.
The SQL scripts are generated in the following locations:

snapshot_directory/ DB Type/ Database name. Schema name
snapshot_directory\ DB Type\ Database name. Schema name

Copy the scripts generated under snapshot_directory/db_type to a database computer, from which upgrades can be performed using an SQL session, once the scripts have been generated.
Run the SQL scripts. Use one of these methods to run the scripts:

Run the SQL scripts using the upgradeSchema.bat or upgradeSchema.sh file that was generated along with the SQL scripts.
Run the SQL scripts using an SQL session with special configuration. See Running SQL upgrade scripts.

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 to keep this data, you can either run the dropSchema.bat or dropSchema.sh file that was generated along with the SQL scripts, or run the dropSchema_Observer.sql script and then the dropTablespace_Observer.sql script using an SQL session with special configuration.
If your source environment is V7.0.0.3 or V7.0.0.4 or later minor versions, you will have two versions of upgrade schema scripts generated for the BPC database. If you enabled the feature "Shared Work Items" that was introduced in V7.0.0.3, you must remove those generated scripts with "700" in the file name before executing upgradeSchema.bat or upgradeSchema.sh.
If that is not the case you must remove the script with "7003" in the file name.
Remember: There are two types of upgrade schema scripts generated for the BPC database, if the database type is an MS SQLServer. One is ugradeSchemaXXX.sql (where "XXX" is the source version) and the other is upgradeSchemaXXXUnicode.sql . The upgradeSchemaXXXUnicode.sql script is for an MS SQL Server with Unicode support. You must remove the upgradeSchemaXXX.sql script before executing upgradeSchema.bat or upgradeSchema.sh if the MS SQLServer in your environment is Unicode supported. If the MS SQLServer in your environment is not Unicode supported, you must remove the upgradeSchemaXXXUnicode.sql script.
Check the result.log file that is generated during execution for errors.
Locate the database upgrade scripts for every component that requires a database upgrade. These scripts are generated in component-specific directories (WAS_HOME/dbscripts/ component_name/DB2zOS) on the target server:

Common database scripts:
WAS_HOME/dbscripts/CommonDB/DB2zOS
Business Process Choreographer database scripts:
WAS_HOME/dbscripts/ProcessChoreographer/DB2zOS
Business Space database scripts:
WAS_HOME/dbscripts/BusinessSpace/DB2zOS

Copy the following scripts from the WAS_HOME/dbscripts/ component_name/DB2zOS directories to your working directory, as appropriate for the product version from which you are migrating:

For the Common database, you must manually upgrade the database only if you are migrating from WebSphere Process Server V6.2.x.
If this is the case, copy the following scripts:

WAS_HOME/dbscripts/CommonDB/DB2zOS/upgradeSchema620_CommonDB.sql
WAS_HOME/dbscripts/CommonDB/DB2zOS/upgradeSchema620_governancerepository.sql

For the Business Process Choreographer database, you must manually upgrade the database only if you are migrating from WebSphere Process Server V6.2.x or V7.0.x, or IBM BPM V7.5.0.1.

For WebSphere Process Server V6.2.x, copy the following scripts:

WAS_HOME/dbscripts/ProcessChoreographer/DB2zOS/upgradeSchema620.sql
WAS_HOME/dbscripts/ProcessChoreographer/DB2zOS/upgradeTablespace620.sql

For WebSphere Process Server V7.0.0.3 or later V7.0.0.x versions where the "Shared Work Items" feature was enabled in your source environment, copy the following scripts:

WAS_HOME/dbscripts/ProcessChoreographer/DB2zOS/upgradeSchema7003.sql
WAS_HOME/dbscripts/ProcessChoreographer/DB2zOS/upgradeTablespace7003.sql

For WebSphere Process Server 7.0.x versions that are earlier than V7.0.0.3, or for WebSphere Process Server V7.0.0.3 or later 7.0.0.x versions where the "Shared Work Items" feature was not enabled in your source environment, copy the following scripts:

WAS_HOME/dbscripts/ProcessChoreographer/DB2zOS/upgradeSchema700.sql
WAS_HOME/dbscripts/ProcessChoreographer/DB2zOS/upgradeTablespace700.sql

For IBM BPM V7.5.0.1, copy the following script:

WAS_HOME/dbscripts/ProcessChoreographer/DB2zOS/upgradeSchema7500.sql

For the Business Space database, you must manually upgrade the database only if you are migrating from WebSphere Process Server V6.2.x or IBM BPM V7.5.x.

For WebSphere Process Server V6.2.x, copy the following script:

WAS_HOME/dbscripts/BusinessSpace/DB2zOS/upgradeSchema620_BusinessSpace.sql

For IBM BPM V7.5.0.1, copy the following script:

WAS_HOME/dbscripts/BusinessSpace/DB2zOS/upgradeSchema750_BusinessSpace.sql

For IBM BPM V7.5.1, copy the following script:

WAS_HOME/dbscripts/BusinessSpace/DB2zOS/upgradeSchema751_BusinessSpace.sql

Ensure that the copied 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 converting from ASCII to EBCDIC, check that no SQL statements exceed 72 characters in length, and fix if necessary. Otherwise, longer lines will lead to line truncation and invalid statements when you run the scripts.
Review each of the scripts, and, if necessary, edit the files to replace the following symbolic variables with the actual values for the schema name, database name, tablespace prefix, storage group, and buffer pool for indexes, tables, or large objects.

Common database scripts:

@SCHEMA@ = DB2 schema name (SQLID)
@DB_NAME@ = DB2 database name
@STOGRP@ = DB2 storage group name
@BPTABLE4K@ = Buffer pool of 4K size for tables

Business Process Choreographer scripts:

@SCHEMA@ = DB2 schema name (SQLID)
@DB_NAME@ = DB2 database name
@STOGRP@ = DB2 storage group name
@BPLOB4K@ = Buffer pool of 4K size for large objects

Business Space scripts:

@SCHEMA@ = DB2 schema name (SQLID)
@DB_NAME@ = DB2 database name
@STOGRP@ = DB2 storage group name
@TSPRE@ = Prefix for tablespace names
@BPINDEX@ = Buffer pool for indexes
@BPTABLE32K@ = Buffer pool of 32K size for tables
@BPLOB4K@ = Buffer pool of 4K size for large objects

Connect to the database and run the customized scripts against the database by using your preferred tool; for example, SPUFI or in a batch job.
If you have to run both upgradeTablespace*.sql and upgradeSchema*.sql, run upgradeTablespace*.sql first.

Results
While upgrading your Business Space databases, you might see the following message informing you that there is an exception:
SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000
If you see this message, ignore it and continue with the upgrade.

Migrating databases

Related tasks:
Migrating the Common database
Migrating the Business Process Choreographer database
Migrating the Business Space database
Running SQL upgrade scripts
Migrating the Business Space database data (V6.x)
Migrating the Business Space V7.0.x database and data

Related reference:
BPMGenerateUpgradeSchemaScripts command-line utility