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

http://www-01.ibm.com/support/knowledgecenter/SSFPJS_8.0.1/com.ibm.wbpm.ref.doc/topics/rref_BPMProcessInstancesCleanup.html?lang=en

http://pic.dhe.ibm.com/infocenter/dmndhelp/v8r5m0/index.jsp?topic=%2Fcom.ibm.wbpm.admin.doc%2Fadminguide%2Ftopic%2Fremoving_proc_instances_stored_procedure.html

http://www-01.ibm.com/support/docview.wss?uid=swg21439859

http://www-01.ibm.com/support/docview.wss?uid=swg21439564

http://pic.dhe.ibm.com/infocenter/dmndhelp/v8r5m0/index.jsp?topic=%2Fcom.ibm.wbpm.admin.doc%2Fadminguide%2Ftopic%2Fremoving_proc_instances_db.html