Adding support for shared work items

IBM BPM, V8.0.1, All platforms > Administer the IT infrastructure > Administer Business Process Choreographer
Adding support for shared work items

If you migrated your Business Process Choreographer configuration from Version 7.0.0.2 or earlier, you can improve the database performance by activating shared work items. Any new Business Process Choreographer configurations automatically support shared work items.

Depending on how many work items you currently have in your Business Process Choreographer database, and how many hours per day you can run the work item migration script, this task could take several days to complete.

Procedure
Upgrade the database schema for shared work items by performing the following:
You must customize the database design file before you can use it to generate the script required to upgrade your schema.
Locate the upgradeSchema.properties database design file.

profile_root/dbscripts/ProcessChoreographer/ database_type/ database_name/ database_schema/upgradeSchema.properties
profile_root\dbscripts\ProcessChoreographer\ database_type\ database_name\ database_schema\upgradeSchema.properties
profile_root/dbscripts/ProcessChoreographer/ database_type/ database_name/ database_schema/upgradeSchema.properties. Where

profile_root

If Business Process Choreographer is configured on a server, this is the profile of the corresponding node.
If Business Process Choreographer is configured on a cluster, this is the profile where you run BPMMigrateCluster (previously known as WBIProfileUpgrade.ant), which is normally the dmgr profile.

database_name

is the name of the Business Process Choreographer database.

database_schema

is the name of the database schema. It is optional, and is not set if an implicit schema is used.

database_type

is the name of the directory that corresponds to the database type that you are using.

Database type Directory name
DB2 Universal Database™ (for all operating systems except z/OS ) DB2
Oracle Oracle
Microsoft SQL Server SQLServer

Make a copy of the appropriate database design file.
Use the database design tool to edit the database configuration that is defined in the copy of the properties file.
Enter the command to start the database design tool.
 INSTALL_ROOT/util/dbUtils/DbDesignGenerator.sh 
           -e  copy_of_upgradeSchema.properties_file 
 INSTALL_ROOT/util/dbUtils/DbDesignGenerator.sh 
       -e  copy_of_upgradeSchema.properties_file 
 INSTALL_ROOT\util\dbUtils\DbDesignGenerator.bat 
       -e  copy_of_upgradeSchema.properties_file 
Answer all the questions, or press enter to accept the default values. In particular, make sure that you select the WorkItemMigration scenario.
You have the choice whether to overwrite the input file or to save your changes in a new file.
Enter the command to run the database design tool on your modified database design file to generate the upgrade scripts.
 INSTALL_ROOT/util/dbUtils/DbDesignGenerator.sh 
      -g  copy_of_upgradeSchema.properties_file
     [-d  output_directory]
 INSTALL_ROOT/util/dbUtils/DbDesignGenerator.sh 
     -g  copy_of_upgradeSchema.properties_file
    [-d  output_directory]
 INSTALL_ROOT/util/dbUtils/DbDesignGenerator.sh 
     -g  copy_of_upgradeSchema.properties_file  
    [-d  output_directory]
If you do not provide the -d option to specify an output directory, the generated file upgradeSchemaSharedWIs.sqlupgradeSchemaSharedWIs.ddl and dropClassicWIIndexes.sqldropClassicWIIndexes.ddl will be written to a subdirectory of the current directory.
If you will run the generated scripts on a different system, copy them to the system on which the database is hosted.
Stop the server or cluster where Business Process Choreographer is configured.
Run the upgradeSchemaSharedWIs.sql script. For information about how to run the script on your database, refer to the product documentation for your database. If there are any errors, or failure is indicated in your database client output, fix the reported errors and retry this step.
Restart the server or cluster.
Optional: Reduce the number of work items in your system by performing one or more of the following:

