What is the Relations between the tables where we are storing camunda meta data

Hi,

I am trying to move all the completed/cancelled Records of camunda meta data into archiving tables including the child records and later when the data ages out, thinking to drop the old data after getting confirmation from business. Is there any other way to reduce the DB space without drooping the data

Also Can you someone help me in understanding the Relations ship between the tables where we are storing the camunda metadata.

Some of the tables:
ACT_RU_EXECUTION
ACT_HI_PROCINST
ACT_RE_PROCDEF
ACT_HI_INCIDENT

There is nice documentation with pictures
https://docs.camunda.org/manual/7.15/user-guide/process-engine/database/database-schema/

Hello @Thanuja_Vadlamudi!

Manually deleting records is quite dangerous if you don’t know the schema structure.
I recommend to read our docs regarding history cleanup.
https://docs.camunda.org/manual/7.16/reference/rest/history/history-cleanup/post-history-cleanup/
https://docs.camunda.org/manual/7.16/user-guide/process-engine/history/#history-cleanup

Best, McAlm

1 Like

Thanks @McAlm for the sharing the above info.

I don’t see the rest call in the version which we are using(Camunda BPM V7.11.0 - Community Version).

/history/cleanup
/process-definition/{id}/history-time-to-live.

Could you please let me know what is method It is internally calling and Is it available in V7.11.0?

HELLO @Thanuja_Vadlamudi: What you’re looking for is this here:
https://docs.camunda.org/manual/7.11/reference/rest/history/history-cleanup/post-history-cleanup/

To see what’s behind this service you can have a look into the sources yourself. (That’s the beauty of OpenSource :wink: )

Best, McAlm

1 Like

Thanks a lot for the above info. I had gone through the links and found below info.

process engine configuration for the history cleanup:
In case you want to provide an engine-wide default TTL for all process, decision and case definitions, use the “historyTimeToLive” attribute of the process engine configuration. This value is applied as the default whenever new definitions without TTL are deployed. Note that it therefore does not change the TTL of already deployed definitions. Use the API method given above to change TTL in this case.

<!-- default setting for all batch operations -->
<property name="batchOperationHistoryTimeToLive">P5D</property>
<property name="historyCleanupStrategy">removalTimeBased</property>
<property name="historyRemovalTimeStrategy">end</property>


The above process will work for new definitions without TTL are deployed. If I want to define the TTL for all the existing definitions/process instances, then the above process (process engine configuration for the history cleanup) will not work right? In order to set the TTL for all the existing process instances, what is the process(Do we need to use Java Rest API to set the TTL for all the existing definitions)?

Hi @Thanuja_Vadlamudi,

if you have the enterprise version available, you can use a Batch operation in Cockpit to set the removal time: Batch operation | docs.camunda.org.

It calls this REST endpoint, that is available in the Community version as well: Set Removal Time Async (POST) | docs.camunda.org

Hope this helps, Ingo

Hi Ingo,

