Looking for help?

Submit a request

Articles in this section

See more

IBM BPM 8.6 and IBM BAW v.19.0.0.x / 20.0.0.x / 21.x purging/cleanup full guide

Avatar
Sergei Malynovskyi
Updated
Follow

TABLE OF CONTENTS

 INTRODUCTION

There are several areas of IBM BPM system level cleanup that should be done periodically to maintain a clean and performant IBM BPM and more importantly database server.  These operations should be done on a regular basis and should help you save disk space on the database server, and improve overall performance of IBM BPM environment. In this article I would focus not only on how to do it but also things to be aware of, common errors/issues you might observe during cleanup and a better understanding of overall procedure. I won't be focusing on WHEN you need to start doing cleanup because in my opinion this is something that should be planned from the very beginning of your IBM BPM journey, especially in all Runtime environments. From my experience the longer you wait, the harder it's to get to this exercise and the more complicated the process of cleanup/purge would look like. It's fine if you have audit requirements to store the data older than X days but it does not mean that you should not be doing cleanup/purge, it means that you should start doing it after 90 days of usage of IBM BPM. If you have a more strict audit / rotation of old data policy then consider having separate archival BPM database where you could store the data for audit purposes.

You may notice quite a big number of references to IBM Knowledge Center in this article. The main reason for this is the syntax of the commands and their usage, especially all the "wsadmin" commands. I'm not going to copy the syntax/parameters/usage directly into this article, instead the main purpose of this article is to: combine all the latest pieces of a cleanup process together in one place as well as provide tidbits and gotchas for various cleanup options that are available to you in IBM BPM 86 and IBM BAW 19/20/21.

This article focuses on BPM 8.6 and IBM BAW v.19.0.x/v.20.0.x/v.21.x

Critical amount of runtime data (New feature in IBM BAW v.19.x/v.20.x/v.21.x)

Starting IBM BAW v.18.0.0.1 the following useful information is added in the system out log each time during server startup - 

The system maintenance monitor is determining if maintenance is required. The system is in an OK state.

[10/9/18 12:48:59:415 UTC] 00000171 MaintenanceLo I com.ibm.bpm.maintenance.MaintenanceLogger log

Current number of snapshots: 77
Maximum number of snapshots allowed: 500
No action is required.

[10/9/18 12:48:59:416 UTC] 00000171 MaintenanceLo I com.ibm.bpm.maintenance.MaintenanceLogger log

Current number of unnamed snapshots: 1,093
Maximum number of unnamed snapshots allowed: 10,000
No action is required.

[10/9/18 12:48:59:417 UTC] 00000171 MaintenanceLo I com.ibm.bpm.maintenance.MaintenanceLogger log

Current number of task instances: 119
Maximum number of task instances allowed: 2,400,000
No action is required.

[10/9/18 12:48:59:418 UTC] 00000171 MaintenanceLo I com.ibm.bpm.maintenance.MaintenanceLogger log

Current number of process instances: 38
Maximum number of process instances allowed: 600,000
No action is required.

[10/9/18 12:48:59:419 UTC] 00000171 MaintenanceLo I com.ibm.bpm.maintenance.MaintenanceLogger log

Current number of durable messages: 3
Maximum number of durable messages allowed: 8,000,000
No action is required.

You may get all of this information using the database queries and/or wsadmin commands in earlier versions of IBM BPM but above information gives you a good idea of what IBM considers good/allowed numbers of snapshots/instances/tasks/durable message events in the system. So, you may do your cleanup/purge job based off of this information. Per IBM BAW documentation when you reach the limit it disables installation of snapshots until the issue is addressed. You may control the exact numbers via corresponding configuration properties that you can find here.

TERMINOLOGY

