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)

    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

      Following is an example of a valid date: 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.

Example

To delete the audit or statistics entries of process instances for template IDs 2 and 3 (=ec_proc_template.id), and have completed, cancelled or failed before the specified date: 

CALL EC_DELETE_AUDIT_STATS_DATA('2,3', '2011-11-01 10:42:01.008');