Running the Stored Procedure for Oracle

Prerequisites

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

CREATE INDEX ix_ec_cleanup_1 on ec_array(id);
CREATE INDEX ix_ec_cleanup_2 on ec_managed_obj_lifecycle(MANAGED_OBJ_ID);
CREATE INDEX ix_ec_cleanup_3 on ec_managed_obj_lifecycle(ROOT_ID);
CREATE INDEX ix_ec_cleanup_4 on ec_event_int(MANAGED_OBJ_ID, MESSAGE_ID);
CREATE INDEX ix_ec_cleanup_5 on ec_event_int(PARENT_OBJ_ID, MESSAGE_ID);
CREATE INDEX ix_ec_cleanup_6 on ec_event_int(CORRELATION_ID);

Procedure

  • To run the stored procedure, enter the following command: 
    SET SERVEROUTPUT ON
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 and defaults to all templates if not specified.
    • end_time is the date before which the process instances should have been completed, cancelled or failed. The date should be in the format DD-MON-YY HH24:mm:ss, where:
      • DD is the day of month (for example, 24).
      • MON is the abbreviated month name (for example, JUL).
      • YY is the last 2 dits of the year (for example, 98)
      • HH24 is the hour of day (0-23).
      • mm is the minutes (0-59).
      • ss is the seconds (0-59).

      Following is an example of a valid date: 15-SEP-11 11:00:00. This is an optional parameter, if not specified all completed, cancelled and failed instances will be deleted.

    • batch_size is the number of process instances that should be deleted before the transaction is committed. This is an optional parameter and if not specified will default to 100.
    • 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. See How Detached Sub-Process Instances are Handled for more information.
      • 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 flag is switched off by default. Set the value to:
      • TRUE to switch on debug.
      • FALSE to switch off debug (default).