Named snapshot(s) - these type of snapshot(s) are present in both type(s) of BPM servers - Development and Runtime. In Development they are created each time you click on (Snapshot) icon in PD/WebPD to give a name to your snapshot. In Runtime named snapshot(s) are all the application snapshot(s) you deploy from your Development environment. (it does not matter if it's online or offline deployment).

Un-named snapshots - these type of snapshot(s) are present only in your Process Center / Development environment(s). They are NOT present in any of Process Server / Runtime environments. In Development these snapshot(s) are created each time any developer working in PD/WebPD performs a "Save" operation in any item (BPD, service, etc).

PC/DEV - short form of Process Center / Development BPM environment

PS/Runtime - short form of Process Server / Runtime BPM environment

PDW - short for of Performance Data Warehouse

BPM Database and relation between important tables

As you may know, all BPM objects are stored in the BPM databases. Please note that below information is just shared for the educational purposes. BPM database is considered to be a "black box" and you should NOT manipulate data in any of the below tables manually. All should be done through APIs and other supported methods.  For the purposes in this article, we start by examining the following:

  • LSW_PROJECT – This is where all the process app(PA) and toolkit(TK) names are stored.  The columns we are most concerned with are:
    • NAME – the name of the PA/TK.  This is shown in the Process Designer and Process Center Console as the logical display name.
    • PROJECT_ID – the GUID of the PROJECT.  This will be used to lookup the various snapshots in the LSW_SNAPSHOT table.
  • LSW_BRANCH – This is where all the PAs and TKs tracks are stored
    • NAME – the name of the Track (Main - default).  This is shown in the Process Designer and Process Center Console as the track name in the left top corner (drop-down list)
    • BASE_SNAPSHOT_ID - this column would be populated only when it's a non-default track (not Main), then it would point to a base (Main) snapshot id
  • LSW_SNAPSHOT – This is where all the named and unnamed snapshots will be listed.
    • PROJECT_ID – we use this to select the appropriate snapshots for a project we want to examine.
    • NAME – Name of the snapshot if a name has been specified (by a user creating a new snapshot), or null if the snapshot is unnamed.
  • LSW_DUR_MSG_RECEIVED – Where durable intermediate message events (IMEs) are stored.

Now let's talk about what are the most important tables that gets cleaned up depending on what type of cleanup operation you execute:

PA/TK snapshot cleanup:

Main tables tat are involved in this cleanup are - LSW_SNAPSHOT, LSW_PO_VERSIONS, LSW_FAVORITES, LSW_BRANCH, LSW_PROJECT, LSW_USR_GRP_XREF, LSW_USR_GRP_GRP_XREF, LSW_USR_GRP_*

IMPORTANT NOTE: As part of snapshot cleanup instances and tasks are cleaned up automatically as well. So, see the next section to see what tables are involved in instances/tasks cleanup.

Instances/tasks cleanup:

Users/groups:

  • LSW_USR_GRP_MEM_XREF
  • LSW_USR_GRP_XREF
  • LSW_USR_GRP_*

Task associated with this 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 (only if legacy BPM 8.0.x/7.5.x docs are still used)
  • LSW_BPD_INSTANCE_DOCUMENTS (only if legacy BPM 8.0.x/7.5.x docs are still used)
  • LSW_BPD_INSTANCE_VARIABLES
  • LSW_BPD_INSTANCE_DATA
  • LSW_BPD_NOTIFICATION
  • LSW_RUNTIME_ERROR
  • LSW_BPD_INSTANCE
  • LSW_INST_MSG_INCL

Durable message event cleanup:

  • LSW_DUR_MSG_RECEIVED

Types of cleanup (Runtime data , Snapshot Data)

What you need to know before you begin

There are certain things to be aware of before you start performing any type of cleanup and especially snapshot cleanup. 

Instance/Task cleanup what you need to know - 

  • It's better to perform it by schedule at the time when there is little to none activity on BPM server. It puts quite a high load on the database and on a big number of important BPM tables, so, the less overloaded BPM server is the better
  • In this article, we're discussing BPM 8.6.x and all IBM BAW releases but in case you need to purge instances or tasks in earlier versions of BPM you may just use stored procedures (LSW_BPM_INSTANCE_DELETE, LSW_ERASE_TASK)
  • Make sure you have a good backup of the database before you perform any type of cleanup (especially in Production). So, a good schedule sequence would be - backup of the database first, cleanup procedure next.

Snapshot cleanup what you need to know - 

  • Probably the most important part before you begin is to check this technote 
    Above technote contains all the information regarding the fixes you need to have installed prior to using snapshot cleanup commands in IBM BPM (depending on the version of IBM BPM you are using). If you don't go through this exercise of checking and installing required fixes there is a very high chance that your environment will be broken and unusable after you try to execute cleanup procedure.
  • Specific to un-named snapshot(s) in PC/DEV - 
    http://www.ibm.com/support/docview.wss?uid=swg1JR58096
    Above fix is important if you're going to use manual or automated cleanup procedure of un-named snapshot(s). Make sure it's installed before you proceed.
  • Snapshot removal can be slow depending on the version of IBM BPM you're using. If you're on IBM BPM 856 or 857 GA, check out the following APAR JR56726
    From my experience it can improve the time of the BPMDELETESNAPSHOT command at least 2x and in some cases even more (depending on the amount of data in the tables and LSW_PO_VERSIONS in particular). I know IBM support has a version of this ifix for IBM BPM 856 CF2 as well, so, you may request it from them. Or you can upgrade to latest IBM BAW releases where it's included.
  • Do not remove any of the system toolkit(s) or applications. Ideally, IBM BPM should simply prevent you from doing it but it won't, so, be careful. Removing any system toolkit or application might cause negative effects (even if it's archive/inactive/old snapshot). By system toolkits here I mean - System Data toolkit, Responsive coaches toolkits, Coaches toolkit, Dashboards toolkit, etc. Applications - Process Portal, Responsive Portal, Heritage Portal.
  • The final note is - take a look at the above points for "instance/task cleanup" - they are fully applicable to "snapshot cleanup" as well (e.g. schedule during inactivity period, backup first).

Process Instances Cleanup

There are few different methods that can be used to cleanup instances depending on your requirements.

 

  • You may use wsadmin "BPMProcessInstancesPurge" as described here.
  • You can also use REST API to remove instances. The REST API call looks like this - 
    To delete all process instances that are in the states finished or terminated: 
    DELETE https://host:port/ops/std/bpm/processes?states=finished,terminated
    There are additional options for the REST API call, you may read more here.
  • Starting IBM BPM 8.6 and in all BAW releases there is an additional UI that was added to Process Admin console called - "Health Management". It's available when you login to Process Admin console and goto "IBM BPM Admin" -> "Health Management" screen. More information can be found here.
  • Keep in mind that if you're migrating from older versions of IBM BPM / WLE then you might have used LSW_BPD_INSTANCE_DELETE back then but it's NOT suggested to use it anymore even though it can still be found in the database. The main reason why it's not suggested to use it is because it won't cleanup corresponding documents in the ECM tables for the built-in document store.
  • It's a good practice to do a cleanup of process instances before running a cleanup of a snapshot. E.g. do it in two separate transactions, this would typically speed up deletion of a snapshot. When you delete a snapshot it would remove all the completed instances on that snapshot, so, if you do remove those instances using the above process instance cleanup / purge command then the removal of such a snapshot would work faster and more reliable.

Tasks Cleanup

There is a wsadmin command called "BPMTasksCleanup" that allows you to cleanup tasks.

There are no special "gotchas" for this command, so, reviewing the official documentation should be enough to get you started.

There is also a utility available in BPM Process Admin console called - "Task Cleanup utility" that allows you to delete tasks from the IBM BPM Process database. I personally call it a "sledge hammer" because it might remove the tasks that you were not expecting...So, use it in lower environment and/or DEV when you need to do a bulk delete of tasks but never in Production. It might also cause transaction timeout(s) on the database and rollbacks if big amount of tasks are to be removed, so, be aware.

Tasks cleanup might be useful in the following scenarios - 

  • You have a set of closed tasks that you know can be cleaned while their instances are still in active state
  • You have a set of closed tasks that you know can be cleaned while the instance that is in complete state should remain un-touched
  • You're doing a cleanup of snapshot(s) and as a preparation you may want to cleanup tasks first, then instances, then snapshot(s). Or you might need to cleanup only tasks in some cases in order for the snapshot deletion to complete successfully (see below section "Common error(s) during snapshot cleanup..." for more information) 

Purging IBM BPM Portal Search Index (Lucene)

The Process Portal index allows end users who are working in Process Portal to search for instances and tasks. The index is also used to provide data for the charts in the Process Performance and Team Performance dashboards. 

This indexing is using Lucene behind the scene and it's enabled by default in IBM BPM. Process instances and tasks are indexed according to a time interval that you can specify. To change the indexing behavior, you must edit the 100Custom.xml configuration file. Check this infocenter link for more details. 

In order to build up this index, BPM uses database tables as well as filesystem (for the local storage of Lucene index). The tables in BPM database for the index are - 

  • (1) BPM_TASK_INDEX

  • (2) BPM_INSTANCE_INDEX

  • (3) BPM_TASK_INDEX_JOB

Currently, there is no automatic maintenance task running, which will delete unneeded data from table (1) and (2). Only for table (3) cleanup is done on a regular basis (by default every hour).

If you're running on IBM BPM beyond IBM BPM 8.6 CF2018.03 then the following dwAnswer post has all the details and SQL scripts you need to know on how to cleanup (1) and (2) tables after you perform a cleanup of snapshots / instances / tasks. 

