Development Server Data Cleaning

Check the existing count of XPDLs
Check the count of unused XPDLs
Delete completed process instances
Delete the unused XDPLs

Over time, as process designs are continuously updated and developed on the development server, an increasing number of XPDLs accumulate in memory. This buildup can lead to a java.lang.OutOfMemoryError exception during runtime or when starting the server. To address this issue, periodically clean the unused XPDLs from the system. Use the following steps to safely perform this cleanup. The example queries provided support both MySQL and MSSQL databases.

Important Precautions:

Ensure that the application server is shut down before beginning the cleanup process.
Perform a full database backup to safeguard your data before proceeding with the cleanup steps.

Check the existing count of XPDLs

On a common production server, there are normally only a few app versions, and each version is tied to 1 process version. This is because we won't do any development on the production server, so the process version is only increased during app importation. However, this will be a different case for the development server. The process version will be going up to a few hundred process versions, which means there are a few hundred XPDL data cached in memory. To check how many XPDLs are cached, please run the following query in your database server.

MySQL, MSSQL, Oracle:
```
select count(*) from SHKXPDLS
```
SQL

Check the count of unused XPDLs

In all the cached process version XPDLs, there is a case that any process instances do not use some process version. These unused XPDLs can be safely deleted. You can use the following query to check the number of unused XPDLs and know how many are unused in the total number of XPDLs. Use the following query:

MySQL and MSSQL:

select count(*) from SHKXPDLS x
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId,packageVersion) as id from (
        select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion
        from SHKActivities act
        join SHKProcesses pro on act.Process = pro.oid
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
        group by def.PackageId, def.ProcessDefinitionVersion
        union
        select packageId, packageVersion from app_package
    ) as used_processes group by packageId, packageVersion
);

Oracle:

select count(*) from SHKXPDLS x
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId,packageVersion) as id from (
        select def.PackageId as packageId, TO_CHAR(def.ProcessDefinitionVersion) as packageVersion
        from SHKActivities act
        join SHKProcesses pro on act.Process = pro.oid
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
        group by def.PackageId, def.ProcessDefinitionVersion
        union
        select packageId, TO_CHAR(packageVersion) from app_package
    ) group by packageId, packageVersion
);

Delete completed process instances

Suppose the process instance data are not important to you or you have captured your process instance data as a copy by the Process Data Collector plugin. In that case, you can delete your process instance data to increase the number of unused XPDLs. In this case, only the process version for running process instances and the latest process version for each app will be kept. Use the following queries to do so:

MySQL:

SET FOREIGN_KEY_CHECKS=0;
  
delete sa from SHKAssignmentsTable as sa
left join SHKProcesses as sp on sa.ActivityProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
 
delete saj from SHKAndJoinTable as saj
left join SHKActivities as sac on saj.Activity = sac.oid
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
  
delete sd from SHKDeadlines as sd
left join SHKActivities as sac on sd.Activity = sac.oid
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
  
delete sad from SHKActivityData as sad
left join SHKActivities as sac on sad.Activity = sac.oid
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
delete sac from SHKActivities as sac
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
 
delete spd from SHKProcessData as spd
left join SHKProcesses as sp on spd.Process = sp.oid
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
 
delete spr from SHKProcessRequesters as spr
left join SHKProcesses as sp on spr.Id = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
 
delete from SHKProcesses where (State = 1000006 or State = 1000008 or State = 1000010);
  
SET FOREIGN_KEY_CHECKS=1;

MSSQL

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
 
delete sa from SHKAssignmentsTable as sa
left join SHKProcesses as sp on sa.ActivityProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
 
delete saj from SHKAndJoinTable as saj
left join SHKActivities as sac on saj.Activity = sac.oid
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
  
delete sd from SHKDeadlines as sd
left join SHKActivities as sac on sd.Activity = sac.oid
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
  
delete sad from SHKActivityData as sad
left join SHKActivities as sac on sad.Activity = sac.oid
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
  
delete sac from SHKActivities as sac
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
 
delete spd from SHKProcessData as spd
left join SHKProcesses as sp on spd.Process = sp.oid
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
 
delete spr from SHKProcessRequesters as spr
left join SHKProcesses as sp on spr.Id = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
 
delete from SHKProcesses where (State = 1000006 or State = 1000008 or State = 1000010);
  
EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Oracle:

delete (select sa.* from SHKAssignmentsTable sa
left join SHKProcesses sp on sa.ActivityProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010));
 
delete saj from SHKAndJoinTable as saj
left join SHKActivities as sac on saj.Activity = sac.oid
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
  
delete sd from SHKDeadlines as sd
left join SHKActivities as sac on sd.Activity = sac.oid
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
  
delete (select sad.* from SHKActivityData sad
left join SHKActivities sac on sad.Activity = sac.oid
left join SHKProcesses sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010));
  
delete (select sac.* from SHKActivities sac
left join SHKProcesses sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010));
 
delete (select spd.* from SHKProcessData spd
left join SHKProcesses sp on spd.Process = sp.oid
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010));
 
delete (select spr.* from SHKProcessRequesters spr
left join SHKProcesses sp on spr.Id = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010));
 
delete from SHKProcesses where (State = 1000006 or State = 1000008 or State = 1000010);

To limit the process within a period for the start and completion, change all the where clauses to the following and modify the date, like in the following examples:

MySQL:

