Stumbling Through – Clearing Test K2 Data
In many K2 projects I've worked on, we end up having to tie existing business data into the various workflow processes. While k2 provides a means to do this via process/activity data fields, they tend to be difficult to query against and can be inefficient compared to an existing schema that has been optimized specifically for the business data involved. That, and why store the same data twice if it already exists in a database? That discussion is not the point of this blog entry, though it is an interesting topic that I may revisit later. For now, lets roll with the assumption that we have data in a custom database that is related to K2 process instances.
During development and testing cycles, one of the tedious activities for me has been trying to clean up old test data, particularly when there are thousands of process instances involved. The reason for this is because cleaning data in the scenario described above is a two-step process: First delete the custom database data and then delete the K2 process instances. Deleting the custom database data is no problem - write a quick SQL script and run it as needed but cleaning the K2 process instances is a different story...
There are two main ways to clear K2 process instances: via API or via Workspace. While we could write a program that would execute our database clean up scripts and then iterate through and delete our K2 process instances via API, but that is too much work for this lazy busy developer. The workspace approach can be a real hassle when there are thousands of process instances because let's face it - deleting more than 100 process instances at a time is not a snappy experience through today's workspace.
After wrestling with clearing test data repeatedly through the workspace, I finally posed myself the question: Why is it that we can't we delete process instances through the same SQL script we use to clear the custom data? Surely, the workspace uses some sort of stored procedure(s) when it deletes a process instance, right? I started digging around through the K2 databases to see if any stored procedures jumped out at me as obvious choices and while there were a few likely candidates, none of them fit the bill. Frustrated, I posed the question on the K2Underground and was quickly directed to an article that came close to what I was trying to accomplish. Using the code in this article as a base, I tweaked it until it was capable of clearing all process instances for a given folder (could easily work for a given process name as well) and it has been so useful to me that I figured I'd share it here. Be warned, this is slapped together for development assistance, and should by no means be used on a production box! Happy data clearing...
*** UPDATE 3/15/2010 – I’ve gone through and verified that all tables were being cleared, and updated the script accordingly ***
/*
Delete all workflow process instances
*/
Use K2Server
DECLARE @FOLDERNAME NVARCHAR(1024)
SET @FOLDERNAME = '[Folder name]'
PRINT 'Starting to remove Process Instances for ' + @FOLDERNAME
PRINT 'Getting List of Process Instances'
SELECT inst.ID
INTO #TMP
FROM dbo.[_ProcInst] AS inst
INNER JOIN dbo.[_Proc] AS prc
ON (inst.ProcID = prc.ID)
INNER JOIN dbo.[_ProcSet] AS pset
ON (prc.ProcSetID = pset.ID)
WHERE pset.[Folder] = @FOLDERNAME;
PRINT 'Removing Process Instances from _IPC'
DELETE _IPC
FROM _IPC
INNER JOIN #TMP
ON SrcProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _IPC'
PRINT 'Removing Process Instances from _Async'
DELETE _Async
FROM _Async
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _Async'
PRINT 'Removing Process Instances from _ErrorLog'
DELETE _ErrorLog
FROM _ErrorLog
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ErrorLog'
PRINT 'Removing Process Instances from _FieldProcInst'
DELETE _FieldProcInst
FROM _FieldProcInst
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _FieldProcInst'
PRINT 'Removing Process Instances from _FieldActInst'
DELETE _FieldActInst
FROM _FieldActInst
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _FieldActInst'
PRINT 'Removing Process Instances from _FieldActInstDest'
DELETE _FieldActInstDest
FROM _FieldActInstDest
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _FieldActInstDest'
PRINT 'Removing Process Instances from _FieldSlot'
DELETE _FieldSlot
FROM _FieldSlot
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _FieldSlot'
PRINT 'Removing Process Instances from _FieldOnDemand'
DELETE _FieldOnDemand
FROM _FieldOnDemand
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _FieldOnDemand'
PRINT 'Removing Process Instances from _IPCReturn'
DELETE _IPCReturn
FROM _IPCReturn
INNER JOIN #TMP
ON SrcProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _IPCReturn'
PRINT 'Removing Process Instances from _Log'
DELETE _Log
FROM _Log
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _Log'
PRINT 'Removing Process Instances from _LogProcInst'
DELETE _LogProcInst
FROM _LogProcInst
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _LogProcInst'
PRINT 'Removing Process Instances from _ProcInstDestQueue'
DELETE _ProcInstDestQueue
FROM _ProcInstDestQueue
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstDestQueue'
PRINT 'Removing Process Instances from _ProcInstDestQueue'
DELETE _ProcInstDestQueue
FROM _ProcInstDestQueue
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstDestQueue'
PRINT 'Removing Process Instances from _ServerList'
DELETE _ServerList
FROM _ServerList
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ServerList'
PRINT 'Removing Process Instances from _WorklistSlot'
DELETE _WorklistSlot
FROM _WorklistSlot
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _WorklistSlot'
PRINT 'Removing Process Instances from _WorklistHeader'
DELETE _WorklistHeader
FROM _WorklistHeader
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _WorklistHeader'
PRINT 'Removing Process Instances from _ActionActInstShared'
DELETE _ActionActInstShared
FROM _ActionActInstShared
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActionActInstShared'
PRINT 'Removing Process Instances from _ActionActInstRights'
DELETE _ActionActInstRights
FROM _ActionActInstRights
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActionActInstRights'
PRINT 'Removing Process Instances from _ProcInst'
DELETE _ProcInst
FROM _ProcInst
INNER JOIN #TMP
ON _ProcInst.ID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInst'
PRINT 'Completed removing completed process instances from K2ServerLog for folder ' + @FOLDERNAME
DROP TABLE #TMP
Use K2ServerLog
PRINT 'Starting to remove Process Instances for ' + @FOLDERNAME
PRINT 'Getting List of Process Instances'
SELECT ID INTO #TMP2 FROM _ProcInst
WHERE PROCID IN
(
SELECT ID
FROM _PROC
WHERE PROCSETID IN
(
SELECT ID
FROM _PROCSET
WHERE FOLDER = @FOLDERNAME
)
)
PRINT 'Removing Process Instances from _ActInst'
DELETE _ActInst
FROM _ActInst INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInst'
PRINT 'Removing Process Instances from _ActInstAudit'
DELETE _ActInstAudit
FROM _ActInstAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstAudit'
PRINT 'Removing Process Instances from _ActInstData'
DELETE _ActInstData
FROM _ActInstData
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstData'
PRINT 'Removing Process Instances from _ActInstDataAudit'
DELETE _ActInstDataAudit
FROM _ActInstDataAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstDataAudit'
PRINT 'Removing Process Instances from _ActInstDest'
DELETE _ActInstDest
FROM _ActInstDest
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstDest'
PRINT 'Removing Process Instances from _ActInstDestData'
DELETE _ActInstDestData
FROM _ActInstDestData
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstDestData'
PRINT 'Removing Process Instances from _ActInstDestDataAudit'
DELETE _ActInstDestDataAudit
FROM _ActInstDestDataAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstDestDataAudit'
PRINT 'Removing Process Instances from _ActInstDestXml'
DELETE _ActInstDestXml
FROM _ActInstDestXml
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstDestXml'
PRINT 'Removing Process Instances from _ActInstDestXmlAudit'
DELETE _ActInstDestXmlAudit
FROM _ActInstDestXmlAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstDestXmlAudit'
PRINT 'Removing Process Instances from _ActInstSlotData'
DELETE _ActInstSlotData
FROM _ActInstSlotData
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstSlotData'
PRINT 'Removing Process Instances from _ActInstSlotDataAudit'
DELETE _ActInstSlotDataAudit
FROM _ActInstSlotDataAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstSlotDataAudit'
PRINT 'Removing Process Instances from _ActInstSlotXml'
DELETE _ActInstSlotXml
FROM _ActInstSlotXml
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstSlotXml'
PRINT 'Removing Process Instances from _ActInstSlotXmlAudit'
DELETE _ActInstSlotXmlAudit
FROM _ActInstSlotXmlAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstSlotXmlAudit'
PRINT 'Removing Process Instances from _ActInstXml'
DELETE _ActInstXml
FROM _ActInstXml
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstXml'
PRINT 'Removing Process Instances from _ActInstXmlAudit'
DELETE _ActInstXmlAudit
FROM _ActInstXmlAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInstXmlAudit'
PRINT 'Removing Process Instances from _EscInst'
DELETE _EscInst
FROM _EscInst
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _EscInst'
PRINT 'Removing Process Instances from _EventInst'
DELETE _EventInst
FROM _EventInst
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _EventInst'
PRINT 'Removing Process Instances from _IPC'
DELETE _IPC
FROM _IPC
INNER JOIN #TMP2
ON SrcProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _IPC'
PRINT 'Removing Process Instances from _LogBatch'
DELETE _LogBatch
FROM _LogBatch
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _LogBatch'
PRINT 'Removing Process Instances from _LineInst'
DELETE _LineInst
FROM _LineInst
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _LineInst'
PRINT 'Removing Process Instances from _ProcEscInst'
DELETE _ProcEscInst
FROM _ProcEscInst
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcEscInst'
PRINT 'Removing Process Instances from _ProcInstAudit'
DELETE _ProcInstAudit
FROM _ProcInstAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstAudit'
PRINT 'Removing Process Instances from _ProcInstData'
DELETE _ProcInstData
FROM _ProcInstData
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstData'
PRINT 'Removing Process Instances from _ProcInstDataAudit'
DELETE _ProcInstDataAudit
FROM _ProcInstDataAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstDataAudit'
PRINT 'Removing Process Instances from _ProcInstRevision'
DELETE _ProcInstRevision
FROM _ProcInstRevision
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstRevision'
PRINT 'Removing Process Instances from _ProcInstXml'
DELETE _ProcInstXml
FROM _ProcInstXml
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ActInst'
PRINT 'Removing Process Instances from _ProcInstXmlAudit'
DELETE _ProcInstXmlAudit
FROM _ProcInstXmlAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInstXmlAudit'
PRINT 'Removing Process Instances from _Worklist'
DELETE _Worklist
FROM _Worklist
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _Worklist'
PRINT 'Removing Process Instances from _ProcInst'
DELETE _ProcInst
FROM _ProcInst
INNER JOIN #TMP2
ON _ProcInst.ID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records removed from _ProcInst'
PRINT 'Completed removing completed process instances from K2ServerLog for folder ' + @FOLDERNAME
DROP TABLE #TMP2