Moving workflow system or Process Engine isolated regions

Content

Important!  Please always download the latest   regmove-5.5.11-0-147-20230524.010831-1.zip attached to this technote.  Validate the download using the  MD5 code, MD5 (regmove-5.5.11-0-147-20230524.010831-1.zip) = d4ddf8e6d45ab552d10047f32d1bb1c7.

1 Overview

You can move one or more isolated regions to a new workflow system or Process Engine database or schema. You can move the isolated regions to correct IBM Case Manager environments where the isolated region and the Content Platform Engine target object store are not stored in the same database and schema. You can also reorganize existing isolated region data by moving the isolated region to a new database or schema.

Move isolated regions for the following scenarios:

• Before you upgrade to Content Platform Engine 5.2:
  • For IBM Case Manager 5.0 or 5.1 systems that are not in compliance with the common database or schema requirement (that is, the isolated region and the Content Platform Engine target object store are not stored in the same database and schema), move the Process Engine isolated regions into a Content Engine object store before you upgrade to Content Platform Engine 5.2.
  • Content Platform Engine requires that a DB2 database has a 32K page size. If the isolated region is stored in a DB2 database with a smaller page size, move the isolated regions data to a DB2 database with a 32K page size before you upgrade to Content Platform Engine 5.2.
    Alternatively, you can increase the page size of the current database. For more information, see " How to increase the table space page size or change the character set for a Process Engine database on DB2 for Linux, Unix, and Windows"
• Move a single isolated region from one workflow system to a new workflow system database and schema.

Restriction: You cannot move an isolated region from the database and schema of one workflow system to an existing workflow system database and schema that already contains an isolated region. If you want to move multiple isolated regions to a new database and schema, you must do so in the same operation in one run.

1.1 Requirements
Before you move isolated regions, ensure that you have met all of the following requirements:

• Both the source and destination database must be the same type and version.
• The workflow system installation is at the appropriate release level: IBM Business Process Manager 5.0, IBM Business Process Manager 5.1, or Content Platform Engine 5.2, or higher.
• The destination database and schema must not contain an existing workflow system or Process Engine database.
• Java Runtime Environment version 1.6 or higher must be accessible on the server where you will run the tools.
• The tools must be able to connect to the source and destination database instances by using type 4 JDBC drivers. For example, include db2jcc4.jar for the JDBC_JAR property for DB2 in the rmove.bat or rdel.bat script files.
• The workflow system or Process Engine database user permissions must be set for the source and destination database user. For more information, see the appropriate topic:
  • For Process Engine 5.1 and earlier, see Creating Process Engine database accounts .
  • For Content Platform Engine 5.2, see Creating Content Platform Engine database accounts .
• The required table spaces and temporary table spaces must exist in both source and destination databases:
  • Creating a Microsoft SQL Server database for an object store
  • Creating Oracle table spaces for a Content Platform Engine object store

  • Creating the DB2 database and table spaces for a Content Platform Engine object store

1.2 Restrictions

• You cannot move an isolated region from the database and schema of one workflow system to an existing workflow system database and schema that already contains an isolated region. If you want to move multiple isolated regions to a new database and schema, you must do so in the same operation in one run.
• The Region Move tool does not merge isolated regions into an existing workflow system or Process Engine database.
• The isolated regions in the destination database will use the same numbers as the isolated regions in the destination database.

2 Using the tools
It is required to use the latest version of Region Move tool which is attached to this technote.   The Region Move tool moves the tables for the specified isolated regions into a separate database or schema. The Delete Region tool can be used to remove the isolated regions from the source database when the move task is completed.

The destination database can have existing Content Engine or Content Platform Engine tables but cannot contain Process Engine or workflow system tables. All isolated regions from a database that are to be moved to the same destination database must be specified and moved at the same time.

2.1 Before you start

