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.
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 the number of nodes in your environment compare to 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 that is 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 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');
- 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.
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;
27 August 2019