Delete BPEL process instances
Delete completed task instances
If you configured a Business Process Archive Manager to archive instances from this Business Process Choreographer configuration, perform Archiving completed BPEL process and task instances.
At a time when there is a low load on the database, run the migrateWI.py script to create shared work items for existing work items.
Decide on the maximum duration in minutes, duration, that you want the script to run for.
Change to the directory where the migrateWI.py script is located: INSTALL_ROOT/ProcessChoreographer/admin

INSTALL_ROOT/ProcessChoreographer/admin
INSTALL_ROOT\ProcessChoreographer\admin
Run the script by invoking wsadmin with the following parameters:
  -conntype NONE
 [-profileName  profile] 
 [-tracefile  trace_file] 
  -f migrateWI.py 
  -migrateToWISharing
 [-duration  duration]
 [-slice  slice_size]
 (-server  serverName) | (-cluster  clusterName)
[[-dbUser  userID] -dbPassword  password]
 [-dbSchema  schema]
If you specify a server, run the script on the server node. If you specify a cluster, run the script on the node of a cluster member, and not on the dmgr. Because wsadmin overwrites its trace file, use the -tracefile option to specify a file name and location for the trace file for the work item data migration. There is no need to stop the server or cluster. For more information about using this script and its parameters, see Business Process Choreographer data migration script for shared work items.
When the script finishes, it reports a summary of the migration status of the work items belonging to each type of entity:

The total number in the system.
The number that has been migrated.
The number that still needs to be migrated.
The percentage that have been completed.

For example:
                   Entities  Migrated  Not migrated  Completed
EventInstance          7602      7602             0       100%
WorkBasket                0         0             0       100%
TaskInstance          29883     29883             0       100%
EscalationInstance     4227      4227             0       100%
BusinessCategory          0         0             0       100%
ActivityInstance      52572     52572             0       100%
ProcessInstance        7602      3036          4566        39%
--------------------------------------------------------------
Total                101886     97320          4566        95%
If the script finished with less than 99% completed (that is, more than 1% of the total number of entities and their corresponding work items in the system still have to be processed), repeat step 3.
If the script finished with 99% or more completed, activate the shared work item support.
Stop the server or cluster where Business Process Choreographer is configured.
Now run the script a final time to process any remaining work items and switch into shared work item mode, by invoking wsadmin with the following parameters:
  -conntype NONE
 [-profileName profile] 
 [-tracefile trace_file] 
  -f migrateWI.py 
  -setMode WS_SHARING_ACTIVE_MODE
  (-server serverName) | (-cluster clusterName)
[[-dbUser userID] -dbPassword password]
 [-dbSchema schema]
Success is indicated by the following message:
migrateWI.py finished with warnings.
In case of problems, you could get a message like the following:
Exception received:
java.lang.IllegalStateException: Mode: WS_SHARING_ACTIVE_MODE
migrateWI.py finished, result = WARNING. 
Restart the server or cluster.
Results
The Business Process Choreographer configuration is using shared work items and has better performance for many database queries.

What to do next
Optionally, you can perform any of the following:

Use the administrative console to change the default cleanup times and retention periods for unused shared work items. You can change these settings on the Runtime or Configuration tab for the Human Task Manager.
Delete unused staff query interfaces and unused work item patterns, by running the cleanupUnusedStaffQueryInstances.py script.
If you do not use any queries that have to run against the non-shared work items, you can free up space in the database by running the dropClassicWIIndexes.sqldropClassicWIIndexes.ddl to drop the indexes on the WORK_ITEM_T table. This script was generated in step 1.b.

Business Process Choreographer data migration script for shared work items

If you migrated from version 7.0.0.2 or earlier, you can optionally activate support for shared work items, which should improve performance. You must use the migrateWI.py script to migrate data and activate the support.

Administer Business Process Choreographer

Related concepts:
Shared work items

Related reference:
Business Process Choreographer data migration script for shared work items
cleanupUnusedStaffQueryInstances.py administrative script
Connection timeout when running a wsadmin script

Database type	Directory name
DB2 Universal Database™ (for all operating systems except z/OS )	DB2
Oracle	Oracle
Microsoft SQL Server	SQLServer