The IBM provided stored procedure to cleanup the BPM_TASK_INDEX and BPM_INSTANCE_TABLE does not work
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.
BPM_INDEX_TABLE_CLEANUPstored procedure as detailed in the following documentation:
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');
- 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');
- 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?
- Identify the INDEX_ID value of each node's Lucene search index by completing the following:
- Navigate to the node profile /searchIndex directory
- Open the bpm.uuid file in a text editor
- The value listed in the bpm.uuid file is the current INDEX_ID value used by the node's search index
- Repeat the preceding three steps for each node in your environment to compile a list of INDEX_ID values that are currently in use
- 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.
- 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');
- 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.
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 informationHow to cleanup the BPM_TASK_INDEX and BPM_INSTANCE_INDEX tables
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