If you're running on IBM BPM 8.6 CF 2018.03 or IBM BAW v19.x/20.x/21.x then the scripts/commands are included as part of the Product (indexTablesCleanup.bat (Windows) or indexTablesCleanup.sh (Linux)). More details can be found in the following infocenter article.

The overall advice is to always run this cleanup after you do a cleanup of any of runtime data.

Durable Message Events

  • IBM BPM that allows you to cleanup "Durable Message Events" as a separate activity. The command for this exercise is "BPMDeleteDurableMessages"
  • This command/utility has this required parameter - "-olderThan age_in_days"
    Specifies an age in days. Durable subscription messages that are older than the specified age are deleted when the command is run.
  • It also allows you to specify the maximum duration and the transaction slice. We are going to talk about all of these parameters more in details in this section but let's first talk about what are those "Durable Message Events" in IBM BPM terms?

Durable Message Events are configured in IBM BPM "Intermediate Message Events" or "Intermediate content events". There is a checkbox that you can check to make your message have a "durable subscription" as you can see in a screenshot below -

IME.png

 

The purpose of this option is to indicate that it us OK if the message is received prior to the token moving on to the intermediate message event. If you do NOT check that checkbox, then an intermediate message event will only fire if a correlating message is received after a token has moved on to the step. If you do check that checkbox, then the event manager will store all messages it receives of that type. Then, whenever a token moves on to a intermediate message event for that type of message, the event manager will query the table to see if there were any correlating messages it had already received.
As such, these durable message events exist and being persisted in the database table (LSW_DUR_MSG_RCVD). And such events are never cleaned up from the database (automatically) due to the nature of this option. There is no way of knowing what can be cleaned up automatically thus there is a manual process involved.

You might be concerned about the strategy for a cleanup of these events due to the nature, so, I'm going to present a Q&A section below to hopefully help you answer most common question you might have on this particular part of cleanup in IBM BPM - 

Q1: Do the application servers need to be stopped? 

A1: Not necessarily but as with any other cleanup it's better to do it when the system is NOT under a heavy load.

Q2: Do we want/need to clean up any durable subscription events?

A2: Good question :) I would say - it depends ... I would not be so worried about the durable subscription table unless you have millions of records there. So, starting 1M and more I would look into cleaning it up. I would also definitely look into cleaning it up if you see any queries associated with the table in question (LSW_DUR_MSG_RCVD) pop up in any of your expensive query reports that your DBA sends you. So, if you notice that there are any queries in any of top SQL categories and there is this table involved then definitely consider a cleanup. The good thing about this particular cleanup is that is cleans up only this single table, it has not direct associations with other tables in IBM BPM schema.

Q3: Do you know of any common-knowledge tidbits related to these operations
that we might not know?

A3: There is few things that are important to take into consideration when you choose on the strategy for a cleanup of durable message events. So, if you will have inflight/active instance with an intermediate message event that will be waiting for the message that got into this table and you would clear it out then once the token gets to the IME (intermediate message event) it will not fire because it won't find the record. That being said, there is a column CREATED_ON in the LSW_DUR_MSG_RCVD table as that would indicate when the message has been created.
So, if you don't have long running processes then you can cleanup based on the data in this column, i.e. delete all msgs older than 2.5 months, something like this.
There is also a UCA_ID that you can be correlated with LSW_UCA and in turn join PROCESS_ID in LSW_UCA with LSW_PROCESS PROCESS_ID to find out the service and other assets that might give you additional idea on things like - is this service that is UCA related even used anymore in the solution or not and based on that data you can get an idea on what can be cleaned out. If you accidentally delete the message that one of the IME's in one of your instances was expecting to receive - well, the IME (intermediate message event) will not fire and the token will not move from IME. So, you could potentially send that message again and it will receive it and will go further along. You will not loose any of inflight instance data except the fact that if you removed one of the messages that you send to your instance it won't be there anymore meaning that it has to be sent again if someone if waiting for it.

The whole purpose of durable message events is that the token will fire as long as it will be on IME assuming that the message is already received by BPM. It's important to understand that it has nothing to do with any internal BPM messages. These are the messages that you send to BPM. So, you would know best what are the right parameters for that cleanup based on your solution that you run in BPM.

Q4: Are there recommended maximum durations (maximumDuration) or transaction slices (transactionSlice)? 

A4: These two parameters are optional and I would say that in most cases using default values is fine. The "maximumDuration" parameter specifies the maximum duration of the deletion operation in minutes. The default value is 0 (which is equivalent to an unlimited number of minutes). When the maximum duration is exceeded, the command is aborted after the current transaction has committed. I would say that in most cases this cleanup is quite fast because it cleans up records only in one single table. So, unless you think you specified age parameter, so, that it's supposed to cleanup millions of rows then having this parameter set at 0 should be fine.

The "transactionSlice" specifies the number of durable subscription message instances that are deleted with each transaction. The default value is 1000. It should be fine to leave it at 1000 again unless you think you want to cleanup faster because you have a lot of events to be removed in which case you can try increasing this number.  

Old EPV values cleanup

Important tidbits about the old EPVs cleanup - 

  • This particular type of cleanup is often overlooked. Not many of IBM BPM admins know that it actually exists and yet in some cases this may dramatically improve performance of your BPM applications. In BP3 Labs we have had experience working with one of our clients where we noticed that the database reports of top SQL queries contained queries related to EPV's in almost every top category. So, if you notice something similar and/or you know for sure that your BPM applications use a lot of EPV's then you may want to look into cleaning up old ones because as database and corresponding tables get bigger the queries become slower and slower for getting and updating EPV values.
  • Old values can be deleted via the Process Admin console or via the BPMEPVHistoryCleanup wsadmin command.
  • When you delete a snapshot all the related EPV data for that snapshot will be deleted. Additionally, when you migrate instances or snapshot data, the EPV values will be migrated from one snapshot to another. This will include historical EPV data to ensure existing tasks are able to use the right values. So the EPV data for the current snapshot will slowly increase over time if you use instance migration. This isn't something you can avoid as currently you can't migrate instances without also migrating snapshot data.

  • Because only EPV values with an effective_on date set to the future can be deleted in the Process Admin Console, all historical EPV values remain in the database. Therefore, the number of EPV values increases over time. This increase depends on the number of snapshots, the number of EPV values in a snapshot, and the number of synchronizations between snapshots. This quantity decreases performance during run time as well as the performance in the Process Admin Console while you manage EPVs.

