Purging data in archive tables

1
0
-1

Hello,

In my environment, I'm using the Tomcat bundle installation of Bonitasoft Community edition v 6.3.2. Database is MySQL. I've noticed that the archived tables are large in size. Is there a process that purges data after a certain retention period?

3 answers

1
+1
-1

Hi Michele,

if you want to purge your archive database, in 6.3 the only way is to delete your archived process instances.

There is no readily available APIs in 6.3 to delete archived process instances. The only method availabe is

deleteArchivedProcessInstances (processDefinitionId,startIndex,EndIndex)

This method does not allow deleting specific process instances. Also be aware that for each process instance there are 3 states and the archive table stores a line per state and not a line per instance, which could be confusing.

I have attached the source code (DeleteOldProcs.zip) from a .jar file which will show you how you can use the command API to achieve this.

You will create a process "DeleteArchivedProc" with one service step. This first step provides a list of all the archived process instances. It allows you to delete one process at a time. You can extend this functionality to your needs.
The process will use a connector that registers the jar file that includes the command to delete the process instances. So add this connector.

Before running the process you need to do the following,

  • Create a "DeleteOldProcs.jar" file from DeleteOldProcs.zip attached, which includes the command for process deletion
  • Place the attached "DeleteOldProcs.jar" into the directory, or a location accessible from the server (you can specify the location of the .jar file when you run the process)

The command uses the engine's internal API method to delete the process instances that are passed to it.

I hope this helps

Delphine

Comments

Submitted by Michele.DuBose on Fri, 12/11/2020 - 20:25

Hi delphine.coille,

I'm confused about your steps. Should I create a "DeleteOldProcs.jar", copy the contents from the DeleteOldProcs.zip and paste the contents in the "DeleteOldProcs.jar" file? What's the best method for creating the "DeleteOldProcs.jar" file? Should the "DeleteOldProcs.jar" file be placed in the bonita home directory? If so, where? In the zip file, location: deleteoldprocs.zip\DeleteOldProcs\src\org\bonitasoft\support\command, I've noticed two "DeleteArchivedProcessInstance.java" files. Are these the commands that will be ran? How do you execute the commands?

1
0
-1

Hello Michel,

+1 with the tool referenced by Emmanuel.

You have a second tool available in the community, the Truckmilk page. Inside, you will find multiple jobs to purge or archive cases based on process, delay. You have also a job to move archived cases to an external database.

Visit https://community.bonitasoft.com/project/pagetruckmilk-execute-jobs-inte...

You don't need to implement any cron with this tool because it uses the Bonita Scheduler. You can limit its execution to a time slot (example, between 2:00 to 4:00 pm every night), and it can handle a large number of cases to delete (with the Bonita-purge-tool., you may need to change the database parameter: this tool purge all cases in one transaction, which is very efficient but you may need a large amount of swap in your database). The Truckmilk job uses the Bonita API and does that per page of 50 cases to be robust.

You will see that tool has a lot of different plugins to do different actions.

If you need an example of this tool, have a look at the PY Tutorial https://www.youtube.com/watch?v=O6Ef7kEe0Ps

Best

Comments

Submitted by Michele.DuBose on Fri, 12/11/2020 - 20:14

Hi Pierre-yves,

Can Truckmilk run for Bonita v6.3.2? Can Bonita-purge-tool run for Bonita v6.3.2?

Submitted by Pierre-yves Monnet on Fri, 12/11/2020 - 22:16

Hym, I think you can try the Truckmilk in 6.3.2, I have no idea when the Groovy custom page came. Could you have a look on the Resource page (as an administrator) and see if you can add it? This page use the API, so it's relatively standard (unfortunately, the Bonita API changes time to time).

But Delphine is right: TruckMilk use the API and maybe the deletion API does not exist in 6.3.

The Bonita-purge-tool will definitively not run. It play some direct SQL request, and in 6.3, table "Contract" does not exist (it appears in 7.0). So, request will failed.

You should consider to upgrade. The point is the Form V6 are not available after Bonita 7.7: you can still migrate up to this version.

Last but not least, you still can delete by your own the data in the ARCH_ tables. Each table has a RootCaseId, so you can purge using this information.

Submitted by Michele.DuBose on Fri, 12/11/2020 - 22:20

Thanks for the information. I do not see a Resource page as an administrator, so I'm assuming this is not available in v6.3.2. As far as deleting the ARCH_tables on my own, is rootcaseid the column name? I assume all the ARCH_tables are linked together so I'll have to know the best method on deleting the records in each table.

Submitted by Pierre-yves Monnet on Thu, 12/17/2020 - 02:46

hello,

Yes the page was introduced in 6.4

Yes, rootcaseid or something like this is a column name.

The archived cases does not have any reference key, or link between table. So, you have to find the correct query on each table.

You can do the same way as the purge tool : delete the root case, then check and analyze request in the purge tool that Emmanuel told. You have the query. Principle of this tool is to delete information in the main archive table (arch_process_instance) and then purge all another table based on something like " I delete all archive activities where the root case does not exist anymore in the archive_process_instance". This is a good starter.

Best,

Submitted by Michele.DuBose on Thu, 12/17/2020 - 08:44

Thank you

1
0
-1

Hi Michele,

In addition to Delphine's answer, we recently, at Bonitasoft, released a new tool that does exactly what you aim at:

See https://community.bonitasoft.com/blog/need-clean-your-archived-process-instances-use-bonita-purge-tool for details.

The only thing left to do is to schedule the running of the tool with the appropriate parameters: processes for which to purge the data, date before which to delete the data.

Hope you like this tool, and don't hesitate to give us feedbacks on it !

Regards,

Emmanuel

Comments

Submitted by Michele.DuBose on Wed, 12/02/2020 - 18:58

Hi Emmanuel,

When I download the tool and open the README.md file, it mentions that the tool can run on Bonita versions greater than or equal to 7.7.0. Can the tool run on Bonitasoft Community edition v 6.3.2? Also, in the README.md file, it states when you run the tool, you'll be given parameters to insert, one being the PROCESS_DEFINITION_ID. Where can I find the value for PROCESS_DEFINITION_ID?

Submitted by emmanuel.duchas... on Mon, 12/14/2020 - 12:21

Hi,

Purge-tool hasn't been tested on earlier versions than 7.7.0. I may work but I cannot guaranty that.

v 6.3.2 is already pretty old and not supported anymore. Considering upgrading would be great.

And to answer your question, if you don't provide a PROCESS_DEFINITION_ID, the tool will list all available processes (name - version - ID) and then stop, so you can find it like that and re-run the tool.

HTH

Submitted by Michele.DuBose on Thu, 12/17/2020 - 01:53

Thank you.

Notifications