Development Server Data Cleaning

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.

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

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:

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