Event Manager on-hold tasks deletion

  • In IBM BPM 8.6 or IBM BAW you have an ability to delete Event Manager tasks that are on hold. Event Manager tasks are special kind of tasks that are not human tasks. Event Manager tasks include UCA's, timer events, message events, token move tasks. You can read more about Event Manager function in IBM BPM here. As you may know there are cases when Event Manager might get stuck and as a result you may get Event Manager tasks (UCAs, timer events, BPD token move tasks, Message Events) getting stuck. As a result in the event manager console, these events are scheduled for execution on "1/2/99" or "2099-02-01" depending on localization. In other words, they will stay there forever unless you replay them using the "BPMReplayOnHoldEMTasks" command. But if you know for sure that you don't want to replay those tasks and instead, you want to get rid of them because associated process instance is no longer valid for such a replay then use newly added commands for that - "BPMListOnHoldEMTasks" and "BPMDeleteOnHoldEMTasks"
  • You can also do this using the UI in PA console. IBM PA Console -> IBM BPM Admin -> Health Management -> Delete Event Manager Tasks
     

Shared Business Data Objects 

Important tidbits about the Shared Business objects cleanup - 

  • Hope at this stage you have already considered all the pros and cons of using Shared Business Objects in IBM BPM. If you did and you know you started using them in your IBM BPM applications then it's important to know how cleanup process works.

  • Starting IBM BPM 8.5.6 the shared business objects do clean themselves up, but it's not a straight forward process. The system needs to keep a copy of the original shared BO, any currently used copies, plus a backlog as set by configuration. Read more about this here. This is also applicable to all the higher releases of IBM BPM after 856.

  • The system performs the cleanup based on the cleanupMaxVersionCount and cleanupMinAge configuration parameters. To specify the versions of the business objects that you want to keep, use wsadmin scripting to modify these configuration parameters as written in the IBM Knowledge Center.

  • Prior to IBM BPM 8.5.6 there is no option for clearing shared business objects. They do get cleaned up to some extend when removing instances and/or whole snapshot.

  • There is an important note regarding the load() method that is directly related to an automated cleanup process. Before you enable cleanup, look in your code for shared business objects that do no have automatic synchronization enabled and make sure that load() is called before a change is made to the business object.

  • If you are not using shared business objects yet in your solution but considering using them then make sure you do all the research first and see if it fits your solution. Situations where you have small amounts of shared data that only need to persist for short periods of time should be ok. Where you get in trouble is with large, complex business objects that are highly accessed and updated, and also maintained for long periods. Namely under that set of conditions you're going to be proliferating large amounts of data in the database, in addition to any data access/performance considerations that might bring.

    A shared business object is logically connected to the process instance that created it. If the shared business object is created in a human service that started independently of a process, it is connected to the corresponding task. Other process instances can also use the shared business object if it is referred to by one of the process variables. When the connected process or task is deleted and no other process instances refer to the shared business object, the shared business object is automatically deleted. When a new version of a shared business object is saved, older versions of the shared business object that are no longer required are automatically deleted. However, you can specify how many versions of business objects are kept in the system.

    And when a shared business object is saved, it is required that the original version is still available. In earlier versions of IBM BPM, all versions of shared business objects were kept, and so the original version was always available. As already mentioned, starting with IBM BPM version 8.5.6, the system deletes old versions of business objects that are no longer required. The system keeps track of which BPD instances reference which version of a shared business object. The versions referenced by active BPD instances are never deleted even if they are eligible for deletion according to the configuration parameters.

Snapshot Cleanup

The assumption here is that you went through the previous section called "What you need to know before you begin". 

Process Server:

Important tidbits about the Snapshot cleanup on Process Server / Runtime environments - 

  • The snapshot cleanup itself is an easy task / single command but keep in mind that it removes not only the snapshot itself but all the runtime data that is associated with the snapshot in question (completed instances/tasks)
  • Special attention might be required for the "inactive" toolkit snapshot cleanup. You should be able to identify those toolkit(s) that are safe to remove using wsadmin commands (BPMListProcessApplications, BPMShowProcessApplication, BPMShowSnapshot). You may also use a SQL query for this which might be simpler to use especially in lower environments.
    Starting BPM 857 there is a single command that returns a list of toolkit snapshots that are not referenced by other process apps or toolkits. You can use the list to delete the orphaned snapshots on your server (BPMShowOrphanedToolkits). 
    If you're running on a prior version of IBM BPM or if you want to cross check the information that is provided by BPMShowOrphanedToolkits vs what's in the database then use the following handy-dandy queries - 

    The following query would give you all the toolkit snapshots that have no dependencies on any other snapshot(s) - 

    select  p.SHORT_NAME, s.NAME, s.CREATED_ON, p.IS_SYSTEM from TSTDBUSR.LSW_SNAPSHOT s, TSTDBUSR.LSW_PROJECT p where s.PROJECT_ID = p.PROJECT_ID and s.SNAPSHOT_ID not in (select d.TARGET_SNAPSHOT_ID from TSTDBUSR.LSW_SNAPSHOT s, TSTDBUSR.LSW_PROJECT_DEPENDENCY d, TSTDBUSR.LSW_PROJECT p  where s.SNAPSHOT_ID = d.TARGET_SNAPSHOT_ID and p.PROJECT_ID = s.PROJECT_ID group by d.TARGET_SNAPSHOT_ID) and p.IS_TOOLKIT = 'T' and p.IS_SYSTEM ='F'

    The following query would give you all the toolkit snapshots and their dependencies - 

    select p.SHORT_NAME, s.NAME,d.TARGET_SNAPSHOT_ID, count(d.PROJECT_DEPENDENCY_ID) as dependancy from LSW_SNAPSHOT s, LSW_PROJECT_DEPENDENCY d, LSW_PROJECT p  where s.SNAPSHOT_ID = d.TARGET_SNAPSHOT_ID and p.PROJECT_ID = s.PROJECT_ID group by d.TARGET_SNAPSHOT_ID, s.NAME, p.SHORT_NAME

  • Do not forget that one of the most important steps AFTER the cleanup/removal procedure is complete is a database maintenance. E.g. Re-indexing and rerunning statistics is important, so, database knows that it has less data now in a set of tables and can use indexes and queries using a more efficient query plan.

There is a number of ways how you can remove snapshot(s) in IBM BPM Process Server/Runtime environment especially in latest versions of IBM BPM. So, I'm going to list different options here and you can pick up that fits your needs best -

  • Probably the main method is using the wsadmin command "BPMDeleteSnapshot". There is no need to go into depth of command syntax here because it's all documented in the above IBM Knowledge Center link. It's best to script it into wrapper wsadmin scripts that does all the checks and automated the whole deletion process for you. Luckily, BP3 Labs has recently developed and released such scripts that are available in the following article.
  • Other two methods to cleanup / delete snapshot are using the PA console and/or REST API call. Both of these options are well documented in this IBM Knowledge Center article.

Process Center/Workflow Center:

