Friday, October 2, 2015

BPM Tasks and Process Instances Clean up – purging data from Process Server DB

BPM Tasks and Process Instances Clean up – purging data from Process Server DB
Removing process instances from the Process Server database
Why is deleting old data necessary?
When an instance completes and all of its associated tasks are closed, future work is not possible with this instance. You cannot re-start it and assign it to someone or edit old work. When a user logs into the portal, various tables are queried to gather data on the active tasks for that user. This operation involves full table scans. Even if only 35% of the data is relevant, it is going to take a while to pull the tasks needed for that user. Thus, if the other 65% is deleted, there is less data to scan.
The cleanup functions provided in the Admin Console remove task data only, not all of the BPD instance data. TheBPMProcessInstancesCleanup command deletes both the instance and task data that is associated with the BPD. Therefore, it is a much more thorough way to clean out BPD instances. You might want your system administrator to set up a scheduled job that invokes theBPMProcessInstancesCleanup command periodically with the correct filter settings for your environment.
You can use the BPMProcessInstancesCleanup command to safely clean up data that is created by runaway processes. The command ensures that even instances with a lot of associated data are deleted in a consistent fashion.
SQL query to identify completed process instances
The following query shows the distribution of all tasks and their status.
Run this SQL query at process server DB
select code.NAME, COUNT(t.STATUS) 
from lsw_task t right join LSW_TASK_STATUS_CODES code on code.STATUS_VALUE = t.STATUS 
group by code.NAME 
order by code.NAME


The following query shows your distribution of tasks by BPD execution status:
select inst.EXECUTION_STATUS, count(*) as total
from
LSW_BPD_INSTANCE inst,
LSW_TASK task
where task.BPD_INSTANCE_ID = inst.BPD_INSTANCE_ID
group by inst.EXECUTION_STATUS
The following query shows you the same data, but just for BPD instances:
select inst.EXECUTION_STATUS, count(*) as total
from
LSW_BPD_INSTANCE inst
group by inst.EXECUTION_STATUS;


select code.NAME, COUNT(bpd.EXECUTION_STATUS)
from LSW_BPD_INSTANCE bpd right join lsw_bpd_status_codes code on code.STATUS_ID = bpd.execution_status
group by code.NAME
order by code.NAME;


BPMProcessInstancesCleanup command

Use this command to delete business process definition (BPD) instance data for a process application snapshot on IBM® Process Server.

Purpose

Using this command deletes the BPD instance and its associated tasks for the instances that are specified by the command parameters. It also logs data to a standard systemout.log file to track which process applications were selected for deleting instance data.
To run this command, you must be in the tw_admins group. Also, you can run this command only in the connected mode. In a network deployment environment, you must run this command on the node containing the application cluster member that handles Process Server applications. Do not run this command from the deployment manager profile.
Restriction: The following restrictions apply:
  • You cannot use this command to delete BPEL process instances.
  • You can run the command from any cluster member in a network deployment environment. However, you must first establish the wsadmin session to the SOAP port of the cluster member from where you are running the command.

Parameters

-containerAcronym containerAcronym
A required parameter that identifies the acronym that specifies the process application to perform process instance cleanup against. For example, the BillingDispute process application might have an acronym of BILLDISP.

-containerSnapshotAcronym containerSnapshotAcronym
A required parameter that identifies the snapshot acronym for the process application. The snapshot acronym can be different from the snapshot name. To find the snapshot acronym, run the BPMShowProcessApplication command. Refer to the topic in the related link.
-instanceStatus instanceStatus
A required parameter that identifies the status of process instances to clean up.
  • COMPLETED - Removes all completed process instances
  • FAILED - Removes all failed process instances
  • CANCELED - Removes all terminated process instances
  • ALL - Removes all completed, failed, and canceled process instances
Tip: You cannot delete an instance that is currently running.
-instanceID instanceID
An optional parameter of type String[] that identifies a list of instance IDs that are being removed. If there is more than one instance ID, each instance ID must be delimited by a space for Jython format commands or a semicolon for JACL format commands. You can also use this parameter withendedAfterLocal or endedBeforeLocal. See the examples in this topic.

-endedAfterLocal endedAfterLocal
An optional parameter of type String that identifies the local time on the server. The string must have the following format:yyyy-MM-ddThh:mm:ss (year, month, day, T, hours, minutes, seconds). Only instances that completed, failed, or canceled after the specified time period by this parameter are processed. If you use this parameter with the endedBeforeLocal parameter, only instances that completed, failed, or canceled during the specified time period by this parameter are processed.

