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
- Back up the Ephesoft database.
- 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.
- Create the procedure in the Ephesoft database.
- 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
- 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
- Back up the database before proceeding.
- Stop Ephesoft Transact if it is running.
Important: All batches must be in the following states to initiate the clean-up:
- DELETED
- ERROR
- FINISHED
- 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.
- 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');
- In the dcma-workflows.properties file, located at [Ephesoft_Directory]\WEB-INF\classes\META-INF\dcma-workflows, set the workflow.deploy parameter to true:
- Restart the database server.
- 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.