IBM Support

The methods provided by IBM to clean up the BPM_TASK_INDEX and BPM_INSTANCE_TABLE do not work

How To


Summary

If you are running the BPM_INDEX_TABLE_CLEANUP stored procedure or the indexTablesCleanup command, you might find that it reports that it ran successfully but you do not observe a reduction in the number of rows in the BPM_TASK_INDEX and BPM_INSTANCE_INDEX tables.

Objective

When you need to purge the index tracking tables BPM_TASK_INDEX and BPM_INSTANCE_INDEX in BPM 8.6.0 CF2017.12 or earlier, you have to run the IBM provided BPM_INDEX_TABLE_CLEANUP stored procedure as detailed in the following documentation:
In Business Process Manager CF2018.03 or Business Automation Workflow you use the indexTablesCleanup command documented in the Knowledge Center:
You might find that running the stored procedure or the cleanup command completed successfully but the number of records in the tables were not reduced.
Run the following query to determine the number of nodes to provide the first parameter of the stored procedure. You might find that it returns a number higher than the number of actual nodes in your environment.

SELECT COUNT(DISTINCT INDEX_ID) FROM BPM_TASK_INDEX_JOB WHERE INDEX_LAST_PURGE_TIME IS NOT NULL AND INDEX_ID NOT IN ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000002');
This query is returning the number of unique INDEX_ID values found in the BPM_TASK_INDEX_JOB table. By default each node in your environment maintains its own Lucene search index. Each node has a unique INDEX_ID value.
If you find that the query returns an unexpected number, then most likely your team manually rebuilt the Lucene search index directory on some or all nodes. The Lucene search index rebuilds with a new INDEX_ID value. The old INDEX_ID values remain in the BPM_TASK_INDEX_JOB table and so will the new INDEX_ID value assigned to the rebuilt search index. Because there are more unique INDEX_ID values than actual nodes, the stored procedure and the indexTablesCleanup command to not clean up as expected.

Environment

Applicable to DB2, Oracle, and MS SQL Server databases.

Steps

  1. Run the SELECT statement to identify the unique INDEX_ID values found in the BPM_TASK_INDEX_JOB table:

    SELECT DISTINCT INDEX_ID FROM BPM_TASK_INDEX_JOB WHERE INDEX_LAST_PURGE_TIME IS NOT NULL AND INDEX_ID NOT IN ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000002');
     
  2. Identify how many nodes you have in your BPM environment. How does the number of nodes in your environment compare to the number of unique INDEX_ID values you found with the previous select statement?
  3. Identify the INDEX_ID value of each node's Lucene search index by completing the following:
    1. Navigate to the node profile /searchIndex directory
    2. Open the bpm.uuid file in a text editor
    3. The value listed in the bpm.uuid file is the current INDEX_ID value that is used by the node's search index
    4. Repeat the preceding three steps for each node in your environment to compile a list of INDEX_ID values that are currently in use
  4. Compare the INDEX_ID values you compiled with the results of the query in Step 1. Identify which INDEX_ID values are not currently in use.
  5. Delete the records in the BPM_TASK_INDEX_JOB table where the INDEX_ID value is one of the obsolete INDEX_ID values that are no longer used by any of the node's search indices. You can use a query like the following: 

    DELETE FROM BPM_TASK_INDEX_JOB WHERE INDEX_ID IN ('old_index_id_1', 'old_index_id_2');
     
  6. Rerun the stored procedure or the indexTablesCleanup command, providing the first parameter as the number of nodes you have in your environment. Now the stored procedure or indexTablesCleanup command runs successfully and cleans up the BPM_TASK_INDEX and BPM_INSTANCE_INDEX tables.

Additional Information

You can verify whether the stored procedure is running successfully or not by running the following queries before and after you execute the stored procedure:
SELECT COUNT(1) FROM BPM_TASK_INDEX WHERE DELETED_DATETIME IS NOT NULL;
SELECT COUNT(1) FROM BPM_INSTANCE_INDEX WHERE DELETED_DATETIME IS NOT NULL;

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSFPJS","label":"IBM Business Process Manager"},"Component":"Process Portal;Process Portal Search Index","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"BPM 8.6 CF2017.12 and earlier;BPM 8.5.7;BPM 8.5.6;BPM 8.5.5;BPM 8.5.0.x","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
14 September 2022

UID

ibm10888251

Page Feedback