• Download the regmove.zip attached to this technote.  Validate the download using the  MD5 code, MD5 (regmove.zip) = 2ce49165834b02d9e2f24fd330172cf9. Extract regmove.zip to a directory on the server where you will run the tool. This directory includes the sample script and properties files that you must modify before you run the tools. The samples are for Windows-based systems. For non-Windows-based systems, see "Sample command scripts for non-Windows environments."
• Back up the source and destination databases before you move any isolated regions.
• Stop the source and destination workflow system or Process Engine software before you move any isolated regions. Ensure that no server software is accessing the database at the time of the move.
  
  Important: Failure to stop all access to the database results in an error. If this error occurs, you must restore the source and destination databases from the backups and restart the move region process. Sample error:
  Exception = java.lang.Exception: ERROR: the amount of data moved does not match. The source row count was <src_record_count> but the destination row count was <dst_record_count>.

2.2 Moving isolated regions
You can specify one or more isolated regions to be moved from a source database to a destination. All isolated regions to be moved to the destination database must be specified when the tool is initially run.
To move isolated regions:

1. From the directory where you copied the regmove contents, rename the rmove.bat.sample and region_move.properties.sample files to rmove.bat and region_move.properties.
2. Edit the rmove.bat script to specify variables including the location and file name of the tool (REG_MOVE_JAR), the location and file names of the type 4 JDBC driver JAR file (JDBC_JAR), and the full path including the location and file name of the region_move.properties file (PROPERTIES_FILE).
3. Edit the region_move.properties file to specify source information like the location and isolated regions to be moved, the destination information of the new isolated regions, and the logging information from the tool. For more information, see the following source, destination, and output information sections.
4. Run the tool by entering the following command at the command prompt:
   rmove

Source information:

• REGION_NUMBER
The numbers of the isolated regions to be moved. You can specify multiple isolated region numbers in a comma-separated list, for example, 1, 2, 4.
• JDBC_BATCH_SIZE
The number of rows to include in a batch for insertion into the destination database. A default of 20000 is specified.
• SOURCE_JDBC_CONNECTION_STRING
The JDBC connection string to connect to the source database. 
  • MSSQL: jdbc:sqlserver://host:port;DatabaseName=database_name
A typical port for MSSQL is 1433.
  • DB2 format: jdbc:db2://host:port/database_name
A typical port for DB2 is 50000.
  • ORACLE format: jdbc:oracle:thin:@host:port:database_name
A typical port for ORACLE is 1521.
• SOURCE_DB_USERNAME
The source database user name. If you do not enter a value here, the tool prompts you for a value at run time.
• SOURCE_DB_PASSWORD
The source database password. If you do not enter a value here, the tool prompts you for a value at run time.
• SOURCE_SCHEMA
The source database schema. If the database schema is not specified, the user's default database schema is used.

Destination information:

• DESTINATION_JDBC_CONNECTION_STRING
The JDBC connection string to connect to the destination database. 
  • MSSQL: jdbc:sqlserver://host:port;DatabaseName=database_name
A typical port for MSSQL is 1433.
  • DB2 format: jdbc:db2://host:port/database_name
A typical port for DB2 is 50000.
  • ORACLE format: jdbc:oracle:thin:@host:port:database_name
A typical port for ORACLE is 1521.
• DESTINATION_DB_USERNAME
The destination database user name. If you do not enter a value here, the tool prompts you for a value at run time.
• DESTINATION_DB_PASSWORD
The destination database password. If you do not enter a value here, the tool prompts you for a value at run time.
• DESTINATION_SCHEMA
The destination database schema. If the database schema is not specified, the user's default database schema is used.
• DESTINATION_PE_DATA 
  • For MSSQL, this value is the file group that is specified for the workflow system or Process Engine data.
  • For DB2 or ORACLE, this value is the table space that is specified for the workflow system or Process Engine data.
• DESTINATION_PE_INDEX
For DB2 or ORACLE, this value is the table space that is specified for the workflow system or Process Engine index.
• DESTINATION_PE_BLOB
FOR DB2, this value is the table space that is specified for the workflow system or Process Engine blob data. The table space must be of type LARGE table space.

