Running the Stored Procedure for DB2

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);
CREATE INDEX ix_ec_cleanup_7 on ec_event_trigger(EVENT_CORRELATION);

Procedure

  • To run the stored procedure, enter the following command: 
    CALL EC_DELETE_AUDIT_STATS_DATA(PROC_TPL, END_TIME, BATCH_SIZE, CURSOR_REFRESH, DELETE_ACTIVE_SUB_PROCS, 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-DD HH24:mm:ss.SSS format, where:
      • YYYY is the 4 dit year. For example, 2011.
      • MM is the month of the year, from 1 - 12.
      • DD is the day of the month. For example, 22.
      • HH24 is the hour of the day, from 0 - 23.
      • mm is the minutes, from 0 - 59.
      • ss is the seconds, from 0 - 59.
      • SSS is the 3-dit milliseconds, from 0 - 999

      For example, a valid date is: 2011-11-01 10:42:01.008.

      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 which defaults to 100 if not specified.
    • DELETE_ACTIVE_SUB_PROCS 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_SUB_PROCS 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.