Running the Stored Procedure for DB2

Note: The EC_DELETE_NON_INSTANCE_AUDIT stored procedure should only be executed once audit data for process instances and work items has been purged using the EC_DELETE_AUDIT_STATS_DATA stored procedure. If this is not done it may leave the audit trail for process instances or work items in an inconsistent state.

To run the stored procedure, enter the following commands: 

SET SERVEROUTPUT ON
CALL EC_DELETE_NON_INSTANCE_AUDIT(end_time, force_delete, batch_size, dbg)

where:

  • end_time is the date before which the audit data will be purged.

    This is an optional parameter, if not specified all audit data will be deleted.

    The date should be in the format YYYY-MM-DD HH24:mm:ss.SSS where:

    • YYYY is the 4 digit year (e.g., 1998)
    • MM is the month of the year (1-12)
    • DD is the day of month (e.g., 24)
    • HH24 is the hour of day (0-23)
    • mm is the minutes (0-59)
    • ss is the seconds (0-59)
    • SSS is the milliseconds (0-999)

      Following is an example of a valid date: 2011-11-01 10:42:01.008

  • force_delete indicates if the audit data should be force deleted.

    In normal operation audit events for process instances should have been purged if the EC_DELETE_AUDIT_STATS_DATA stored procedure has been executed.

    The EC_DELETE_NON_INSTANCE_AUDIT stored procedure will error if it identifies any process instance audit events within the threshold to be purged. If this happens it will not continue with the purge.

    It is recommended you check the audit trail to see if the audit events are required. If you wish to continue with the purge you could pass in 'TRUE' for this option.

    Note: This may leave the audit trail for process instances or work items in an inconsistent state.

    Valid values are:

    • ’TRUE' to switch on force_delete
    • ’FALSE' to switch off force_delete (default value)
  • batch_size is the number of audit events that should be deleted before the transaction is commited.

    This is an optional parameter and if not specified will default to 5000.

  • dbg is the debug flag which gives useful debug information. This is switched off by default. Valid values are
    • 'TRUE' to switch on debug
    • ’FALSE' to switch off debug (default value)
      Note: Use the DB2 command line processor (CLP) command SET SERVEROUTPUT ON to redirect the debug output to standard output.

The following are examples of usage.

  • To delete audit data before the specified date:

    CALL EC_DELETE_NON_INSTANCE_AUDIT('2011-11-01 10:42:01.008’);

  • To delete all audit entries:

    CALL EC_DELETE_NON_INSTANCE_AUDIT();

  • To delete all audit entries with a different commit batch size (150):

    CALL EC_DELETE_NON_INSTANCE_AUDIT(NULL, NULL, 150);

  • To delete all audit entries forcefully:

    CALL EC_DELETE_NON_INSTANCE_AUDIT(NULL, 'TRUE');