IBM Support

The IBM provided stored procedure to cleanup the BPM_TASK_INDEX and BPM_INSTANCE_TABLE does not work

How To


Summary

If you are running the BPM_INDEX_TABLE_CLEANUP stored procedure to cleanup the Process Portal Search Index tracking tables BPM_TASK_INDEX and BPM_INSTANCE_INDEX 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 as part of your regular cleanup maintenance activities or as directed by IBM Support to resolve an issue and your version is 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:


You might find that when it is executed it reports to have run successfully but did not remove any rows from the tables.

When you 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 that are found in the BPM_TASK_INDEX_JOB table. By default each node in your environment maintains its own Lucene search index and is tracked by a unique INDEX_ID value.

If you find that the above query returns an unexpected number (remember that the query result should match the number of nodes in your environment) then most likely your team has manually rebuilt the Lucene search index directory on some or all nodes. When this process is executed the Lucene search index is rebuilt with a new INDEX_ID value. The old INDEX_ID values will still be found in the BPM_TASK_INDEX_JOB table as well as the new INDEX_ID value assigned to the rebuilt search index. This causes the stored procedure to not cleanup as expected.


Environment

The follow solution is 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 this compare you 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 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 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, providing the first parameter as the number of nodes you have in your environment. Now the stored procedure should run successfully and you should find that it cleans up the BPM_TASK_INDEX and BPM_INSTANCE_INDEX tables.

Additional Information

You can verify if 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;

Related information

How to cleanup the BPM_TASK_INDEX and BPM_INSTANCE_INDEX tables

Document information

More support for: IBM Business Process Manager

Component: Process Portal, Process Portal Search Index

Software version: BPM 8.5.0.x, BPM 8.5.5, BPM 8.5.6, BPM 8.5.7, BPM 8.6 CF2017.12 and earlier

Operating system(s): Linux, Windows

Reference #: 0888251

Modified date: 15 July 2019