Output information:

• LOG_LEVEL
  Options of logging are NONE, SOME, and FULL. The default is SOME. In most cases, SOME is sufficient to monitor the progress of the tools.
• LOG_FILENAME
  The tool automatically logs output to the screen; you can optionally specify an output log file.

Sample output:
The date and command are listed at the beginning of the output run. The summary of issues is presented at the end of the output for the isolated region.
Region Move Tool version: 1.0
Start Time:                 2013/02/05 16:33:37
Source database : PEDB
Destination database : TARGETDB

************************************
Log Level : FULL
Log Filename : E:\regmove\log2.txt
Region Number : 1
Batch Size : 20000
Source JDBC String :
    jdbc:db2://pedbhost:50000/PEDB
Source Database Type : 2
Source Database Username : peuser
Source Database name : PEDB
Source schema : PEUSER
Destination JDBC String :
    jdbc:db2://osdbhost:50000/TARGETDB
Destination Database Type : 2
Destination Database Username : p8local
Destination Database name : TARGETDB
Destination schema : NEWUSER
Destination Database Default Data Filegroup or Tablespace : PEDATA_TS
Destination Database Default Index Tablespace : PEINDEX_TS
Destination Database Default Blob Tablespace : PEBLOB_TS
************************************
Confirm that the above information is correct.

Enter 'y' to continue: y
*** Copying Process Engine system tables to destination region
    Copying table VWServer
    Copying region specfic rows from table VWIsolReg to VWIsolReg
    Copying region specfic rows from table VWNotify to VWNotify
    Copying region specfic rows from table VWPAEventState to VWPAEventState
    Copying region specfic rows from table VWPending to VWPending
    Copying region specfic rows from table VWWobTimeOut to VWWobTimeOut
    Copying table VWAttached1
    Copying table VWEvent1
    Copying table VWInject1
    Copying table VWRole1
    Copying table VWWCStats1
    Copying table VWWPStats1
*** Copying Queue, Roster and Log data to destination region
Creating Table  NEWUSER.VWQUEUE1_101
Creating Table  NEWUSER.VWQUEUE1_102
Creating Table  NEWUSER.VWQUEUE1_103
Creating Table  NEWUSER.VWQUEUE1_121
Creating Table  NEWUSER.VWQUEUE1_122
Creating Table  NEWUSER.VWQUEUE1_123
Creating Table  NEWUSER.VWQUEUE1_124
Creating Table  NEWUSER.VWQUEUE1_125
Creating Table  NEWUSER.VWQUEUE1_126
Creating Table  NEWUSER.VWQUEUE1_127
Creating Table  NEWUSER.VWROSTER1_112
Creating Table  NEWUSER.VWROSTER1_118
Creating Table  NEWUSER.VWLOG1_113
Creating Table  NEWUSER.VWLOG1_119
Creating Table  NEWUSER.VWLOG1_140
*** Creating indices in destination region
*** Copying View Definitions
Creating View: NEWUSER.VWVL1_AJ1_MyCase
Creating View: NEWUSER.VWVL1_DefaultEventLog
Creating View: NEWUSER.VWVL1_FVT51_FVTBPMTesterCase1
Creating View: NEWUSER.VWVQ1_CE_Operations
Creating View: NEWUSER.VWVQ1_Conductor
Creating View: NEWUSER.VWVQ1_Delay
Creating View: NEWUSER.VWVQ1_FVT51_FVTEventTester
Creating View: NEWUSER.VWVQ1_Inbox
Creating View: NEWUSER.VWVQ1_InstructionSheetInterpr
Creating View: NEWUSER.VWVQ1_MFFF_FF_Role_Strings
Creating View: NEWUSER.VWVQ1_MFFF_FilterRole
Creating View: NEWUSER.VWVQ1_MFFF_FloatRole
Creating View: NEWUSER.VWVQ1_MFFF_Integer_Role
Creating View: NEWUSER.VWVQ1_Tracker
Creating View: NEWUSER.VWVQ1_WSRequest
Creating View: NEWUSER.VWVR1_DefaultRoster
Creating View: NEWUSER.VWVR1_FVT_Roster
*** Creating the Sequences
Creating Sequence: NEWUSER.VWLOG1_113SEQ
Creating Sequence: NEWUSER.VWLOG1_119SEQ
Creating Sequence: NEWUSER.VWLOG1_140SEQ
Creating Sequence: NEWUSER.VWOBJECTID1SEQ
Creating Sequence: NEWUSER.VWOBJECTIDSEQ
Creating Sequence: NEWUSER.VWPENDINGSEQ
Creating Sequence: NEWUSER.VWUNIQUEID1SEQ
Creating Sequence: NEWUSER.VWUSERSEQ
*** Region Move Tool exited, Time to copy 2344 rows of data 53392 ms, batch size is 20000

