Topic
  • 14 replies
  • Latest Post - ‏2013-03-22T14:51:09Z by SystemAdmin
JohnJKKim
JohnJKKim
41 Posts

Pinned topic Deleting Process Instances

‏2011-04-01T04:40:36Z |
Hello Lombardians

So when you are developing, you create a lot of process instances and it is not hard to create over 100+ instances in short time (whether they are active, completed, failed or terminated.)

So I clean up all instances time to time. I use AE to do this, but it is quite slow to delete all instances.
I select all instances and click X button to delete but it takes good amount of time to get rid of all instances.

Is there a faster way? some kind of command I can run from server?
Also, can you delete all scheduled events too?

Thanks,
John
Updated on 2013-03-22T14:51:09Z at 2013-03-22T14:51:09Z by SystemAdmin
  • kolban
    kolban
    3316 Posts

    Re: Deleting Process Instances

    ‏2011-04-01T14:43:29Z  
    Hi John,
    Don't know about scheduled events, but here are some notes on cleaning finished instances. Hope this is useful.

    Neil

    Cleaning completed processes

    When a BPD instance has completed, the record of that BPD instance is not deleted from the databases. As such, over time, the databases will become full of historical information. WLE provides a stored procedure called LSW_BPD_INSTANCE_DELETE that clears historic data.
    The signature of this procedure looks as follows:
    CREATE PROCEDURE LSW_BPD_INSTANCE_DELETE(IN bpdInstanceId DECIMAL(12,0))
    Here is a Java application that will delete such instances:

    
    
    
    package com.kolban.wle;   
    
    import java.sql.CallableStatement; 
    
    import java.sql.Connection; 
    
    import java.sql.DriverManager; 
    
    import java.sql.PreparedStatement; 
    
    import java.sql.ResultSet;   
    
    public 
    
    class CleanBPD 
    { 
    
    public 
    
    static 
    
    void main(String[] args) 
    { CleanBPD cleanBPD = 
    
    new CleanBPD(); cleanBPD.run(); 
    }   
    
    private 
    
    void run() 
    { System.out.println(
    "Running"); 
    
    try 
    { Class.forName(
    "com.ibm.db2.jcc.DB2Driver").newInstance(); String url = 
    "jdbc:db2://localhost:50000/PROCDB"; String userid = 
    "db2admin"; String password = 
    "db2admin"; Connection con = DriverManager.getConnection(url, userid, password); PreparedStatement queryStmt = con.prepareStatement(
    "SELECT BPD_INSTANCE_ID FROM LSW_BPD_INSTANCE WHERE EXECUTION_STATUS = 2"); CallableStatement deleteStmt = con.prepareCall(
    "{CALL LSW_BPD_INSTANCE_DELETE(?)}"); ResultSet rs = queryStmt.executeQuery(); 
    
    while(rs.next()) 
    { 
    
    int bpdId = rs.getInt(
    "BPD_INSTANCE_ID"); System.out.println(
    "Deleting BPD: ID = " + bpdId); deleteStmt.setInt(1, bpdId); deleteStmt.execute(); 
    } 
    } 
    
    catch (Exception e) 
    { e.printStackTrace(); 
    } 
    } 
    }
    
  • JohnT.Reynolds
    JohnT.Reynolds
    233 Posts

    Re: Deleting Process Instances

    ‏2011-04-01T15:33:57Z  
    Note that the decision "when" to delete a completed instance is not a technical decision... It's a business decision.

    Of course you have to guide the business to make sure that they take into account the physical constraints of their database and remind them that "saved searches" will start to run slower, but the data from the completed process may be of business interest long after that instance has completed.

    "Long" is in the eye of the beholder... If there's a real need for information from an instance to be available "for years", then you want to copy the relevant data to a "real" system of record.

    Obviously you could archive important information as soon as a process completes, and then delete the instance, but from a practical standpoint that might complicate your implementation. I often create status dashboards for the process owners that merge information from completed instances and active instances. It's just "simpler" to build these if I am getting the data from the same source (using TWSearch).

    Just some things to think about.
  • JohnJKKim
    JohnJKKim
    41 Posts

    Re: Deleting Process Instances

    ‏2011-04-04T06:22:51Z  
    Note that the decision "when" to delete a completed instance is not a technical decision... It's a business decision.

    Of course you have to guide the business to make sure that they take into account the physical constraints of their database and remind them that "saved searches" will start to run slower, but the data from the completed process may be of business interest long after that instance has completed.

    "Long" is in the eye of the beholder... If there's a real need for information from an instance to be available "for years", then you want to copy the relevant data to a "real" system of record.

    Obviously you could archive important information as soon as a process completes, and then delete the instance, but from a practical standpoint that might complicate your implementation. I often create status dashboards for the process owners that merge information from completed instances and active instances. It's just "simpler" to build these if I am getting the data from the same source (using TWSearch).

    Just some things to think about.
    Thanks guys for your comments.

    I have a problem now.

    I tried to delete an instance from Inspector.
    If I click one process instance and click "Delete the selected BPD instances" button, it takes ages (thus does this in background) and it is not deleted when the background job is finished.

    Any ideas?

    John
  • SystemAdmin
    SystemAdmin
    7615 Posts

    Re: Deleting Process Instances

    ‏2011-04-04T13:39:02Z  
    • JohnJKKim
    • ‏2011-04-04T06:22:51Z
    Thanks guys for your comments.

    I have a problem now.

    I tried to delete an instance from Inspector.
    If I click one process instance and click "Delete the selected BPD instances" button, it takes ages (thus does this in background) and it is not deleted when the background job is finished.

    Any ideas?

    John
    No, I've never had anything like that happen.

    I'd try to use a database tool to run the stored procedure directly. That's what the AE UI is doing behind the scenes. If running the stored proc gives you an error then there is some problem in the database and you should have a concrete error message. If not, you'll know the error is in the connectivity.

    Have you tried rebooting the AE and/or ProcessCenter? Anything interesting in the ProcessCenter logs?
  • SystemAdmin
    SystemAdmin
    7615 Posts

    Re: Deleting Process Instances

    ‏2012-12-07T09:52:25Z  
    I was searching for LSW_BPD_INSTANCE_DELETE procedure in DB. Unfortunatly, I did not find it.

    Where can I get that Store Proc (LSW_BPD_INSTANCE_DELETE)?
    Is LSW_BPD_INSTANCE_DELETE procedure deletes the completed process instances only?
    Is LSW_BPD_INSTANCE_DELETE performing any activity like archival of process instances, which are going to delete?
  • kolban
    kolban
    3316 Posts

    Re: Deleting Process Instances

    ‏2012-12-07T23:58:44Z  
    I was searching for LSW_BPD_INSTANCE_DELETE procedure in DB. Unfortunatly, I did not find it.

    Where can I get that Store Proc (LSW_BPD_INSTANCE_DELETE)?
    Is LSW_BPD_INSTANCE_DELETE procedure deletes the completed process instances only?
    Is LSW_BPD_INSTANCE_DELETE performing any activity like archival of process instances, which are going to delete?
    It looks like this was changed in v8 to be:

    http://pic.dhe.ibm.com/infocenter/dmndhelp/v8r0m1/topic/com.ibm.wbpm.ref.doc/topics/rref_BPMProcessInstancesCleanup.html

    Neil
  • SystemAdmin
    SystemAdmin
    7615 Posts

    Re: Deleting Process Instances

    ‏2012-12-10T03:32:24Z  
    • kolban
    • ‏2012-12-07T23:58:44Z
    Thank you Neil.

    What would be the solution 7.2 version?
  • kolban
    kolban
    3316 Posts

    Re: Deleting Process Instances

    ‏2012-12-10T03:56:35Z  
    Thank you Neil.

    What would be the solution 7.2 version?
    Unfortunately I don't know. I do know that in 7.5 it was the stored procedure and it appears that in 8 it is the script ... so who knows what it was in 7.2 :-)

    Neil
  • SystemAdmin
    SystemAdmin
    7615 Posts

    Re: Deleting Process Instances

    ‏2013-02-11T23:01:16Z  
    Thank you Neil.

    What would be the solution 7.2 version?
    for 7.2 it is the same stored proc.
  • edling
    edling
    261 Posts

    Re: Deleting Process Instances

    ‏2013-03-06T13:38:05Z  
    How do I delete instances in Process Center for 8.0.1? It seems that not only does the BPMProcessInstancesCleanup command not accept Tip as -containerSnapShotAcronym, but even worse it only works in Process Server.

    (I have 75 000 instances in the Tip that I need to delete after a UCA mishap.)
  • vlit
    vlit
    926 Posts

    Re: Deleting Process Instances

    ‏2013-03-06T13:55:52Z  
    • edling
    • ‏2013-03-06T13:38:05Z
    How do I delete instances in Process Center for 8.0.1? It seems that not only does the BPMProcessInstancesCleanup command not accept Tip as -containerSnapShotAcronym, but even worse it only works in Process Server.

    (I have 75 000 instances in the Tip that I need to delete after a UCA mishap.)
    Hi edling

    What's a problem to use LSW_BPD_INSTANCE_DELETE?
    It works in 8, I remember.

    Vladlen | http://bpmadmin.blogspot.com
  • edling
    edling
    261 Posts

    Re: Deleting Process Instances

    ‏2013-03-06T16:41:02Z  
    • vlit
    • ‏2013-03-06T13:55:52Z
    Hi edling

    What's a problem to use LSW_BPD_INSTANCE_DELETE?
    It works in 8, I remember.

    Vladlen | http://bpmadmin.blogspot.com
    No idea, I just noted that Kolban wrote that the script was the way to go in 8.0.

    I need to delete failed, terminated and completed instances of a certain process in a process application. There are instances of another application I don't want to delete. With the script I would have used timeStamps for damage control. The instances were all created during 1-2 days. As this is a process center, they instances belong to the tip. I suppose I could ask my technician to look into the LSW_BPD_INSTANCE_DELETE, as I don't have any access to the database, but I would benefit from being able to supply him with instructions, including what arguments/parameters can be used to select instances for deletion.
  • edling
    edling
    261 Posts

    Re: Deleting Process Instances

    ‏2013-03-06T16:42:11Z  
    • edling
    • ‏2013-03-06T16:41:02Z
    No idea, I just noted that Kolban wrote that the script was the way to go in 8.0.

    I need to delete failed, terminated and completed instances of a certain process in a process application. There are instances of another application I don't want to delete. With the script I would have used timeStamps for damage control. The instances were all created during 1-2 days. As this is a process center, they instances belong to the tip. I suppose I could ask my technician to look into the LSW_BPD_INSTANCE_DELETE, as I don't have any access to the database, but I would benefit from being able to supply him with instructions, including what arguments/parameters can be used to select instances for deletion.
    Correction:
    There are instances of another process in the same application that I don't want to delete.
  • SystemAdmin
    SystemAdmin
    7615 Posts

    Re: Deleting Process Instances

    ‏2013-03-22T14:51:09Z  
    • edling
    • ‏2013-03-06T16:42:11Z
    Correction:
    There are instances of another process in the same application that I don't want to delete.
    FYI, if you are working in the process designer, then you always have access to the DB. The default jndi lookup for the database connectors in the system data toolkit is the process center/server DB. So you could easily write your own service to allow you to query the LSW_BPD_INSTANCE table (or use the search api) and then issue the Stored procedure call to remove the instances you want.

    It does not appear that the TWProcessInstance object in the JS API has yet obtained a "delete" method.

    Andrew Paier | Director of Special Operations | BP3 Global, Inc. www.bp-3.com