where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010)
and (FROM_UNIXTIME((sp.Started-sp.StartedTZO)/1000) >= '2020-05-03 00:00:00.000' and FROM_UNIXTIME((sp.Started-sp.StartedTZO)/1000) <= '2020-05-06 23:59:59.999')
and (FROM_UNIXTIME((sp.LastStateTime-sp.LastStateTimeTZO)/1000) >= '2020-05-03 00:00:00.000' and FROM_UNIXTIME((sp.LastStateTime-sp.LastStateTimeTZO)/1000) <= '2020-05-06 23:59:59.999')

MSSQL:

where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010)
and (DATEADD(s, ((sp.Started-sp.StartedTZO)/1000), '1970-01-01 00:00:00') >= '2020-05-03 00:00:00' and DATEADD(s, ((sp.Started-sp.StartedTZO)/1000), '1970-01-01 00:00:00') <= '2020-05-06 23:59:59')
and (DATEADD(s, ((sp.LastStateTime-sp.LastStateTimeTZO)/1000), '1970-01-01 00:00:00') >= '2020-05-03 00:00:00' and DATEADD(s, ((sp.LastStateTime-sp.LastStateTimeTZO)/1000), '1970-01-01 00:00:00') <= '2020-05-06 23:59:59')

Oracle:

where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010)
and (FROM_UNIXTIME((sp.Started-sp.StartedTZO)/1000) >= '2020-05-03 00:00:00' and FROM_UNIXTIME((sp.Started-sp.StartedTZO)/1000) <= '2020-05-06 23:59:59')
and (FROM_UNIXTIME((sp.LastStateTime-sp.LastStateTimeTZO)/1000) >= '2020-05-03 00:00:00' and FROM_UNIXTIME((sp.LastStateTime-sp.LastStateTimeTZO)/1000) <= '2020-05-06 23:59:59')

You can use the following query to clean all the process instance data, including the running process instances:

Warning
This query deletes all your process records. Perform a backup before running the query below. It is not recommended to be executed on a production server.

MySQL:

SET FOREIGN_KEY_CHECKS=0;
 
delete from SHKAssignmentsTable;
delete from SHKAndJoinTable;
delete from SHKDeadlines;
delete from SHKActivityData;
delete from SHKActivities;
delete from SHKProcessData;
delete from SHKProcessRequesters;
delete from SHKProcesses;
  
SET FOREIGN_KEY_CHECKS=1;

MSSQL:

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
 
delete from SHKAssignmentsTable;
delete from SHKAndJoinTable;
delete from SHKDeadlines;
delete from SHKActivityData;
delete from SHKActivities;
delete from SHKProcessData;
delete from SHKProcessRequesters;
delete from SHKProcesses;
 
EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Oracle:

delete from SHKAssignmentsTable;
delete from SHKAndJoinTable;
delete from SHKDeadlines;
delete from SHKActivityData;
delete from SHKActivities;
delete from SHKProcessData;
delete from SHKProcessRequesters;
delete from SHKProcesses;

Delete the unused XDPLs

Now, you can delete the unused XPDLs, and they will be free from your memory cache when the next server starts. Use the following query to do so:

MySQL:

SET FOREIGN_KEY_CHECKS=0;
delete x, xd from SHKXPDLS x join SHKXPDLData xd on x.oid= xd.XPDL
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId,packageVersion) as id from (
        select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion
        from SHKActivities act
        join SHKProcesses pro on act.Process = pro.oid
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
        group by def.PackageId, def.ProcessDefinitionVersion
        union
        select packageId, packageVersion from app_package
    ) as used_processes group by packageId, packageVersion
);
SET FOREIGN_KEY_CHECKS=1;

MSSQL:

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
 
delete xd from SHKXPDLData as xd
left join SHKXPDLS as x on x.oid= xd.XPDL
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId,packageVersion) as id from (
        select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion
        from SHKActivities act
        join SHKProcesses pro on act.Process = pro.oid
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
        group by def.PackageId, def.ProcessDefinitionVersion
        union
        select packageId, packageVersion from app_package
    ) as used_processes group by packageId, packageVersion
);
 
delete x from SHKXPDLS as x
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId,packageVersion) as id from (
        select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion
        from SHKActivities act
        join SHKProcesses pro on act.Process = pro.oid
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
        group by def.PackageId, def.ProcessDefinitionVersion
        union
        select packageId, packageVersion from app_package
    ) as used_processes group by packageId, packageVersion
);
 
EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Oracle:

delete (select xd.* from SHKXPDLData xd
left join SHKXPDLS x on x.oid= xd.XPDL
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId,packageVersion) as id from (
        select def.PackageId as packageId, TO_CHAR(def.ProcessDefinitionVersion) as packageVersion
        from SHKActivities act
        join SHKProcesses pro on act.Process = pro.oid
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
        group by def.PackageId, def.ProcessDefinitionVersion
        union
        select packageId, TO_CHAR(packageVersion) from app_package
    ) group by packageId, packageVersion
));
 
delete (select x.* from SHKXPDLS x
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId,packageVersion) as id from (
        select def.PackageId as packageId, TO_CHAR(def.ProcessDefinitionVersion) as packageVersion
        from SHKActivities act
        join SHKProcesses pro on act.Process = pro.oid
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
        group by def.PackageId, def.ProcessDefinitionVersion
        union
        select packageId, TO_CHAR(packageVersion) from app_package
    ) group by packageId, packageVersion
));

Created by Aadrian Last modified by Aadrian on Mar 12, 2025

Tags:

Previous Maintenance NextManaging Log Files

Check the existing count of XPDLs
Check the count of unused XPDLs
Delete completed process instances
Delete the unused XDPLs