Clearing Audit and Statistical Data Entries Associated with Case Data

The ec_delete_case_audit stored procedure can be used to delete audit and statistical data entries associated with case data from the BPM database (the entries in the ec_event_int and ec_case_status tables).

The ec_delete_case_audit stored procedure is supplied in the CONFIG_HOME\bpm\bpm_app_name\database\ec\ec_delete_case_audit_db.sql. script, where db is the type of database you are using (mssql, ora or db2).

Note: In earlier ActiveMatrix BPM versions, case data audit entries were removed using the ec_delete_non_instance_audit_data stored procedure, which meant that they were removed only if all the process instance entries for the same period had already been deleted. The ec_delete_case_audit stored procedure allows you to delete audit and statistical data entries for case data independently, whether or not the process instance entries for the same period have already been deleted.

ec_delete_case_audit Stored Procedure

ec_delete_case_audit takes the following parameters: 

ec_delete_case_audit (end_time, max_cases, batch_size, dbg)

where:

  • end_time - is the date, in 'dd-mmm-yy' format, up to which audit and statistical data entries should be deleted. (Default: null = delete ALL entries). For example, a value of '01-AUG-18' deletes entries that are older than 1st August 2018.
  • max_cases - is the maximum number of cases for which audit and statistical data entries should be deleted. (Default: -1 = no limit)
  • batch_size - is the number of cases for which audit and statistical data entries should be deleted before performing a commit. (Default: 100)
  • dbg - switches debug mode on or off. (Default: False = Off)
    Note: You should only set the dbg flag when advised to do so by TIBCO Support.

Prerequisites

The ec_delete_case_audit stored procedure does not include any provision for backing up or archiving your database. You must back up your database, according to whatever backup strategy your organization has implemented, before executing this stored procedure. Refer to the documentation supplied with your database server for information on how to do this.

Procedure

  1. From an appropriate database query/management tool (for example, SQL Server Management Studio, Oracle SQL*Plus or a DB2 command window), connect to the BPM database using the BPM database user credentials (default: bpmuser).
  2. Run the ec_delete_case_audit_db.sql script to install the stored procedure (where db is either mssql, oracle or db2.
  3. Execute the ec_delete_case_audit stored procedure, using appropriate parameter values, to delete the audit and statistical data entries that you want to clear.