2.3 Deleting the source isolated regions
The Region Delete tool deletes one or more isolated regions from a specified source database. Isolated region tables are removed along with any references to the isolated region in the remaining tables. The tool automatically logs output to the screen, or you can optionally specify a log file in the properties file.
 

To delete the source isolated regions:

1. From the directory where you copied the regmove contents to, rename the rdel.bat.sample and region_delete.properties.sample files to rdel.bat and region_delete.properties.
2. Edit the rdel.bat file to specify environment including the location and file name of the tool (REG_MOVE_JAR), the location and file names of the type 4 JDBC driver JAR file (JDBC_JAR), and the full path including location and file name of the region_delete.properties file (PROPERTIES_FILE).
3. Edit the region_delete.properties file to specify information like the location and isolated regions to be deleted and the logging information from the tool. For more information, see the following source information section.
4. Run the tool by entering the following command at the command prompt:
   rdel

Source information:

• REGION_NUMBER
  The numbers of the isolated regions to be moved. You can specify multiple isolated region numbers in a comma-separated list, for example, 1, 2, 4.
• SOURCE_JDBC_CONNECTION_STRING
  The JDBC connection string to connect to the destination database.
  • MSSQL: jdbc:sqlserver://host:port;DatabaseName=database_name
    A typical port for MSSQL is 1433.
  • DB2 format: jdbc:db2://host:port/database_name
    A typical port for DB2 is 50000.
  • ORACLE format: jdbc:oracle:thin:@host:port:database_name
    A typical port for ORACLE is 1521.
• SOURCE_DB_USERNAME
  The source database user name. If you do not enter a value here, the tool prompts you for a value at run time.
• SOURCE_DB_PASSWORD
  The source database password. If you do not enter a value here, the tool prompts you for a value at run time.
• SOURCE_SCHEMA
  The source database schema. If the database schema is not specified, the user's default database schema is used.

Sample output:
The date and command are listed at the beginning of output run. The summary of issues is presented at the end of the output for the isolated region.
Region Delete Tool version: 1.0
Start Time:                 2013/02/05 16:36:38
Source database : VWDB

************************************
WARNING: Once the region data has been deleted, it cannot be recovered.
Back up your database before continuing.


Log Level :                FULL
Log Filename :             E:\regmove\del.log
Region Number : 1
Source Database Username : peuser
Source JDBC String :
    jdbc:db2://pedbhost1:50000/VWDB
Source Database Name :     VWDB
Source Schema name :       PEUSER

************************************
Confirm that the above information is correct.

Enter 'y' to continue: y
*** Deleting region 1 from Process Engine tables

