KB0010063: Manually Cleaning the Activiti Tables

Applies to: Ephesoft Transact 4.5.0.0 and above

Issue

A significant amount of workflow data can accumulate over time, which may hamper the overall application performance.

Solution

You can avoid this issue by manually cleaning up the Activiti-related tables using the instructions in this article, but this manual cleanup method is only recommended for extenuating circumstances.

Important: Manually cleaning the Activiti tables will impact the accuracy of reporting data. Ephesoft does not recommend manual Activiti table cleanup. However, if there is a case of Activiti corruption, and it is important to get a system back online, this method may be the only alternative.

Stored Procedures

  1. Back up the Ephesoft database.
  2. If the Advanced Reporting license is valid for the Ephesoft server, add @advanced to the IN clause in the job filter below:
SET @minimumDateTime = (SELECT MIN(last_execution_at) FROM <reportDB>.dbo.last_execution where job IN(@batchInstance,@dashBoard,@standard));

Note: Replace <reportDB> with the actual report database name.

  1. Create the procedure in the Ephesoft database.
  2. In a separate SQL Query editor, run the following query to execute the procedure:
EXEC cleanActivity

Note: This procedure applies to MSSQL only:

CREATE PROCEDURE cleanActivity
AS
BEGIN
DECLARE @minimumDateTime AS DATETIME;
DECLARE @delState AS VARCHAR(20);
DECLARE @finishState AS VARCHAR(20);
DECLARE @batchInstance AS VARCHAR(30);
DECLARE @dashBoard AS VARCHAR(20);
DECLARE @standard AS VARCHAR(20);
DECLARE @advanced AS VARCHAR(20);
SET @delState = 'DELETED';
SET @finishState = 'FINISHED';
SET @batchInstance = 'BATCH_INSTANCE_LAST_UPDATE';
SET @dashBoard = 'DASHBOARD';
SET @standard = 'STANDARD';
SET @advanced = 'ADVANCED';
SET @minimumDateTime = (SELECT MIN(last_execution_at) FROM report.dbo.last_execution where job IN(@batchInstance,@dashBoard,@standard));
DELETE FROM ACT_HI_VARINST WHERE PROC_INST_ID_ IN (
SELECT DISTINCT(proc_inst.PROC_INST_ID_) FROM ACT_HI_PROCINST proc_inst JOIN batch_instance batch_inst ON proc_inst.NAME_=batch_inst.identifier
WHERE batch_inst.batch_status IN (@delState,@finishState) AND batch_inst.last_modified <@minimumDateTime);
DELETE FROM ACT_HI_ACTINST WHERE PROC_INST_ID_ IN (
SELECT DISTINCT(proc_inst.PROC_INST_ID_) FROM ACT_HI_PROCINST proc_inst JOIN batch_instance batch_inst ON proc_inst.NAME_=batch_inst.identifier
WHERE batch_inst.batch_status IN (@delState,@finishState) AND batch_inst.last_modified <@minimumDateTime);
DELETE FROM ACT_HI_PROCINST WHERE PROC_INST_ID_ IN (
SELECT DISTINCT(proc_inst.PROC_INST_ID_) FROM ACT_HI_PROCINST proc_inst JOIN batch_instance batch_inst ON proc_inst.NAME_=batch_inst.identifier
WHERE batch_inst.batch_status IN (@delState,@finishState) AND batch_inst.last_modified < @minimumDateTime);
END
  1. After the procedure executes, all the Activiti-related data for batches in DELETED and FINISHED state will be deleted from the following tables:
    • ACT_HI_ACTINST
    • ACT_HI_PROCINST
    • ACT_HI_VARINST

Dropping Tables

  1. Back up the database before proceeding.
  2. Stop Ephesoft Transact if it is running.

Important: All batches must be in the following states to initiate the clean-up:

  • DELETED
  • ERROR
  • FINISHED
  1. Drop the following tables from <ephesoft_DB> by executing the queries in the query editor.

Note: Replace <ephesoft_DB> with the name of the Ephesoft database.

MSSQL