Important tidbits about the Snapshot cleanup on Process Server / Runtime environments - 

  • As we already discussed at the biggest difference between the Process Center and Process Server in a context of "snapshots" is that PC has named and un-named snapshots stored in the database while PS only has named snapshots.
  • It's important to cleanup not only named snapshots in PC but also un-named because the number of un-named snapshots in PC is much higher than un-named.
  • Typically PC BPM database performance issues due to slow queries start to appear much sooner than in any of PS environments. This is mainly because Developers are creating much higher number of named and un-named snapshot(s) in PC compared to PS where you can only deploy snapshot(s). So, the sooner you start planning the cleanup strategy in PC the better.
  • You might experience slowness in UCA executions, different REST API's, exposed items, and so on due to a high number of snapshot(s) as well as complex applications. By complex applications in this case I mean those applications that contain a lot of complex toolkits dependencies (where you have PA with many TK dependencies and in turn those dependencies are having other TK dependencies and so on). The delay in many cases due to inefficient algorithm where it searches through ALL snapshots including unnamed ones but it also retrieves all the PA Business Object data when it tries to execute a UCA and so, the more complex PA the more time it takes to execute a single UCA. You may put your vote in this IBM RFE to implement a better algorithm for this.

There is a number of ways how you can remove named and un-named snapshot(s) in IBM BPM/BAW Process Center (Workflow Center)/DEV environment. Especially in latest versions of IBM BAW. So, I'm going to list different options here and you can pick up that fits your needs best -

Un-named Snapshots deletion in Process Center (Workflow Center) server:

  • Checkout the following article where BP3 Labs has developed a number of scripts including the one that helps with the deletion of un-named snapshot(s).
  • The next option to mention here is an "Automated un-named snapshot cleanup in PC/DEV". There are 2 options for this:
    Option 1) Deleting unnamed snapshots in bulk from a Process Center (Workflow Center)
    You can configure IBM Process Center (Workflow Center) to automatically delete unnamed snapshots in bulk at a scheduled time from all process applications and toolkits.  
    Option 2) Deleting unnamed snapshots dynamically from a Process Center (Workflow Center) server
    You can configure IBM Process Center (Workflow Center) to automatically delete unnamed snapshots from active projects as and when named snapshots are created, on an ongoing basis.
  • If you go down the automated cleanup path (either bulk or dynamic) of cleaning up un-named snapshot(s) I suggest using default option(s) in XML configuration. They work in most cases.
  • Finally, there is an option where you can script the deletion of un-named snapshots using wsadmin command (that's what we use in our DevOps scripts we developed at BP3 for our Labs clients). The wsadmin command to delete unnamed and archived snapshots for a process: BPMSnapshotCleanup

Named Snapshots deletion in Process Center (Workflow Center) server:

  • Using the wsadmin command "BPMSnapshotCleanup"
  • Other two methods to cleanup / delete snapshots on Process Center (Workflow Center) are using the PA console and/or REST API call. Both of these options are well documented in this IBM Knowledge Center article.

Remove Process Application and/or Toolkit (Process Center only)

In Process Center/DEV environment you also have an ability to completely remove whole Process Application and/or Toolkit. When you do so, it will remove all the snapshot(s) associated with this PA and/or TK and all of its runtime data. There are two main prerequisites for this -

  • Process Application and/or Toolkit needs to be archived first using PC console and/or wsadmin
  • You must be a member of the tw_admins group or have administrative rights to the repository to delete process applications or toolkits from the Process Center.

There are two ways to delete Process Application and/or Toolkit from PC/DEV -

1) You may use PC console and the steps would be as follows:

  • From the Process Apps and/or Toolkits tab of the PC console, click the PA and/or TK that you want to remove.
  • Click Manage.
  • From the Manage page, click Archive Process App.
    PAremove.png
  • Click Snapshots tab (assuming you're still inside the app you want to remove)
  • Click "Delete Process Application" or "Delete Toolkit" and confirm by clicking on "Delete" 
    PAremove2.png
    IMPORTANT NOTE: it might take some time for the PA and/or TK to delete from the system, so, if you remain on the same screen don't click on delete again or any other action for this PA or TK that you're trying to delete. Give it time to do the action. Once you click on Delete depending on how much snapshot(s) in your PA it might take a while before it fully disappears from PC console.

2) The other method is to use wsadmin commands:

Common error(s) during snapshot cleanup and how to resolve them 

Unfortunately deletion of snapshot(s) is not always successful and may fail with an error, especially in Production type of environments with a lot of data. And I'm not talking about those error(s) where the specific conditions/requirements for the deletion are not met, such examples include - snapshot is active, default, snapshot has running/suspended instances, user has insufficient rights. In this section I'm going to focus on "unexpected" errors/exceptions that you may experience during your snapshot purging journey in IBM BPM.

Common Error #1:

Timeouts - 

There are two main type of timeouts you may observe when executing cleanup tasks, especially snapshot cleanup because it takes most of the time.

SOAP timeout - 

Most of the cleanup commands are executed using the wsadmin tool and SOAP connection. As a result if the command takes longer to complete than the specified SOAP timeout value you will see an error in the command line (wsadmin). Although the command continues to run until it is finished, you might see the exception java.net.SocketTimeoutException: Read timed out.

So, while it doesn't have a functional impact on the cleanup procedure if you wan to prevent this exception, set a higher value for the com.ibm.SOAP.requestTimeout property in the profile_root/properties/soap.client.props file.

default-long-transaction-timeout/default-transaction-timeout - 

 <!-- default long transaction timeout (seconds) -->
<default-long-transaction-timeout>14400</default-long-transaction-timeout>
<!-- default transaction timeout (seconds) -->
 <default-transaction-timeout>480</default-transaction-timeout>

Common error - 

The error might be different depending on the database you use and could be just a transaction timeout error without database error necessarily.

Here is an example of a transaction timeout in IBM BPM / DB2 configuration - 

[10/5/18 5:56:45:227 PDT] 0000004f TimeoutManage I WTRN0006W: Transaction 000001664445ECAB0000000124FE4730533E0FE6737797327A82A9C9BBB804194875E11B000001664445ECAB0000000124FE4730533E0FE6737797327A82A9C9BBB804194875E11B00000001 has timed out after 480 seconds.
[10/5/18 5:56:45:229 PDT] 0000004f TimeoutManage I WTRN0124I: When the timeout occurred the thread with which the transaction is, or was most recently, associated was Thread[SoapConnectorThreadPool : 0,5,main]. The stack trace of this thread when the timeout occurred was:
(stack trace goes here)

Or in case of IBM BPM running on Oracle db it could be something like - 

[1/12/18 15:10:57:977 GMT] 00000604 CommandServic I PALAdminCommands snapshotDelete Entering