Deleted 1 rows from table PEUSER.VWIsolReg
Deleted 10 rows from table PEUSER.VWNotify
Deleted 0 rows from table PEUSER.VWPAEventState
Deleted 0 rows from table PEUSER.VWPending
Deleted 0 rows from table PEUSER.VWWobTimeOut
Dropping Table  PEUSER.VWAttached1
Dropping Table  PEUSER.VWEvent1
Dropping Table  PEUSER.VWInject1
Dropping Table  PEUSER.VWObject1
Dropping Table  PEUSER.VWRole1
Dropping Table  PEUSER.VWWCStats1
Dropping Table  PEUSER.VWWPStats1
Dropping Table  PEUSER.VWQUEUE1_101
Dropping Table  PEUSER.VWQUEUE1_102
Dropping Table  PEUSER.VWQUEUE1_103
Dropping Table  PEUSER.VWQUEUE1_121
Dropping Table  PEUSER.VWQUEUE1_122
Dropping Table  PEUSER.VWQUEUE1_123
Dropping Table  PEUSER.VWQUEUE1_124
Dropping Table  PEUSER.VWQUEUE1_125
Dropping Table  PEUSER.VWQUEUE1_126
Dropping Table  PEUSER.VWQUEUE1_127
Dropping Table  PEUSER.VWQUEUE1_146
Dropping Table  PEUSER.VWQUEUE1_147
Dropping Table  PEUSER.VWQUEUE1_148
Dropping Table  PEUSER.VWROSTER1_112
Dropping Table  PEUSER.VWROSTER1_118
Dropping Table  PEUSER.VWLOG1_113
Dropping Table  PEUSER.VWLOG1_119
Dropping Table  PEUSER.VWLOG1_140
*** Deleting region views from source database

*** Deleting region and system sequences from source database

Dropping Sequence: DROP SEQUENCE PEUSER.VWLOG1_113SEQ
Dropping Sequence: DROP SEQUENCE PEUSER.VWLOG1_119SEQ
Dropping Sequence: DROP SEQUENCE PEUSER.VWLOG1_140SEQ
Dropping Sequence: DROP SEQUENCE PEUSER.VWOBJECTID1SEQ
Dropping Sequence: DROP SEQUENCE PEUSER.VWOBJECTIDSEQ
Dropping Sequence: DROP SEQUENCE PEUSER.VWPENDINGSEQ
Dropping Sequence: DROP SEQUENCE PEUSER.VWUNIQUEID1SEQ
Dropping Sequence: DROP SEQUENCE PEUSER.VWUSERSEQ
**** Deleting system tables from source database
.
Dropping Table  PEUSER.VWUser
Dropping Table  PEUSER.VWIsolReg
Dropping Table  PEUSER.VWSysNums
Dropping Table  PEUSER.VWServer
Dropping Table  PEUSER.VWObject
Dropping Table  PEUSER.VWRDBObject
Dropping Table  PEUSER.VWNotify
Dropping Table  PEUSER.VWPAEventState
Dropping Table  PEUSER.VWPending
Dropping Table  PEUSER.VWWobTimeOut

*** Region Delete Tool exited, Time to delete 1161 rows of data 27727 ms

3 Reconnecting your environment
After the database is moved and old regions are deleted, it is important to setup the database for the Process Engine or Workflow system. Corresponding Isolated Regions and Connection points also need to be created or modified in the P8 Domain to make the system functional after the move.

3.1 P8 BPM 5.0 and 5.1 Environment

3.1.1 Process Engine Server Configuration
Use the Process Task Manager to configure a new Process Engine server or virtual server to connect to the moved database. Enter the Database information. See Configure database properties section in the info center. Run vwtaskman virtual_server_name to start Process Task Manager. After entering the database information, select next and enter any changes for the Process Engine Naming Service port or Process Engine Broker Port if defaults are not desired.

Note: Process Engine virtual servers will require a unique set of Naming service and Broker ports if running on the same machine. If configuring an additional virtual server on the same machine, see Configuring a Process Engine virtual server and database interactively .


3.1.2 Setting Up Isolated Regions and Connection Points
Using the FileNet Enterprise Manager Administration Tool, create new isolated region(s) in the P8 domain for the newly moved region(s). Enter the Naming Service Port that corresponds to the newly created virtual server in the region creation wizard. Next, update the existing Process Engine Connection Points using the new isolated regions or create new Connection Points for the new regions. See Configure a Process Engine Isolated Region .
On the Process Engine server, update the region key using Process Task Manager. See Updating Process Engine region keys with Process Task Manager .
Delete unused Connection Points or Isolated Region objects.