drop table <ephesoft_DB>.dbo.ACT_EVT_LOG;
drop table <ephesoft_DB>.dbo.ACT_GE_PROPERTY;
drop table <ephesoft_DB>.dbo.ACT_HI_ACTINST;
drop table <ephesoft_DB>.dbo.ACT_HI_ATTACHMENT;
drop table <ephesoft_DB>.dbo.ACT_HI_COMMENT;
drop table <ephesoft_DB>.dbo.ACT_HI_DETAIL;
drop table <ephesoft_DB>.dbo.ACT_HI_IDENTITYLINK;
drop table <ephesoft_DB>.dbo.ACT_HI_PROCINST;
drop table <ephesoft_DB>.dbo.ACT_HI_TASKINST;
drop table <ephesoft_DB>.dbo.ACT_HI_VARINST;
drop table <ephesoft_DB>.dbo.ACT_ID_INFO;
drop table <ephesoft_DB>.dbo.ACT_ID_MEMBERSHIP;
drop table <ephesoft_DB>.dbo.ACT_ID_USER;
drop table <ephesoft_DB>.dbo.ACT_RE_MODEL;
drop table <ephesoft_DB>.dbo.ACT_RU_EVENT_SUBSCR;
drop table <ephesoft_DB>.dbo.ACT_RU_IDENTITYLINK;
drop table <ephesoft_DB>.dbo.ACT_RU_JOB;
drop table <ephesoft_DB>.dbo.ACT_RU_TASK;
drop table <ephesoft_DB>.dbo.ACT_RU_VARIABLE;
drop table <ephesoft_DB>.dbo.ACT_GE_BYTEARRAY;
drop table <ephesoft_DB>.dbo.ACT_ID_GROUP;
drop table <ephesoft_DB>.dbo.ACT_RE_DEPLOYMENT;
drop table <ephesoft_DB>.dbo.ACT_RU_EXECUTION;
drop table <ephesoft_DB>.dbo.ACT_RE_PROCDEF;

MySQL or MariaDB

drop table <ephesoft_DB>.act_evt_log;
drop table <ephesoft_DB>.act_ge_property;
drop table <ephesoft_DB>.act_hi_actinst;
drop table <ephesoft_DB>.act_hi_attachment;
drop table <ephesoft_DB>.act_hi_comment;
drop table <ephesoft_DB>.act_hi_detail;
drop table <ephesoft_DB>.act_hi_identitylink;
drop table <ephesoft_DB>.act_hi_procinst;
drop table <ephesoft_DB>.act_hi_taskinst;
drop table <ephesoft_DB>.act_hi_varinst;
drop table <ephesoft_DB>.act_id_info;
drop table <ephesoft_DB>.act_id_membership;
drop table <ephesoft_DB>.act_id_user;drop table <ephesoft_DB>.act_re_model;
drop table <ephesoft_DB>.act_ru_event_subscr;
drop table <ephesoft_DB>.act_ru_identitylink;
drop table <ephesoft_DB>.act_ru_job;
drop table <ephesoft_DB>.act_ru_task;
drop table <ephesoft_DB>.act_ru_variable;
drop table <ephesoft_DB>.act_ge_bytearray;
drop table <ephesoft_DB>.act_id_group;
drop table <ephesoft_DB>.act_re_deployment;
drop table <ephesoft_DB>.act_ru_execution;
drop table <ephesoft_DB>.act_re_procdef;

Important: After the operation, ensure all Activiti-related tables (ACT_*) are removed from the database.

  1. In the <reporting_DB>, execute the following query:

MSSQL

update <reporting_DB>.dbo.last_execution set last_execution_at = GETDATE() where job in ('BATCH_INSTANCE_LAST_UPDATE' ,'DASHBOARD' ,'STANDARD', 'ADVANCED');

MySQL or MariaDB

update <report_DB>.last_execution set last_execution_at = NOW() where job in ('BATCH_INSTANCE_LAST_UPDATE' ,'DASHBOARD' ,'STANDARD', 'ADVANCED');
  1. In the dcma-workflows.properties file, located at [Ephesoft_Directory]\WEB-INF\classes\META-INF\dcma-workflows, set the workflow.deploy parameter to true:
  2. Restart the database server.
  3. Restart Ephesoft Transact.

Note:

  • All batches must be in DELETED, ERROR or FINISHED state.
    • Cleaning up data otherwise may lead to database corruption.
  • By executing the procedure in this document, previous reporting data will be lost.
  • Ephesoft reporting is directly linked to the Activiti tables and deleting them will impact the reporting data.