Event collector (EC) non-instance cleanup purge scripts (available at CONFIG_HOME\bpm\bpm_app_name\database\ec\ec_delete_non_instance_audit_data_ora.sql) does not delete any non-instance data from the database, and outputs similar statements as follows:

17-MAY-19 18:46:51.447449000 -- There are audit events for process instances (Started: 159, Complete/Cancelled/Failed: 467) before 09-MAR-18
17-MAY-19 18:46:51.447537000 -- Possibly ec_delete_audit_stats_data hasn't been executed yet?
17-MAY-19 18:46:51.447552000 -- Audit data can be force deleted, please check the documentation to enable this option!
17-MAY-19 18:46:51.447563000 -- WARNING: AUDIT DATA FOR PROCESS INSTANCES MAY BE INCOMPLETE IF THIS OPTION IS USED!!!

Prerequisites for Event Collector (EC) non-instance data cleanup using EC purge scripts

ALL

Even though there are process instances (PI) in terminated states (Complete/Cancelled/Failed), the script will not delete any non-instance data until all the process instances are terminated (Complete/Cancelled/Failed) until the date mentioned, while running the cleanup scripts. In the example above, there are still 159 instances in Started state.

Following queries maybe used to check if there are process instances in states other than terminated states:

SELECT COUNT(message_id) FROM ec_event WHERE message_id = 'BX_INSTANCE_PROCESS_STARTED' AND event_timestamp < to_timestamp(end_time, 'DD-MON-RR HH24:MI:SS:FF');
    
SELECT COUNT(message_id) FROM ec_event WHERE message_id in ('BX_INSTANCE_PROCESS_COMPLETED', 'BX_INSTANCE_PROCESS_CANCELLED', 'BX_INSTANCE_PROCESS_FAILED') AND event_timestamp < to_timestamp(end_time, 'DD-MON-RR HH24:MI:SS:FF');


If any of the above queries return a count > 0, then follow the steps before you run EC non-instance cleanup scripts:

1. Ensure all the PI related audit data is removed using the audit stats cleanup script (for example for Oracle: ec_delete_audit_stats_data_ora.sql). Note this will require all PIs to be in a terminal state.

2. Run the two DB queries mentioned above, and if the output for both statements is zero (0), you can now run EC non-instance cleanup scripts.

3. Once the non-instance cleanup scripts have completed successfully, and deleted the non-instance data, you will see output similar to as follows:

31-MAY-19 22:19:29.062585000 -- Query: [SELECT event_pk, case_reference FROM ec_event WHERE event_timestamp < to_timestamp('04-MAR-18', 'DD-MON-RR HH24:MI:SS:FF')]
31-MAY-19 22:19:36.199793000 -- 5000 events deleted! Commiting transaction! Total events deleted 5000
31-MAY-19 22:19:39.260238000 -- 5000 events deleted! Commiting transaction! Total events deleted 10000
31-MAY-19 22:19:42.080488000 -- 5000 events deleted! Commiting transaction! Total events deleted 15000
31-MAY-19 22:36:19.179585000 -- 12270326 orphan array rows deleted!
31-MAY-19 23:05:44.202869000 -- 12270326 orphan array lookup rows deleted!