Running the Stored Procedure for Microsoft SQL Server

Prerequisites

TIBCO recommends that, before running the stored procedure, you create the following indexes to improve performance: 

CREATE NONCLUSTERED INDEX ix_ec_cleanup_1 on amxbpm.ec_array(id);
CREATE NONCLUSTERED INDEX ix_ec_cleanup_2 on amxbpm.ec_managed_obj_lifecycle(managed_obj_id);
CREATE NONCLUSTERED INDEX ix_ec_cleanup_3 on amxbpm.ec_managed_obj_lifecycle(root_id);
CREATE NONCLUSTERED INDEX ix_ec_cleanup_4 on amxbpm.ec_event_int(managed_obj_id, message_id);
CREATE NONCLUSTERED INDEX ix_ec_cleanup_5 on amxbpm.ec_event_int(parent_obj_id, message_id);
CREATE NONCLUSTERED INDEX ix_ec_cleanup_6 on amxbpm.ec_event_int(correlation_id);
CREATE NONCLUSTERED INDEX ix_ec_cleanup_7 on amxbpm.ec_event_trigger(event_correlation);

Procedure

  • To run the stored procedure, enter the following command: 
    EXEC ec_delete_audit_stats_data proc_tpl, end_time, batch_size, cursor_refresh, delete_active_subprocs, dbg

    where:

    • proc_tpl is a comma-separated list of process template IDs. The process template IDs can be found in the ec_proc_template table corresponding to a unique process template name, module name, and module version. This is an optional parameter which defaults to all templates if not specified.
    • end_time is the date before which the process instances should have been completed, cancelled, or failed. Specify the date in the YYYY-MM-DDTHH:mm:ss.SSSTZ format, where:
      • YYYY-MM-DD is the date.
      • HH:mm:ss is the time, specified in the 24-hour format.
      • SSS is the 3-digit milliseconds, and TZ is the timezone. For example, UTC is represented by ’Z’. An example of a valid date is 2010-10-08T13:35:37.977Z
      This is an optional parameter, if not specified all completed, cancelled and failed instances are deleted.
    • batch_size is the number of process instances that should be deleted before the transaction is committed. This is an optional parameter which defaults to 100 if not specified.
    • cursor_refresh is the number of process instances that should be deleted before the cursor is refreshed. This is now deprecated and is only maintained for backward compatibility.
    • delete_active_subprocs is an (optional) boolean flag that specifies whether, before purging audit data for a process instance, to check if the process instance has any detached child sub-process instances that are still active. If delete_active_subprocs is:
      • FALSE: Audit data for the root process instance and its chain of sub-process instances will only be purged if it does not have any active sub-process instances. This is the default value.
      • TRUE: Audit data for the root process instance and its chain of sub-process instances will be purged even if any of those sub-process instances are active.
    • dbg is the debug flag which gives useful debug information. This is switched off by default. Set the value to:
      • TRUE to switch on debug.
      • FALSE to switch off debug (default).