Using the LSW_BPD_INSTANCE_DELETE stored procedure

Use the LSW_BPD_INSTANCE_DELETE stored procedure to remove all runtime data in the Process Server database that is associated with a completed business process definition (BPD) instance.

Before you begin

Run the query during an off period or maintenance window. When thousands of instances and data are purged, this process might cause a strain on the LSW_TASK and LSW_BPD_INSTANCE tables, which are core product tables. Running a cleanup job outside of normal business hours is a good practice.

If you want to archive data rather than deleting it, copy the data into a custom-built table outside of the product schema.

About this task

Completed BPD instances are not deleted from the system automatically. After a process instance is completed, the instance is typically no longer needed, so it can be removed from the Process Server database. Use the LSW_BPD_INSTANCE_DELETE stored procedure to delete old instances.

When an instance completes and all of its associated tasks are closed, future work is not possible with the instance. You cannot restart it, assign it to someone, or edit old work. When a user logs in to Process Portal, various tables are queried to gather data on the active tasks for that user. The operation involves full table scans, so that even if only 35% of the data is relevant, it is going to take a while to pull the tasks needed for the user. If the other 65% is deleted, there is less data to scan.

If you do not delete old completed instances, your team experiences slow performance on Process Portal and a potentially unusable state. Ignoring increases in database size cause an increase in backup time and disk space.

Deleting old instances affects only the search for history items from the Process Portal inbox. When you run the delete queries, you can specify that only completed tasks that are older than 30 days will be deleted. Store any data that you really need in the performance database or another system of record for auditing or metrics.

How often you delete old instances depends on the following factors:

How many instances are closed in a specified time period (for example, a week or month)
How large the data is in each task (including execution context and document attachments)
How many tasks there are in a process instance

The two most common reasons for runtime data growth are documents and execution context. If you have many documents and you must reference them later, consider a document management solution. Execution context is all the data that is carried from activity to activity. If the solution has many variables with large amounts of data, this scenario quickly uses database space. In this case, reviewing your solution is a good idea to reduce the amount of processor usage in the application.

You can use the stored procedure to safely clean up data that is created by runaway processes. This approach is acceptable for scenarios with event-based undercover agents associated with tasks. The undercover agent makes up to five attempts, after which it stops, and nothing is required from the Process Portal user.

The cleanup utility that is provided in the Admin Console removes task data only, not all of the BPD instance data. The LSW_BPD_INSTANCE_DELETE stored procedure 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. If you are using stand-alone services, consider running the cleanup utility after you run the LSW_BPD_INSTANCE_DELETE stored procedure. You might want your database administrator to construct a recurring job that queries for, then deletes, the instances that you must delete.

The LSW_BPD_INSTANCE_DELETE stored procedure removes all runtime data that is associated with the instance in the following database tables:

Dynamic groups that are created for the instance from:
- LSW_USR_GRP_MEM_XREF
- LSW_USR_GRP_XREF
Tasks that are associated with the instance from:
- LSW_TASK_ADDR
- LSW_TASK_EXECUTION_CONTEXT
- LSW_TASK_NARR
- LSW_TASK_FILE
- LSW_TASK_IPF_DATA
- LSW_TASK_EXTACT_DATA
- LSW_TASK
BPD instance data from:
- LSW_BPD_INSTANCE_DOC_PROPS
- LSW_BPD_INSTANCE_DOCUMENTS
- LSW_BPD_INSTANCE_VARIABLES
- LSW_BPD_INSTANCE_DATA
- LSW_BPD_NOTIFICATION
- LSW_RUNTIME_ERROR
- LSW_BPD_INSTANCE
- LSW_INST_MSG_INCL
- LSW_INST_MSG_EXCL

Procedure

Query the LSW_BPD_INSTANCE table for all closed instances that fall within a date range that you want to remove.

Input the resulting instances into the stored procedure. You can get the date from the LAST_MODIFIED_DATETIME column in the LSW_BPD_INSTANCE table. The closed instances have an EXECUTION_STATUS value of 2, which means that the instance completed.

The following table shows the meanings of the values in the EXECUTION_STATUS field in the LSW_BPD_INSTANCE table.

Note: Querying the internal system tables is not supported. This information is for documentation purposes. The values are subject to change at any time without notice.

Table 1. EXECUTION_STATUS values in the LSW_BPD_INSTANCE table
Flag	Meaning
1	The BPD instance is active.
2	The BPD instance has completed.
3	The BPD instance has failed. Look at the ERROR and ERROR_STACK_TRACE for further details.
4	The BPD instance has terminated.
5	The BPD instance did not start because its message event condition returned false.
6	The BPD instance is suspended.

Example

For example, 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