KB Articles

KB Article # 10063

Topic/Category: Workflow, MSSQL, mysql, mariadb

Ephesoft Version: 4x

Description
A significant amount of data related to workflow get accumulated over a period of time. This piling up of data may hamper the overall application performance in the long run. By manually cleaning up the Activiti related tables this situation can be avoided.

Analysis:

Manually Cleaning Activiti tables will impact accuracy of reporting data, thus we would not recommend cleaning up Activiti tables manually. However in case of Activiti corruption and getting a system back online this may be the only alternative.

 

Solution:
Activiti Table Cleanup for MSSQL

Database Cleanup (Stored Procedure)

  1. Take backup of the Ephesoft database before proceeding further.
  2. If 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));
  3. Replace the <reportDB> with the actual report database name.
  4. Create the procedure in Ephesoft database.
  5. In other SQL Query editor run the query EXEC cleanActivity to execute the procedure;
  6. After the procedure execution, all the Activiti related data for batches in DELETED and FINSIHED state will be deleted from tables ACT_HI_ACTINST, ACT_HI_PROCINST and ACT_HI_VARINST.

Procedure mentioned at Step 4.

For 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

Database Cleanup (Dropping Tables)

Take the backup of the database before proceeding. Stop 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 activity 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 workflow.deploy parameter to true in file {Ephesoft Installation Directory}\WEB-INF\classes\META-INF\dcma-workflows\dcma-workflows.properties and restart the server.
E) Restart the Ephesoft server.

NOTE: It is important to note that no batches must be in a state other than DELETED, ERROR or FINISHED. Cleaning up data otherwise may lead to database corruption.
By executing the following procedure previous reporting data will be lost. Ephesoft reporting is directly linked to the Activiti tables and deleting them would impact the reporting data.

 

 

Was this article helpful to you?

Walter Lee