Applies To Version: 3.0.x – 3.1.x

 

This procedure deletes the data for the batch instances. It deletes the data from the batch_instance and jbpm work tables.

This is a sql script that has the procedure “clean_batch_instance”. This procedure takes two inputs which are expected from user:

  1. Batch Instance Status: Either of valid batch instance status. For example, “FINISHED” , “DELETED” , “ERROR”.
  2. Date (expected format yyyy-mm-dd): The date before which we want the batches to be cleaned.

In order to run the sql script, please perform the following steps:

  1. please update the following line (Line#2)
    use ephesoft;
    to the appropriate database name.
  2. please update the following line (Line#42)
    exec clean_batch_instance ‘DELETED’,’2011-08-24′;
    where,
    DELETED :- Please replace it by the appropriate batch instance status.
    2011-08-24 :- Please replace it by the appropriate date up to which we intend to clean the batch instances data.

MSSQL CODE

–Database name on which the clean up is to be performed.
USE ephesoft;

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'clean_batch_instance_by_status') BEGIN

DROP procedure clean_batch_instance_by_status;
END GO

CREATE procedure clean_batch_instance_by_status(@batch_instance_status varchar(25), @last_modified_date varchar(255)) as

BEGIN
SET NOCOUNT ON;
DECLARE @batch_Instance_identifier VARCHAR(255);
DECLARE @like_clause VARCHAR(255);
DECLARE cur_batch_INstance_list cursor Static for SELECT bi.identifier FROM batch_INstance bi WHERE bi.batch_status = @batch_INstance_status AND bi.last_modified < convert(date,@last_modified_date);
OPEN cur_batch_INstance_list;
SELECT @@CURSOR_ROWS as BATCH_INSTANCES_COUNT;
FETCH NEXT FROM cur_batch_INstance_list INTO @batch_Instance_identifier;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @like_clause = '%' + @batch_Instance_identifier + '%';
DELETE FROM batch_INstance WHERE batch_INstance.identifier = @batch_Instance_identifier;
DELETE FROM jbpm4_variable WHERE jbpm4_variable.DBID_ IN
(SELECT jbpm4_variable.DBID_ FROM jbpm4_execution WHERE jbpm4_execution.DBID_ = jbpm4_variable.EXECUTION_ AND jbpm4_execution.ID_ like @like_clause);
DELETE FROM jbpm4_job WHERE jbpm4_job.DBID_ IN
(SELECT jbpm4_job.DBID_ FROM jbpm4_execution WHERE jbpm4_execution.DBID_ = jbpm4_job.EXECUTION_ AND jbpm4_execution.ID_ like @like_clause);
DELETE FROM jbpm4_execution WHERE jbpm4_execution.ID_ like @like_clause;
DELETE FROM jbpm4_hist_actINst WHERE jbpm4_hist_actINst.EXECUTION_ like @like_clause;
DELETE FROM jbpm4_hist_procINst WHERE jbpm4_hist_procINst.ID_ like @like_clause;
FETCH NEXT FROM cur_batch_INstance_list INTO @batch_Instance_identifier;
END
CLOSE cur_batch_INstance_list;
DEALLOCATE cur_batch_INstance_list;
END

MYSQL CODE

–Database name on which the clean up is to be performed.
USE (DatabaseName);

DELIMITER //

DROP PROCEDURE IF EXISTS clean_batch_instance_by_status;//

CREATE procedure clean_batch_instance_by_status (in batch_instance_status varchar(25),in last_modified_date varchar(255))
BEGIN
Declare batch_Instance_identifier VARCHAR(255);
Declare DbId Int;
DECLARE no_more_rows, no_more_batch BOOLEAN;

DECLARE cur_batch_instance_list CURSOR FOR SELECT bi.identifier FROM batch_instance bi WHERE bi.batch_status = batch_instance_status AND bi.last_modified < STR_TO_DATE(last_modified_date, '%Y-%m-%d');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_batch = TRUE;
SET no_more_batch = FALSE;
OPEN cur_batch_instance_list;
batch_loop: LOOP
FETCH cur_batch_instance_list INTO batch_Instance_identifier;
IF no_more_batch THEN
LEAVE batch_loop;
END IF;

BLOCK2: BEGIN
DECLARE delete_lob CURSOR FOR
Select LOB_ FROM JBPM4_VARIABLE where EXECUTION_ in (
Select DBID_ from jbpm4_execution where ID_ LIKE CONCAT('%',batch_Instance_identifier,'.%')
OR ID_ LIKE CONCAT('%',batch_Instance_identifier));
DECLARE delete_variable CURSOR FOR
Select DBID_ FROM JBPM4_VARIABLE where EXECUTION_ in (
Select DBID_ from jbpm4_execution where ID_ LIKE CONCAT('%',batch_Instance_identifier,'.%')
OR ID_ LIKE CONCAT('%',batch_Instance_identifier));
DECLARE delete_execution CURSOR FOR
SELECT DBID_ FROM JBPM4_EXECUTION where ID_ LIKE CONCAT('%',batch_Instance_identifier,'.%') OR ID_ LIKE CONCAT('%',batch_Instance_identifier);
DECLARE delete_actinst CURSOR FOR
SELECT DBID_ FROM jbpm4_hist_actinst where EXECUTION_ LIKE CONCAT('%',batch_Instance_identifier,'.%') OR EXECUTION_ LIKE CONCAT('%',batch_Instance_identifier);
DECLARE delete_procinst CURSOR FOR
SELECT DBID_ FROM JBPM4_HIST_PROCINST where ID_ LIKE CONCAT('%',batch_Instance_identifier,'.%') OR ID_ LIKE CONCAT('%',batch_Instance_identifier);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

SET no_more_rows = FALSE;
OPEN delete_variable;
read_loop: LOOP
FETCH delete_variable INTO DbId;
IF no_more_rows THEN
LEAVE read_loop;
END IF;

SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM JBPM4_VARIABLE where DBID_ = DbId;
SET FOREIGN_KEY_CHECKS = 1;

END LOOP;
CLOSE delete_variable;

SET no_more_rows = FALSE;
OPEN delete_lob;
read_loop: LOOP
FETCH delete_lob INTO DbId;
IF no_more_rows THEN
LEAVE read_loop;
END IF;

SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM JBPM4_LOB where DBID_ = DbId;
SET FOREIGN_KEY_CHECKS = 1;

END LOOP;
CLOSE delete_lob;

SET no_more_rows = FALSE;
OPEN delete_execution;
read_loop: LOOP
FETCH delete_execution INTO DbId;
IF no_more_rows THEN
LEAVE read_loop;
END IF;

SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM JBPM4_EXECUTION where DBID_ = DbId;
SET FOREIGN_KEY_CHECKS = 1;

END LOOP;
CLOSE delete_execution;

SET no_more_rows = FALSE;
OPEN delete_actinst;
read_loop: LOOP
FETCH delete_actinst INTO DbId;
IF no_more_rows THEN
LEAVE read_loop;
END IF;

SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM jbpm4_hist_actinst where DBID_ = DbId;
SET FOREIGN_KEY_CHECKS = 1;

END LOOP;
CLOSE delete_actinst;

SET no_more_rows = FALSE;
OPEN delete_procinst;
read_loop: LOOP
FETCH delete_procinst INTO DbId;
IF no_more_rows THEN
LEAVE read_loop;
END IF;

SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM JBPM4_HIST_PROCINST where DBID_ = DbId;
SET FOREIGN_KEY_CHECKS = 1;

END LOOP;
CLOSE delete_procinst;
END BLOCK2;
DELETE FROM batch_instance WHERE batch_instance.identifier = batch_Instance_identifier;
END LOOP batch_loop;
CLOSE cur_batch_instance_list;

END//
DELIMITER ;

*****

–This procedure DELETES all the batch Instance data with the given STATUS AND which were modified before the date specified. –Parameter 1 :- Batch INstance Status. For example, “FINISHED” , “DELETED” , “ERROR”. –Parameter 2 :- Date of format yyyy-mm-dd

MSSQL: 

GO EXEC clean_batch_Instance_by_status 'DELETED','2015-01-30'; GO

MySQL: 

use database_name; CALL clean_batch_instance_by_status('DELETED','2015-08-20');

Was this article helpful to you?

Walter Lee

Comments are closed.