-endedBeforeLocal endedBeforeLocal
An optional parameter of type String that identifies the local time on the server. The string must have the following format: yyyy-MM-ddThh:mm:ss (year, month, day, T, hours, minutes, seconds). Only instances that completed, failed, or canceled after the specified time period are processed. If you use this parameter with the endedAfterLocal parameter, only instances that completed, failed, or canceled during the specified time period by this parameter are processed.
-outputFile outputFile
An optional parameter that provides a qualified file path to write the process instance cleanup log entries. The default is SystemOut.log.
Sample execution
  • Run sql query to determine the no of completed BPD instances
Here 6025 instance is in completed state
Connect to soap port of application cluster member
C:\IBM\WebSphere\AppServer\bin>wsadmin -conntype SOAP -port 8882 -host bpmprocessserver.local.com -user psadmin -password psadmin
WASX7209I: Connected to process "PSDE1.SingleCluster.processserverlocalNode03.0" on node processserverlocalNode03.0 using SOAP connector;  The type of process is: Man
agedProcess
WASX7029I: For help, enter: "$Help help"
wsadmin>
Run BPMProcessInstancesCleanup
$AdminTask BPMProcessInstancesCleanup {-containerAcronym HSAV1 -containerSnapshotAcronym  HSAV856 -instanceStatus ALL -endedBeforeLocal 2015-10-02 -outputFile C:\IBM\WebSphere\AppServer\HSAV1-processinstancecleanup_02Oct15.txt}


Here
-containerAcronym
found by “ $AdminTask BPMListProcessApplications”
Note:each process apps will have an Acronym
-containerSnapshotAcronym
found by “$AdminTask BPMShowProcessApplication {-containerAcronym   “value1”}”
value1 – containerAcronym of process application
Note: each process apps have no of snapshots associated
Check the output file
[2015-10-02 16:04:31.197] About to start the BPMProcessInstancesCleanup with parameters:  -containerAcronym Hiring Sample Advanced -containerSnapshotAcronym HSAV856 -instanceStatus ALL  -endedBeforeLocal 2015-10-02 00:00:00.0 -maximumduration 0 -transactionSlice 1 -outputFile C:\IBM\WebSphere\AppServer\HSAV1-processinstancecleanup_02Oct15.txtSuccessfully ran the BPMProcessInstancesCleanup command with parameters:  -containerAcronym Hiring Sample Advanced -containerSnapshotAcronym HSAV856 -instanceStatus ALL  -endedBeforeLocal 2015-10-02 00:00:00.0 -maximumduration 0 -transactionSlice 1 -outputFile C:\IBM\WebSphere\AppServer\HSAV1-processinstancecleanup_02Oct15.txt
Number of qualifying instances before deletion: 30
Number of qualifying instances after deletion: 0


  • Run sql query to determine the no of completed BPD instances
Here the no of completed instances is reduced to 5972 after deleting the BPD process instances


Removing task data from Process Server DB
Determine the no of closed tasks by running sql query at process server database
select code.NAME, COUNT(t.STATUS)
from lsw_task t right join LSW_TASK_STATUS_CODES code on code.STATUS_VALUE = t.STATUS
group by code.NAME
order by code.NAME
Total no of closed tasks is 17507 and no of tasks received is 1462, hence total no of tasks becomes 18969
Use the cleanup utility at Process Admin console to delete tasks
Task Cleanup utility at process admin console should be used to remove the completed task instances as shown below
After running the cleanup, the tasks which are in closed state are removed and only the received tasks exists…
Note:
  • Choose the last cleanup option among the list of 4
  • Click the clean up button to remove tasks
Task status before clean up
Task status after clean up



Run the sql query again to check no of tasks
No of completed tasks would become zero as shown below
Reference

6 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Thanks for the information for cleaning tasks. It was very useful.

    ReplyDelete
  3. Hi All,
    any explanation about task status ?
    like when we will get "New" and when we will get "actioned" etc...

    ReplyDelete
  4. I thanks for the information shared like this.

    You explained very valuable information about Business process management

    ReplyDelete
  5. Hi ,
    I am looking to cleanse selective processinstances as the bpd instances are created using batch as well as manual. So time to retain automatic and manual process instances are different. is there a way to differentiate batch invoked and manual invoked process instances.

    ReplyDelete
  6. Casinos Near Casinos, Atlantic City - Mapyro
    Discover the closest 거제 출장마사지 casinos to 공주 출장안마 Atlantic City including a full list 양주 출장안마 of casinos with slot machines, blackjack, roulette, 경주 출장마사지 craps, & more. 라이브스코어

    ReplyDelete