3.2 Content Platform Engine 5.2 Environment

3.2.1 Workflow System Configuration
If the region(s) has been moved to a database defined for an existing Content Engine Object Store, the database connection is already defined in the application server.

3.2.2 Setting Up Isolated Regions and Connection Points
Use the IBM Administration Console for Content Platform Engine tool to define a new Workflow system under the Object Store. See Creating a workflow system . During the Workflow System creation wizard specify a connection point and the region information for one of the regions moved. Create additional connection points and isolated regions for all the remaining regions that were moved. See Creating additional connection points and isolated regions . To clean up delete any unused Connection Points.
After creating the new connection points, if the user wants to keep the same connection point name, they can rename it to the old name once the old connection point has been deleted. Note that a server restart may be necessary to clear out any cached information related to the old connection point.

4 Sample command scripts for non-Windows environments
The command scripts rmove.bat.sample and rdel.bat.sample are on the disk in the regmove folder. The sample scripts are in the Windows DOS bat format. The following sample shell scripts can be used to run the tools on Linux, HP, Solaris, or AIX.

The script requires the user to specify the following variables:

• REG_MOVE_JAR
  The file name and location of the Region Move tool.
• JDBC_JAR
  The file name and location of the type 4 JDBC driver JAR file.
• PROPERTIES_FILE
  The full path, including the location and file name, of the region_move.properties file.

4.1 Sample Region Move script 
This shell script sample sets up the environment and runs the Region Move tool. It moves one or more isolated regions from a source database to a destination database as specified in the region_move.properties file that is specified in the rmove script.

#! /bin/sh
#rmove.sh
# @ECHO OFF
# sample batch file to run the Region Move tool
# Directory location of the regmove.jar
REG_MOVE_JAR=/home/fnsw/regmove/regmove.jar

# Directory location of the JDBC jar file
JDBC_JAR=/opt/jars/ojdbc6.jar

# Full path of the Region Move property file (region_move.properties)
PROPERTIES_FILE=/home/fnsw/regmove/region_move.properties

java -cp $REG_MOVE_JAR:$JDBC_JAR -Xms512m -Xmx1024m  -DPROPFILE=$PROPERTIES_FILE  filenet.vw.regmove.RegionMoveMain

4.2 Sample Region Delete script 
This shell script sample sets up the environment and runs the Delete Region tool. It deletes one or more isolated regions from a source database as specified in the region_delete.properties file that is specified in the rdel script.

#! /bin/sh
#rdel.sh
# @ECHO OFF
# sample batch file to run the region delete tool

# Directory location of the regmove.jar
REG_MOVE_JAR=/home/fnsw/regmove/regmove.jar

# Directory location of the JDBC jar file
JDBC_JAR=/opt/jars/ojdbc6.jar

# Full path of the region delete property file (region_delete.properties)
PROPERTIES_FILE=/home/fnsw/regmove/region_delete.properties


java -cp $REG_MOVE_JAR:$JDBC_JAR  -Xms512m -Xmx1024m -DPROPFILE=$PROPERTIES_FILE  filenet.vw.regmove.RegionDeleteMain

5 A Note about SQL Server JDBC driver versions

Testing has shown that many of the available Microsoft JDBC Drivers, used by the region move tool to access Microsoft SQL Server databases, do not work with the region move tool. The following versions are known to not work correctly, 6.2, 6.4, 7.4 and 9.4.  The affected drivers will throw Exceptions and terminate the region move program.  Exceptions that have been observed are either “java.io.IOException: The TDS protocol stream is not valid.” or "java.io.IOException: The stream is closed." when copying rows from the source to the destination databases.

The Microsoft JDBC Driver version 6.0 works correctly. (sqljdbc42.jar, extracted from sqljdbc_6.0.8112.200_enu.tar.gz, downloaded from https://go.microsoft.com/fwlink/?linkid=2122714 )