KB Articles

KB Article # 10063

Topic/Category: Workflow, MSSQL, mysql, mariadb

Ephesoft Version: 4x

Description
A significant amount of workflow data can accumulate over a period of time. This data accumulation may hamper the overall application performance in the long run. 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.

Analysis:

Manually cleaning the Activiti tables will impact the accuracy of reporting data. Therefore, 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.

Solution:
Manual Activiti Table Cleanup for MSSQL

Database Cleanup (Stored Procedure)

Perform the following steps to manually clean the Activiti tables:

1. Back up the Ephesoft database.

2. If the Advanced Reporting license is valid for the Ephesoft server, add @advanced in job filter(add @advanced in below IN clause).

SET @minimumDateTime = (SELECT MIN(last_execution_at) FROM <reportDB>.dbo.last_execution where job IN(@batchInstance,@dashBoard,@standard));

  • Replace the <reportDB> with the actual report database name.

3. Create the procedure in Ephesoft database.

4. In other SQL Query editor run the query EXEC cleanActivity to execute the procedure.

5. After the procedure executes, all the Activiti-related data for batches in DELETED and FINISHED state will be deleted from tables ACT_HI_ACTINST, ACT_HI_PROCINST and ACT_HI_VARINST.

Procedure mentioned at Step 4

This procedure applies to MS SQL 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

Database Cleanup (Dropping Tables)

Perform these steps for database cleanup:

1. Back up the database before proceeding.

2. Stop the Ephesoft server if it is running.

A) Prerequisites: All batches must be in the following states to initiate the clean-up:

1. DELETED

2. ERROR

3. FINISHED

B) Drop the following tables from <ephesoft_DB> by executing the queries in the query editor.
While executing the query, 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;

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

C) In the <reporting_DB>, execute the given query after updating the <reporting_DB> with the database name used in reporting.

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');

D) Set the workflow.deploy parameter to true in the following file:

{Ephesoft Installation Directory}\WEB-INF\classes\META-INF\dcma-workflows\dcma-workflows.properties

Restart the server.

E) Restart the Ephesoft server.

NOTES:

    • It is very important hat no batches be in a state other than DELETED, ERROR or FINISHED.
    • 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.