Thanks for the info. can this be achieved using TTL process ( process engine configuration for the history cleanup:) means that cleaning up the existing process definitions and new process definitions (removal time = end time + 18 months) will not be done by defining the ```
historyCleanupJobLogTimeToLive attribute and few attributes in process engine configuration?
History and Audit Event Log | docs.camunda.org (Section : )

Can you please suggest me which is the best way to cleanup existing process instances and new process instances having the criteria removal time = end time + 18 months.

Regards,
Thanuja.

Hi @Thanuja_Vadlamudi,

after setting the HistoryTimeToLive in the process diagram, it will be applied only to process instances started after the change.

All older process instances keep their removalTime unset (which is null).

To set the removal time, you can use the batch operation that I explained above.

Here are more details about setting the removal time: Batch operations | docs.camunda.org

Hope this helps, Ingo

It helps a lot. Thank you very much for the Reply.

One last Question - It was setting the removal time for the completed Records which are there in history table using Rest API [Set Removal Time Async (POST) | docs.camunda.org ] since we are using Community version.

And for the new process instances, It will be set after changing process engine configuration.

what about the running process instances which are in ACT_RU_EXECUTION Table. Do we need to continue executing the Rest call until all running ones moved to history? Or is there possible to set the removal time for all existing ones(Running and Completed) in one go?

Hi @Thanuja_Vadlamudi,

each record in the ACT_RU_EXECUTION table has a corresponding record in one of the ACT_HI tables. They are created within the commit of the transaction, not only after the process instance is ended.

Hope this helps, Ingo

Hi Ingo,

Ok. will it set Removal time for the running and completed process instances once they are completed using rest call in one go when I use calculated time attribute as true(End Time + TTL )

Hi,

I had set below configurations in the code.
config.setHistoryCleanupStrategy(“removalTimeBased”);
config.setHistoryRemovalTimeStrategy(“end”);
config.setBatchOperationHistoryTimeToLive(“P548D”);

And calling the rest service /history/process-instance/set-removal-time with below request.
{
“clearedRemovalTime”: true,
“hierarchical”: true,
“historicProcessInstanceQuery”: {
“unfinished”: true
}
}

Below is the code


HistoricProcessInstanceQuery query = 
				this.historyService.createHistoricProcessInstanceQuery();

Batch batch = historyService.setRemovalTimeToHistoricProcessInstances()
  //.absoluteRemovalTime(new Date()) // sets an absolute removal time
   .clearedRemovalTime()        // resets the removal time to null
   //.calculatedRemovalTime()     // calculation based on the engine's configuration
  .byQuery(query)
  .byIds("693206dd-11e9-b7cb-be5e0f7575b7", "...")
   // .hierarchical()              // sets a removal time across the hierarchy
  .executeAsync();

It is working for Absolute Time and not working for Calculated Time. It should set the removal time as end time + TTL. Can you please help me to resolve the issue.

Regards,
Thanuja.

Can someone help me to resolve the above issue please.

Regards,
Thanuja.

HI Ingo,

Can you please help me to resolve the above issue.

Regards,
Thanuja.

Hi @Thanuja_Vadlamudi,

I think you can’t set the removal time to a calculated value by batch.

What you can do is to query all processes started at a single day and set the removal time to a value calulaed by yourself (or your program).

If you loop over several days, the removal times should at least fit the expected day.

Hope this helps, Ingo

Hi Ingo,
Thank you so much for the explanation. I can proceed with this info.

And also I had created new process instance after deploying the below config changes. It is not setting the removal time once that process is completed. Can you please suggest me what step I missed to clear that process instance.

config.setBatchOperationHistoryTimeToLive(“P548D”);
config.setHistoryCleanupBatchWindowStartTime(“18:00”);
config.setHistoryCleanupBatchWindowEndTime(“22:00”); config.setHistoryCleanupStrategy(ProcessEngineConfiguration.HISTORY_CLEANUP_STRATEGY_REMOVAL_TIME_BASED);
config.setHistoryRemovalTimeStrategy(ProcessEngineConfiguration.HISTORY_REMOVAL_TIME_STRATEGY_END);
config.setHistoryCleanupBatchSize(100);

Regards,
Thanuja.

Hi Ingo,

Could you please answer the above question.

Regards,
Thanuja.

Hi @Thanuja_Vadlamudi,

the perfect setup to clean up old process instances from the history is:

  1. Add the history time to live in the BPMN model. All process instances starting with this model version will have a removal time once they are finished.
  2. Set the history time to live for older versions in the Cockpit (with Enterprise version, Cleanup View | docs.camunda.org) or with the Rest API. All new process instances started after the change from this version will have a removal time once they are finished.
  3. Handle process instances that are started before you setup the history time to live. Here the removal time has to be set via batch.

You can check with this REST call, how many process instances will be cleaned in the run: Get Cleanable Process Instance Report Count | docs.camunda.org

And with this REST Api you can check how many process instances have be cleaned up in a given interval. Use history-cleanup-removed-process-instances as the metrics name: Get Metrics in Interval | docs.camunda.org

Hope this helps, Ingo

1 Like

HI Ingo,

Much helpful.

  1. Set the history time to live for older versions in the Cockpit (with Enterprise version, Cleanup View | docs.camunda.org) or with the Rest API. All new process instances started after the change from this version will have a removal time once they are finished.
    we are using community version. we can achieve this with rest API that means query all processes started at a single day and set the removal time to a value calculated by yourself (or your program) right.

Basically 2nd point and 3rd point : set the removal time to a value calculated by yourself (or your program) right.

Regards,
Thanuja.