Date: Fri, 29 Mar 2024 08:42:11 +0000 (UTC) Message-ID: <502839949.140460.1711701731123@ip-10-0-0-39.us-west-2.compute.internal> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_140459_1887662365.1711701731114" ------=_Part_140459_1887662365.1711701731114 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Note
1. Please shutdown your application server.
se= lect count(*) from SHKXPDLS
select c= ount(*) 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=20 from SHKActivities act=20 join SHKProcesses pro on act.Process =3D pro.oid=20 left join SHKProcessDefinitions def on pro.ProcessDefinition =3D de= f.oid=20 group by def.PackageId, def.ProcessDefinitionVersion=20 union=20 select packageId, packageVersion from app_package ) as used_processes group by packageId, packageVersion );
select c= ount(*) 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.ProcessDefinitionVer= sion) as packageVersion=20 from SHKActivities act=20 join SHKProcesses pro on act.Process =3D pro.oid=20 left join SHKProcessDefinitions def on pro.ProcessDefinition =3D de= f.oid=20 group by def.PackageId, def.ProcessDefinitionVersion=20 union=20 select packageId, TO_CHAR(packageVersion) from app_package ) group by packageId, packageVersion );
If you would like to list the process versions, you can use the followin= g query.
select *= 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=20 from SHKActivities act=20 join SHKProcesses pro on act.Process =3D pro.oid=20 left join SHKProcessDefinitions def on pro.ProcessDefinition =3D de= f.oid=20 group by def.PackageId, def.ProcessDefinitionVersion=20 union=20 select packageId, packageVersion from app_package ) as used_processes group by packageId, packageVersion );
select *= 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.ProcessDefinitionVer= sion) as packageVersion=20 from SHKActivities act=20 join SHKProcesses pro on act.Process =3D pro.oid=20 left join SHKProcessDefinitions def on pro.ProcessDefinition =3D de= f.oid=20 group by def.PackageId, def.ProcessDefinitionVersion=20 union=20 select packageId, TO_CHAR(packageVersion) from app_package ) group by packageId, packageVersion );
SET FORE= IGN_KEY_CHECKS=3D0; delete sa from SHKAssignmentsTable as sa left join SHKProcesses as sp on sa.ActivityProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete saj from SHKAndJoinTable as saj left join SHKActivities as sac on saj.Activity =3D sac.oid left join SHKProcesses as sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete sd from SHKDeadlines as sd left join SHKActivities as sac on sd.Activity =3D sac.oid left join SHKProcesses as sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete sad from SHKActivityData as sad left join SHKActivities as sac on sad.Activity =3D sac.oid left join SHKProcesses as sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete sac from SHKActivities as sac left join SHKProcesses as sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete spd from SHKProcessData as spd left join SHKProcesses as sp on spd.Process =3D sp.oid where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete spr from SHKProcessRequesters as spr left join SHKProcesses as sp on spr.Id =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete from SHKProcesses where (State =3D 1000006 or State =3D 1000008 or S= tate =3D 1000010);=20 SET FOREIGN_KEY_CHECKS=3D1;
EXEC sp_= MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" delete sa from SHKAssignmentsTable as sa left join SHKProcesses as sp on sa.ActivityProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete saj from SHKAndJoinTable as saj left join SHKActivities as sac on saj.Activity =3D sac.oid left join SHKProcesses as sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete sd from SHKDeadlines as sd left join SHKActivities as sac on sd.Activity =3D sac.oid left join SHKProcesses as sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete sad from SHKActivityData as sad left join SHKActivities as sac on sad.Activity =3D sac.oid left join SHKProcesses as sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete sac from SHKActivities as sac left join SHKProcesses as sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete spd from SHKProcessData as spd left join SHKProcesses as sp on spd.Process =3D sp.oid where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete spr from SHKProcessRequesters as spr left join SHKProcesses as sp on spr.Id =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete from SHKProcesses where (State =3D 1000006 or State =3D 1000008 or S= tate =3D 1000010);=20 =20 EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
delete (= select sa.* from SHKAssignmentsTable sa left join SHKProcesses sp on sa.ActivityProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= )); delete saj from SHKAndJoinTable as saj left join SHKActivities as sac on saj.Activity =3D sac.oid left join SHKProcesses as sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete sd from SHKDeadlines as sd left join SHKActivities as sac on sd.Activity =3D sac.oid left join SHKProcesses as sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= );=20 delete (select sad.* from SHKActivityData sad left join SHKActivities sac on sad.Activity =3D sac.oid left join SHKProcesses sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= ));=20 delete (select sac.* from SHKActivities sac left join SHKProcesses sp on sac.ProcessId =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= ));=20 delete (select spd.* from SHKProcessData spd left join SHKProcesses sp on spd.Process =3D sp.oid where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= ));=20 delete (select spr.* from SHKProcessRequesters spr left join SHKProcesses sp on spr.Id =3D sp.ID where (sp.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010= ));=20 delete from SHKProcesses where (State =3D 1000006 or State =3D 1000008 or S= tate =3D 1000010);
To limit the process within a time period for started and completed time= , change all the where clause to following and modify the date.
where (s= p.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010) and (FROM_UNIXTIME((sp.Started-sp.StartedTZO)/1000) >=3D '2020-05-03 00:= 00:00.000' and FROM_UNIXTIME((sp.Started-sp.StartedTZO)/1000) <=3D '2020= -05-06 23:59:59.999') and (FROM_UNIXTIME((sp.LastStateTime-sp.LastStateTimeTZO)/1000) >=3D '20= 20-05-03 00:00:00.000' and FROM_UNIXTIME((sp.LastStateTime-sp.LastStateTime= TZO)/1000) <=3D '2020-05-06 23:59:59.999')
where (s= p.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010) and (DATEADD(s, ((sp.Started-sp.StartedTZO)/1000), '1970-01-01 00:00:00') &= gt;=3D '2020-05-03 00:00:00' and DATEADD(s, ((sp.Started-sp.StartedTZO)/100= 0), '1970-01-01 00:00:00') <=3D '2020-05-06 23:59:59') and (DATEADD(s, ((sp.LastStateTime-sp.LastStateTimeTZO)/1000), '1970-01-01 = 00:00:00') >=3D '2020-05-03 00:00:00' and DATEADD(s, ((sp.LastStateTime-= sp.LastStateTimeTZO)/1000), '1970-01-01 00:00:00') <=3D '2020-05-06 23:5= 9:59')
where (s= p.State =3D 1000006 or sp.State =3D 1000008 or sp.State =3D 1000010) and (FROM_UNIXTIME((sp.Started-sp.StartedTZO)/1000) >=3D '2020-05-03 00:= 00:00' and FROM_UNIXTIME((sp.Started-sp.StartedTZO)/1000) <=3D '2020-05-= 06 23:59:59') and (FROM_UNIXTIME((sp.LastStateTime-sp.LastStateTimeTZO)/1000) >=3D '20= 20-05-03 00:00:00' and FROM_UNIXTIME((sp.LastStateTime-sp.LastStateTimeTZO)= /1000) <=3D '2020-05-06 23:59:59')
If you would like to clean the all the process instances data including = the running process instances, you can use the following query.
WARNING&nbs= p;This deletes all your process records. Perform a backup before running th= e query below and is not recommended to be executed on a production server!=
SET FORE= IGN_KEY_CHECKS=3D0; 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=3D1;
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"
delete f= rom SHKAssignmentsTable; delete from SHKAndJoinTable; delete from SHKDeadlines; delete from SHKActivityData; delete from SHKActivities; delete from SHKProcessData; delete from SHKProcessRequesters; delete from SHKProcesses;
SET FORE= IGN_KEY_CHECKS=3D0; delete x, xd from SHKXPDLS x join SHKXPDLData xd on x.oid=3D 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=20 from SHKActivities act=20 join SHKProcesses pro on act.Process =3D pro.oid=20 left join SHKProcessDefinitions def on pro.ProcessDefinition =3D de= f.oid=20 group by def.PackageId, def.ProcessDefinitionVersion=20 union=20 select packageId, packageVersion from app_package ) as used_processes group by packageId, packageVersion ); SET FOREIGN_KEY_CHECKS=3D1;
EXEC sp_= MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" delete xd from SHKXPDLData as xd left join SHKXPDLS as x on x.oid=3D 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=20 from SHKActivities act=20 join SHKProcesses pro on act.Process =3D pro.oid=20 left join SHKProcessDefinitions def on pro.ProcessDefinition =3D de= f.oid=20 group by def.PackageId, def.ProcessDefinitionVersion=20 union=20 select packageId, packageVersion from app_package ) as used_processes group by packageId, packageVersion ); delete x from SHKXPDLS as x=20 where concat(x.XPDLId, x.XPDLVersion) not in ( select concat(packageId,packageVersion) as id from ( select def.PackageId as packageId, def.ProcessDefinitionVersion as = packageVersion=20 from SHKActivities act=20 join SHKProcesses pro on act.Process =3D pro.oid=20 left join SHKProcessDefinitions def on pro.ProcessDefinition =3D de= f.oid=20 group by def.PackageId, def.ProcessDefinitionVersion=20 union=20 select packageId, packageVersion from app_package ) as used_processes group by packageId, packageVersion ); EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
delete (= select xd.* from SHKXPDLData xd left join SHKXPDLS x on x.oid=3D 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.ProcessDefinitionVer= sion) as packageVersion=20 from SHKActivities act=20 join SHKProcesses pro on act.Process =3D pro.oid=20 left join SHKProcessDefinitions def on pro.ProcessDefinition =3D de= f.oid=20 group by def.PackageId, def.ProcessDefinitionVersion=20 union=20 select packageId, TO_CHAR(packageVersion) from app_package ) group by packageId, packageVersion )); delete (select x.* from SHKXPDLS x=20 where concat(x.XPDLId, x.XPDLVersion) not in ( select concat(packageId,packageVersion) as id from ( select def.PackageId as packageId, TO_CHAR(def.ProcessDefinitionVer= sion) as packageVersion=20 from SHKActivities act=20 join SHKProcesses pro on act.Process =3D pro.oid=20 left join SHKProcessDefinitions def on pro.ProcessDefinition =3D de= f.oid=20 group by def.PackageId, def.ProcessDefinitionVersion=20 union=20 select packageId, TO_CHAR(packageVersion) from app_package ) group by packageId, packageVersion ));