[1/12/18 15:15:33:067 GMT] 00000136 ProgrammaticT W   CWLLG2103W: The retry limit of 5 was exceeded in ProgrammaticTransactionSupport.

[1/12/18 15:17:00:091 GMT] 00000604 ProgrammaticT W   CWLLG2103W: The retry limit of 5 was exceeded in ProgrammaticTransactionSupport.

[1/12/18 15:21:34:129 GMT] 00000136 ProgrammaticT W   CWLLG2103W: The retry limit of 5 was exceeded in ProgrammaticTransactionSupport.

[1/12/18 15:21:34:132 GMT] 00000136 ProgrammaticT W   CWLLG2103W: The retry limit of 5 was exceeded in ProgrammaticTransactionSupport.

[1/12/18 15:21:34:132 GMT] 00000136 wle           E   CWLLG2229E: An exception occurred in an EJB call.  Error: PreparedStatementCallback; bad SQL grammar [DELETE FROM LSW_USR_GRP_XREF WHERE GROUP_ID = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock  com.lombardisoftware.client.delegate.BusinessDelegateException: PreparedStatementCallback; bad SQL grammar [DELETE FROM LSW_USR_GRP_XREF WHERE GROUP_ID = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock 

Cause:

The error means that you're hitting a transaction timeout that is configured to be used during the snapshot deletion. This timeout is configured in IBM BPM configuration files (XML).

Depending on which version of IBM BPM you're running on there is one of the following timeouts being used during the snapshot cleanup -

default-transaction-timeout - this one is being used if you're on IBM BPM 856/IBM BPM 857 GA (no CF's) or beyond.

default-long-transaction-timeout - this one is being used if you're on IBM BPM 857 CF2016.09 or above.

It's more appropriate to use the second long timeout because it's specifically configured to be used in such operations. You don't want to change the default-transaction-timeout to a really high value permanently because it would affect other operations too, not just snapshot deletion. So, a big timeout might cause some issues where things won't be timing out for a long time. If you're on IBM BPM 856 or 857 GA then it's better to upgrade to a later version to use the long timeout setting. Otherwise you may change default transaction timeout during snapshot deletion and then change it back to its default value.

The APAR that addresses the issue of default transaction timeout being used instead of the long transaction timeout can be found here.

Common Error #2:

java.lang.Exception: java.lang.Exception: You cannot delete a snapshot that is referenced by tasks belonging to BPD instances referencing another snapshot

Cause:

It typically happens if there is a task which has been completed in a old snapshot A and then a user has migrated the instance to snapshot B. After that when you will try to delete snapshot A, this error will be thrown.
It happens because none of the closed tasks are migrated with the instance migration from old to new snapshot and this works as designed, only active tasks are migrated. Closed tasks will essentially leave on snapshot A.
You may query LSW_TASK table with a query like -

select bpd_instance_id, task_id from lsw_task where snapshot_id='XYZ' and status='32'

(replace snapshot_id with the id of the snapshot you're trying to delete).
This would give you all instances id's and task id's for those instances.

But just to be clear - it's not the instances that are causing the problem but the tasks within the instances. Closed tasks that were closed in old snapshot A and instances were migrated to snapshot B but tasks were not migrated because they were closed. Only active tasks withn the instances are migrated. So, the closed tasks on snapshot A are causing this.

Resolution:

1) Run BPMTasksCleanup command to delete completed tasks from running process instances. See above section on how to cleanup tasks.

2) Then try to delete snapshot BPMDeleteSnapshot again. It should work this time.

Common Error #3:

WASX7015E: Exception running command: exception information:
java.lang.Exception: java.lang.Exception: Snapshot [xxxxx]
cannot be deleted at this time, due to 
There are still objects referencing required snapshot artifacts.
entities preventing from deletion are:
BPDInstance.xxxxxx
BPDInstance.xxxxxx
BPDInstance.xxxxxx
BPDInstance.xxxxxx
BPDInstance.xxxxxx

Cause:

This issue might occur because of an error in the user management updates for the snapshot's participant group. As a result of the error, groups and their manager refer to different
snapshots.

Resolution:

There is not a good workaround for this issue. So, this was reported as a defect with IBM support and it's addressed in IBM BPM 8.6. CF2018.03. More information can be found in APAR JR58847 

Common Error #4:

java.lang.Exception: java.lang.Exception: 
Business Process Diagram with ID BPD.20b219a8-3ee1-43ca-b221-9262d30b33ce not found.

Cause:

The error is self-explanatory, apparently it cannot find a BPD with ID in a snapshot you're trying to delete where it's expecting that BPD should exist.

Resolution:

Run the following queries to determine the snapshot id that we might need to delete first:

select name, version_id from lsw_bpd 
where bpd_id='20b219a8-3ee1-43ca-b221-9262d30b33ce'
//this gives us version_id
select branch_id from prddbusr.lsw_po_versions 
where po_version_id='0b018ecd-0713-4f2b-a2fa-99700da5c463'
//use the version_id we got from a previous query

select base_snapshot_id,name from prddbusr.lsw_branch 
where branch_id='481b66ce-66ed-4cef-8e8f-a312ffd4ccc3'
//use the branch_id we got from a previous query

At this stage you should get a snapshot id. Try to delete it using BPMDeleteSnapshot command. If it works then you should now be able to delete the original snapshot that was throwing this error. If it does not work and throws another error then it's most likely "Common Error #4", so, proceed to those steps to resolve it and then come back to deletion of this snapshot.

Common Error #5:

java.lang.Exception: java.lang.Exception: StatementCallback; SQL [
                DELETE FROM LSW_BPD
                WHERE VERSION_ID NOT IN
                        (SELECT PO_VERSION_ID
                         FROM LSW_PO_VERSIONS
                         WHERE PO_TYPE = 25)
            ]; 
DB2 SQL Error: SQLCODE=-532, SQLSTATE=23504, SQLERRMC=PRDDBUSR.LSW_BPD_INSTANCE.LSWC_BPD_INST_FK1, DRIVER=4.18.60; 
nested exception is com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: 
DB2 SQL Error: SQLCODE=-532, SQLSTATE=23504, SQLERRMC=PRDDBUSR.LSW_BPD_INSTANCE.LSWC_BPD_INST_FK1, DRIVER=4.18.60

Cause:

This happens due to some conflicting instances that prevent snapshot deletion.

This may also happen in Process Center during un-named snapshot deletion.

Resolution:

1) Use the following query to determine what instances are blocking the deletion of the snapshot -

select bpd_instance_id, snapshot_id from prddbusr.lsw_bpd_instance 
where cached_bpd_version_id in 
(select po_version_id from prddbusr.lsw_po_versions 
where po_type = 25 and branch_id = (select branch_id 
from prddbusr.lsw_snapshot snap where snap.acronym = 'MySnap1.1'))"

