Clarity

  • 1.  CA Clarity Tuesday Tip: Delete Process Instances Job

    Posted Aug 30, 2011 08:57 AM
    Title: CA Clarity Tuesday Tip: Delete Process Instances Job

    CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 8/30/2011

    Before we start delving into financials, I have topic or two I'd like to cover. Today, I'd had a need to look at exactly what the delete process instance job does in terms of tables it deletes from.

    This job allows you to delete process instance that have "finished" that are either in an Aborted or Done status. Filtering criteria allows you to select a finish date range, process instance obs, object type, initiated by and initiated OBS.

    Here's how the deletion of the processes works:

    1) The job itself will run the instance selection query and add the filter criteria. Here's the base query without the dynamic filtering components.

    SELECT
    INSTANCES.ID INSTANCE_ID
    FROM
    BPM_DEF_PROCESSES PROCESSES,
    BPM_DEF_PROCESS_VERSIONS VERSIONS,
    BPM_RUN_PROCESSES INSTANCES,
    CMN_CAPTIONS_NLS CAPTIONS,
    CMN_LOOKUPS_V LOOKUP,
    SRM_RESOURCES RESOURCES
    WHERE
    INSTANCES.STATUS_CODE IN ('BPM_PIS_DONE', 'BPM_PIS_ABORTED') AND
    VERSIONS.PROCESS_ID = PROCESSES.ID AND
    CAPTIONS.PK_ID = PROCESSES.ID AND
    INSTANCES.PROCESS_VERSION_ID=VERSIONS.ID AND
    CAPTIONS.LANGUAGE_CODE = ? AND
    CAPTIONS.TABLE_NAME = 'BPM_DEF_PROCESSES' AND
    LOOKUP.LANGUAGE_CODE = ? AND
    LOOKUP.LOOKUP_TYPE = 'BPM_PROCESS_INSTANCE_STATES' AND
    INSTANCES.STATUS_CODE = LOOKUP.LOOKUP_CODE AND
    RESOURCES.USER_ID = INSTANCES.INITIATED_BY

    2) Next the job will iterate through the results gathered in step 1 and perform the following delete actions per process instance.

    a) First, check if this process has any subprocesses; if so, delete subprocesses first. (this essentially will follow 2b-2d for the sub-process)
    b) Delete escalation objects (action items, etc.) for the process and each step instance. Records from the following are deleted:

    - ESC_RUN_USERS
    - ESC_RUN_LEVELS
    - ESC_RUN_UNREG_OBJECTS
    - ESC_RUN_OBJECTS

    c) Delete all associated steps/run records

    - BPM_RUN_OBJECTS
    - BPM_RUN_STEPS
    - BPM_RUN_STEP_ACTION_RESULTS
    - BPM_RUN_STEP_COND_OBJECTS
    - BPM_RUN_STEP_COND_RESULTS
    - BPM_RUN_STEP_TRANSITIONS
    - BPM_RUN_ASSIGNEES
    - BPM_RUN_REPLACE_ASSIGNEES
    - BPM_RUN_ASSIGNEE_NOTES
    - CAL_ACTION_ITEMS
    - CAL_ACTION_ITEM_ASSIGNEES


    d) Delete the process instance
    - BPM_RUN_PROCESSES
    - BPM_RUN_OBJECTS
    - BPM_RUN_THREADS
    - BPM_ERRORS
    - BPM_ERROR_ARGUMENTS

    ** After every 100 delete process cycles (this is the default), Clarity will commit the transaction.


  • 2.  RE: CA Clarity Tuesday Tip: Delete Process Instances Job

    Posted Aug 30, 2011 01:52 PM
    Hi Shawn,

    One quick question. I've around 400K process instances. If I try to delete those through that Job it takes forever and it never completes. is there any alternate way to delete those instances? or if you could give a tip on how to handle my situation would be great.

    Environment: 12.0.1, SQL Server.

    Thanks
    Saran


  • 3.  RE: CA Clarity Tuesday Tip: Delete Process Instances Job

     
    Posted Aug 31, 2011 11:16 AM
    Great info Shawn!

    Thanks!
    Chris


  • 4.  RE: CA Clarity Tuesday Tip: Delete Process Instances Job

    Posted Sep 06, 2011 07:37 PM
    Saran,

    The best way is through the application, just because there are so many dependencies. Manual deletion go be tricky to get everything and if something is missed then you would have orphan data. Schedule the job to take a piece of it at a time, by using some of the job's filtering criteria and maybe run it on a weekend or during lighter usage times.

    -shawn


  • 5.  RE: CA Clarity Tuesday Tip: Delete Process Instances Job

    Posted Sep 05, 2012 12:02 AM
    I have a question regarding the 'Delete Process instance' job. We have a process that call a sub-process. The completion mode is set to Independent. In the production envrionment we also run the Delete Process Instance job and it removes all completed instances. But we have observed that when the parent process is deleted it also deleted the running instance of the sub process.
    Is there any configuration change needs to be done to avoid this?


  • 6.  RE: CA Clarity Tuesday Tip: Delete Process Instances Job

    Broadcom Employee
    Posted Oct 12, 2012 06:17 PM
    I know this is a late reply. But I actually get asked this question fairly frequently.

    The long-term answer is to develop a process maintenance plan per my Tuesday tip on 9/5/2012.

    But, once you get yourself into this situation, you need to limit the amount of time that you are deleting processes for so that you are deleting 5000 or less processes each time you run the job. This job's performance will get progressively worse when you try to delete large numbers of processes. Deleting smaller amounts of processes will get the job done more quickly.

    For example, if you have a year's worth of processes and you now have say 20,000 processes to delete, try deleting 3 months worth at a time, starting with the oldest processes. You can also run the job for only aborted processes or only completed processes to cut down on the number of processes that you are deleting at once.

    Once you are done and you get yourself back to the point that you have a reasonable number of processes (this amount varies depending on how beefy your servers are), you will probably notice a huge performance increase when you look at your initiated processes tab.

    Jeanne


  • 7.  RE: CA Clarity Tuesday Tip: Delete Process Instances Job

     
    Posted Oct 12, 2012 07:06 PM
    Thanks for sharing this info Jeanne! Have a great weekend! :grin: