The ec_delete_audit_stats_data_<db>.sql script is used to purge audit entries and statistical data for
process instances and any child sub-process instances, where all of those instances have either
completed, cancelled or failed. (See "Clearing Audit and Statistical Data for Process Instances" in
TIBCO ActiveMatrix BPM Administration for more information.)

Note: <db> is either 'db2', 'mssql' or 'ora', depending on the database that you are using.

However, if a sub-process is invoked in Asynchronous Detached mode, the invoking call activity
completes immediately and the process flow continues. The sub-process lifecycle is independent of the
invoking process, so the sub-process instance may still be active when the parent process instance has
terminated. In this case, if the ec_delete_audit_stats_data_<db>.sql script is used to purge audit
data for the parent process instance, the audit data for the still active sub-process instance is
incorrectly purged as well.

To prevent this problem from occurring, you can modify the ec_delete_audit_stats_data_<db>.sql script
so that it checks if a completed, cancelled or failed process instance has any child, detached
sub-process instances that are still active, before it purges audit data for the whole chain of
instances. The chain will only be purged if there are no active, detached sub-process instances.

All supported operating systems and databases

In the ec_delete_audit_stats_data_<db>.sql script, modify the query in the stored procedure that
selects the root instances to purge, as follows:

   * SQL Server:
     -----------------
     
     Add the following line to the amxbpm.create_purge_sessions stored procedure.
     
         SET @qry = @qry + ' AND pe.managed_obj_id NOT IN (SELECT root_id FROM
         amxbpm.ec_managed_obj_lifecycle pe2 WHERE pe2.root_id = pe.managed_obj_id AND
         pe2.status >= 2)';
         
     You must add the line after the optional end_time is appended to the query, and before the query
     is printed out as debug - like this:
     
         IF @end_time IS NOT NULL
         BEGIN
           SET @qry = @qry + ' AND pe.creation_time < @end_time';
         END
    
        SET @qry = @qry + ' AND pe.managed_obj_id NOT IN (SELECT root_id FROM
        amxbpm.ec_managed_obj_lifecycle pe2 WHERE pe2.root_id = pe.managed_obj_id AND
        pe2.status >= 2)';

        IF @dbg = 'TRUE'
        BEGIN
          PRINT (CONVERT( VARCHAR(24), SYSDATETIME(), 121)) + ' -- Query [' + @qry + ']!';
        END

    * Oracle:
      ----------

     Add the following line to the create_purge_sessions stored procedure.
     
         qry := qry || ' AND pe.managed_obj_id NOT IN (SELECT root_id FROM
         ec_managed_obj_lifecycle pe2 WHERE pe2.root_id = pe.managed_obj_id AND pe2.status >= 2)';
         
     You must add the line after the optional end_time is appended to the query, and before the query
     is printed out as debug - like this:

         IF end_time IS NOT NULL THEN
          qry := qry || ' AND pe.creation_time < to_timestamp(:s, ''DD-MON-RR HH24:MI:SS:FF'')';
         END IF;

        qry := qry || ' AND pe.managed_obj_id NOT IN (SELECT root_id FROM ec_managed_obj_lifecycle pe2
        WHERE pe2.root_id = pe.managed_obj_id AND pe2.status >= 2)';

        IF dbg = 'TRUE' THEN
          dbms_output.put_line(TO_CHAR (SYSTIMESTAMP, 'DD-MON-YY HH24:MI:SSxFF') || ' -- Query [' ||
          qry || ']');
        END IF;

   * DB2:
     -------

     Add the following line to the EC_DELETE_AUDIT_STATS_DATA stored procedure.
     
         SET QRY = QRY || ' AND pe.managed_obj_id NOT IN (SELECT root_id FROM
         ec_managed_obj_lifecycle pe2 WHERE pe2.root_id = pe.managed_obj_id AND pe2.status >= 2)';
         
     You must add the line after the optional END_TIME is appended to the query, and before the
     optional CURSOR_BATCH_SIZE is appended to the query - like this:
     
        IF END_TIME IS NOT NULL THEN
          SET QRY = QRY || ' AND pe.creation_time < TIMESTAMP(''' || END_TIME || ''')';
        END IF;

        SET QRY = QRY || ' AND pe.managed_obj_id NOT IN (SELECT root_id FROM
        ec_managed_obj_lifecycle pe2 WHERE pe2.root_id = pe.managed_obj_id AND pe2.status >= 2)';
    
        IF CURSOR_BATCH_SIZE > 0 THEN
          SET QRY = QRY || ' FETCH FIRST ' || CURSOR_BATCH_SIZE || ' ROWS ONLY';
        END IF;
       
NOTE: The optional <end_time> parameter for the ec_delete_audit_stats_data stored procedure specifies
the date before which a root process instance must have completed, cancelled, or failed to be included
in the purge list. You should be aware that the completion time for detached sub-process instances is
not considered when purging a root process instance, only the fact that they are complete. If a root
process instance meets the criteria to be included in the purge list, as specified by the <end_time>
parameter, and all of its detached sub-process instances have completed, those detached sub-process
instances are also included in the purge list, even if they have only just completed.

For example, suppose there is a main procedure (proc1) that invokes, in Asynchronous Detached mode, a
single sub-procedure (subproc1). An instance of proc1 is started, invokes an instance of subproc1, and
then completes. subproc1 runs as a detached instance, and completes 89 days after proc1 completed.

Two days after the subproc1 instance completed, ec_delete_audit_stats_data is run, with <end_time> set
to 90 days. The proc1 instance is added to the purge list even though subproc1 only completed 2 days
ago, because:

* proc1 completed 91 days ago (which is more than the 90 day threshold specified in <end_time>) and
* proc1 has no active, detached sub-process instances.

Audit data for the entire proc1 instance chain - including subproc1 - is then purged.

Purging Audit Data for a Completed or Cancelled Parent Process Instance Can Also Incorrectly Purge Audit Data for a Detached Sub-Process Instance That is Still Active