//replace  'MySnap1.1' in snap.acronym with the value of the acronym of the snapshot you're //trying to delete

2) You will get a list of snapshot id's and instance id's.

3) You may now want to first delete the snapshot(s) based on the snapshot id's you got. You can query the LSW_SNAPSHOT table to get the acronyms based on the snapshot id's you got.

4) If the removal of the snapshots was successful then you can attempt to delete the original snapshot that was giving you an error.

5) If the removal of the snapshots is failing with the same error then try to cleanup instances first using the corresponding wsadmin command as explained in "Process Instances Cleanup" section of this article.

If this error happens when you try to remove un-named snapshot(s) in Process center then you need to determine what instances are present on the un-named snapshot in question. 

Any older instances, which may be related to one of the unnamed snapshots, need to be clean up or it can prevent the cleanup from completing.

With the current version of the process app or toolkit open, the Process Designer's Inspector tab can be used to view the instances which are associated with the unnamed
snapshots (TIP). Alternatively you may also query the database similar to how it's explained above but instance of snapshot acronym use snapshot id of the un-named snapshot. Un-named snapshot does not have snapshot acronym associated with it.

Common error #6:

Symptoms: 

You're running on IBM BPM 856 (GA or CF1/CF2) or earlier version and you experience slowness deleting snapshots. Even with snapshots that does not have a lot of completed instances on it. It might take up to one hour to delete a single snapshot which makes this procedure almost unusable.

You observe hung threads in the system server logs suggestion there is a lock contention on the database side -

[1/30/18 19:13:35:724 GMT] 00000046 ThreadMonitor W   WSVR0605W: Thread "SoapConnectorThreadPool : 0" (00000150) 
has been active for 748513 milliseconds and may be hung.  There is/are 1 thread(s) in total in the server that may be hung.
...
...
    at com.lombardisoftware.server.ejb.repositoryservices.BranchDeleteSupport.deleteTrack(BranchDeleteSupport.java:52)
    at com.lombardisoftware.server.ejb.repositoryservices.BranchSupport.deleteTrack(BranchSupport.java:67)
    at com.lombardisoftware.server.ejb.repositoryservices.SnapshotDeleteSupport.deleteSnapshot(SnapshotDeleteSupport.java:949)
    at com.lombardisoftware.server.ejb.repositoryservices.SnapshotDeleteSupport.deleteSnapshot(SnapshotDeleteSupport.java:1137)
    at com.lombardisoftware.server.ejb.repositoryservices.RepositoryServicesCore.deleteSnapshot(RepositoryServicesCore.java:525)
...

Cause:

The speed of a snapshot deletion is governed by how many objects are in your snapshot and the complexity of those. The speed of deleting a row from the database would also be a big factor as the snapshot delete for large snapshots could require the removal of over 100,000 rows. Since insert and delete times are usually similar for a database, snapshot delete will often take a similar amount of time as an import for the snapshot.

Resolution:

On later versions of IBM BPM (starting IBM BPM 857 CF2016.12) the snapshot delete command makes use of SQL which includes a lookup based on object type:
SELECT PO_VERSION_ID FROM LSW_PO_VERSIONS WHERE PO_TYPE = 2025

In some cases this could benefit from an index for PO_TYPE and PO_VERSION_ID. Although for large snapshots this may only be a small improvement in the overall time.

Your DBA can analyze the performance of the queries and DB resource usage during snapshot delete to see if there are improvements that can be made from DB tuning perspective.

If you're running on IBM BPM 856 CF2 then open a PMR with IBM requesting a fix for this APAR.

You may notice that it's included in IBM BPM starting 857 CF2016.12 but there is a version of this APAR available for IBM BPM 856 CF2 but it's not published, so, should be requested directly via IBM Support. This particular fix has improved timing for snapshot deletion in IBM BPM 856 CF2 from 1.5 hours to 30 minutes or so.

Common error #7:

[6/28/18 2:40:18:026 EDT] 000003a5 BPMDeleteSnap I   BPMDeleteSnapshot failed due to: Snapshot cannot be deleted at this time due to dependents on the current snapshot. Please use BPMShowSnapshot command to get detail information.
java.lang.Exception: Snapshot cannot be deleted at this time due to dependents on the current snapshot. Please use BPMShowSnapshot command to get detail information.

Resolution:

Most likely this error occurs when you're trying to remove one of the Toolkit snapshots. Be careful when removing "active" toolkit snapshot(s). This message basically suggests that the snapshot of the toolkit cannot be removed because it's dependent on one of the process applications making it active. Starting BPM 857 there is a single command that returns a list of toolkit snapshots that are not referenced by other process apps or toolkits. You can use the list to delete the orphaned snapshots on your server (BPMShowOrphanedToolkits).

Common error #8:

Symptoms:

You're running a snapshot delete and it fails with weird errors that you have not observed previously.

These specific error(s) are not that common but if you haven't cleaned up your IBM BPM for a while and doing so the first time then you might potentially run into these - 

[10/3/18 8:22:20:521 PDT] 00000381 wle E CWLLG2229E: An exception occurred in an EJB call. 
Error: Business Process Diagram with ID BPD.a81e7e64-f040-40ab-b759-33cb56037e03 not found.
 com.lombardisoftware.core.TeamWorksException: 
Business Process Diagram with ID BPD.a81e7e64-f040-40ab-b759-33cb56037e03 not found.
 at com.lombardisoftware.server.ejb.persistence.CommonDAO.assertNotNull(CommonDAO.java:65)
 at com.lombardisoftware.server.ejb.persistence.CommonDAO.assertMapNotNull(CommonDAO.java:45)
 at com.lombardisoftware.server.ejb.persistence.dao.VersionedPODAO.findByPrimaryKey(VersionedPODAO.java:398)
[10/9/18 5:30:35:314 PDT] 00000147 BPMDeleteSnap I   BPMDeleteSnapshot failed due to: 
CWTDS0000E: An unexpected failure occurred. Details: 'FNRCE0051: E_OBJECT_NOT_FOUND: 
The requested item was not found. Object identity: classId=IBM_BPM_Document&objectId={60BA485A-0000-C210-A110-B1F295F7E699}&objectStore={2FE7EA20-B064-4F4F-87CC-ABC11953748C}.  
Class name: IBM_BPM_Document.'

Explanation: An exception was thrown.

Action: Check the server log files.

java.lang.Exception: CWTDS0000E: An unexpected failure occurred. 
Details: 'FNRCE0051: E_OBJECT_NOT_FOUND: 
The requested item was not found. 
Object identity: classId=IBM_BPM_Document&objectId={60BA485A-0000-C210-A110-B1F295F7E699}&objectStore={2FE7EA20-B064-4F4F-87CC-ABC11953748C}.  
Class name: IBM_BPM_Document.'

