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.

About this task

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:
1. You must customize the database design file before you can use it to generate the script required to upgrade your schema.
  1. 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 deployment manager 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
  2. Make a copy of the appropriate database design file.
  3. Use the database design tool to edit the database configuration that is defined in the copy of the properties file.
    1. 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
    2. Answer all the questions, or press enter to accept the default values. In particular, make sure that you select the WorkItemMigration scenario.
    3. You have the choice whether to overwrite the input file or to save your changes in a new file.
2. 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.
3. If you will run the generated scripts on a different system, copy them to the system on which the database is hosted.
4. Stop the server or cluster where Business Process Choreographer is configured.
5. 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.
6. Restart the server or cluster.
Optional: Reduce the number of work items in your system by performing one or more of the following:
- Deleting BPEL process instances
- Deleting 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.
1. Decide on the maximum duration in minutes, duration, that you want the script to run for.
2. Change to the directory where the migrateWI.py script is located: install_root/ProcessChoreographer/admin
  - install_root/ProcessChoreographer/admin
  - install_root\ProcessChoreographer\admin
3. 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]
```
  Note: If you specify a server, you must run the script on the server node. If you specify a cluster, you must run the script on the node of a cluster member, and not on the deployment manager. 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.
4. 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.
1. Stop the server or cluster where Business Process Choreographer is configured.
2. 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]
```
3. 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. 
```
4. Restart the server or cluster.

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

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.