Explanation: An exception was thrown.

Cause:

The first error above suggests that BPM was trying to delete an instance of a BPD or the BPD itself (during snapshot cleanup) and it cannot find the BPD in question in the database.

The second error suggests that BPM was trying to delete a document from internal BPM document store apparently associated with an instance that it was trying to delete and it cannot find this document in the database.

Resolution:

There is no good resolution to this because it's likely that the data that snapshot deletion command is trying to remove no longer exists in IBM BPM database for whatever reason.

There is a workaround you may use by cleaning up all the associated with the snapshot in question instances first. If you try to use wsadmin instances deletion command (as instructed above in this article) it might fail too with the same error(s). So, instead, you may use LSW_BPD_INSTANCE_DELETE stored procedure that can be found in IBM BPM database. Yes, it's not advised to use this procedure in latest version of IBM BPM but it's only because it's not getting rid of the associated documents in IBM BPM internal document store but that's exactly the problem here. The doc is no longer there, it's not in the database. So, it should be safe to remove such an instance(s) using the stored procedure and then retry the snapshot deletion again.

Common error #9:

Symptoms:

You're trying to run BPMProcessInstancesPurge with the criteria - "completed before or after date" and you know that it's supposed to purge an X amount of instances (because you did check it before running the script) but at the it doesn't remove a single instance. 

Cause:

BPMProcessInstancesPurge in 85x/86 relies on "CLOSE_DATETIME" column when used with -endedBeforeLocal parameter in wsadmin command. The "CLOSE_DATETIME" column is new in IBM BPM 85x/86, it was not there in BPM 801 or 751. Hence, if you have migrated from an earlier version of IBM BPM (75x or 80x) all the instances created prior migration have no value set in this column and that is why the BPMProcessInstancesPurge command finds 0 instances to process.

Here is a simpler query to verify it -

db2 "select bpd_instance_id, last_modified_datetime, close_datetime from lsw_bpd_instance where execution_status = 2 order by last_modified_datetime with ur"
Only instances dated 2018-09-03 and after have "CLOSE_DATETIME" column populated.

Resolution:

The easiest way to resolve this is to manually populate "CLOSE_DATETIME" column for older instances with the same values as in "LAST_MODIFIED_DATETIME" column minus ~10 milliseconds for each instance, like it does for newer instances created in IBM BPM 85x/86 -

BPD_INSTANCE_ID LAST_MODIFIED_DATETIME CLOSE_DATETIME
--------------- -------------------------- --------------------------
28661. 2018-10-18-01.00.00.383000 2018-10-18-01.00.00.376000
28518. 2018-10-18-14.23.28.140000 2018-10-18-14.23.28.132000
28662. 2018-10-18-16.56.24.896000 2018-10-18-16.56.24.881000

DISCLAIMER: Direct database manipulations are not supported by IBM BPM support, so, make sure you have a reliable database backup before you perform any such manipulations. Feel free to file a defect report with IBM on this also to get it properly addressed.  

Performance Data Warehouse (PDW) cleanup

  • There is an out of the box prune feature available for Performance Data Warehouse (PDW) as part of a command line tool called "perfDWTool". It allows purging of data that is beyond a given age in days.
    IMPORTANT NOTE: There are also settings in 100Custom.xml file for PDW that affect how this prune feature works.
    prune-batch-size: The number of records to be deleted in a single prune operation. The default value is 1000.
    prune-operation-time-box: The amount of time the operation will run, in seconds. The default is 10800 or 3 hours.
    prune-operation-time-box-retry: The number of times the operation will be tried. The default is 4 such that it will retry 3 times.
  • Another prune option that does a similar type of cleanup is using the REST API and/or PA console UI interface (it calls the same REST API). You can read more about that option here.

  • You might often end up in a situation in the PDW database where you have lots of data generated from enabled auto-tracking and especially if that BPD where it was enabled ran into a loop generating thousands of instances. So, in this case one of the tables that would get most of the records would be "LSW_OPTIMIZER_DATA". This table gets a lot of records from all the BPDs that have auto-tracking enabled even though the Optimizer itself is a deprecated component in IBM BPM latest versions. The good news is that this table does not have any relations with other tables, so, truncating LSW_OPTIMIZER_DATA should be completely safe option. 
  • Finally, it's worth mentioning an option of full refresh of PDW database. You may often end up in situations in PDW database where you have lots of data generated from auto-tracking and at the same time you know that you're not using much of manually created tracking data / tracking groups either. Yet, PDW database is occupying a lot of space and basically stores un-needed data. So, you want to start from the beginning, disable auto-tracking, create manual tracking groups/points, etc. The question is - how do you start with fresh database in PDW only? The answer is well documented here in dwAnswers post.

Automated cleanup/purge by schedule

  • From BP3 Labs experience it's best to use scripting for all kinds of cleanup processes you're going to set up in your IBM BPM environments. All of the purging commands in IBM BPM 85x/86 are available via wsadmin and REST API calls. BP3 has developed a number of scripts to help you to fully automate this process. You may find them in our "BP3 Labs DevOps for IBM BPM" article.
  • It can be a custom UI that would be calling REST APIs behind the scenes (similar to what PA console -> Health Management offers) or it can be a cron job that runs every night on weekends calling jython scripts (wsadmin) that in turn checks what can be removed based on your conditions.
  • All the scripts that can be found in above mentioned article with scripts are taking into consideration the exception handling, separate custom logging capabilities. So, that way you may fully automate the process and review the logging for any failures that occurred during the process of scripts execution(s).
  • Everything written in this article has been fully scripted and again, you can find it all here.

Conclusions

The main takeaways from this article:

  • start thinking about IBM BPM cleanup process sooner vs later
  • make sure your whole cleanup process is fully automated
  • work with business to define cleanup strategy based on audit requirements
  • review most common errors section in details while working on automated cleanup scripts to make sure you catch all those exceptions because for those cases you might need to use the resolution section to follow the right cleanup path (manually)
  • don't forget about PDW database cleanup because it's often overlooked
  • remember that there are various type of cleanup commands available in IBM BPM, so, if you cannot remove some specific data you still have choices as noted in this article in common errors section
  • this article along with our automated "BP3 Labs DevOps for IBM BPM" scripts summarizes all available cleanup options for IBM BPM 86 and IBM BAW 19x/20x/21x along with some hints/tips

If you have any questions/comments/concerns please leave your feedback here in comments section or contact BP3 Labs at support@bp3.zendesk.com

Comments

2 comments

Sort by Date Votes

Please sign in to leave a comment.


Cookies List