Hi David,
Data archiving will indeed improve performace; Especially in workorder, workflow transactions and Message Tracking. For example, if the inbound/outbound payload has attachments, then filtering record in Message tracking would be burdensome.
For Archiving, we can take full database backup, then delete old records. or Delete and insert the records to archive database using SQL job.
In any case, 'how we filter' the records is crucial. Because workorders, workflow transactions etc. may have reference to other active records.
Here are few suggestions to filter.
| Table |
Condition |
| LOGINTRACKING |
attemptdate <'SpecificDate' |
| WORKORDER |
historyflag=1 and reportdate<'SpecificDate' |
| AUDITTABLES |
eaudittimestamp<'SpecificDate' |
Archiving WorkFlow transactions is tricky. We need to consider the order of deletion and related tables. Here's a script example (MS-SQL) to delete workflow tables such as WFCALLSTACK, WFASSIGNMENT etc.
---Clearing WorkFlow Transaction---
declare @dateVar date;
set @dateVar=(SELECT DATEADD(month, -60, GETDATE()));
DELETE FROM [WFCALLSTACK]
WHERE WFID IN (SELECT WT.WFID FROM WFTRANSACTION WT (nolock)
JOIN [WFINSTANCE] WI (nolock) ON WT.WFID = WI.WFID
WHERE WI.ACTIVE = 0 AND WT.TRANSDATE <= @dateVar
AND WT.MEMO IS NULL
);
DELETE FROM [WFASSIGNMENT]
WHERE WFID IN (SELECT WT.WFID FROM [WFTRANSACTION] WT
JOIN [WFINSTANCE] WI ON WT.WFID = WI.WFID
WHERE WI.ACTIVE = 0 AND WT.TRANSDATE <= @dateVar
AND WT.MEMO IS NULL);
DELETE FROM [WFINSTANCE] WHERE WFID IN (
SELECT WFID FROM [WFTRANSACTION] (nolock) WHERE MEMO IS NULL
AND TRANSDATE <= @dateVar)
AND ACTIVE = 0;
DELETE FROM [WFTRANSACTION] WHERE WFID NOT IN
(SELECT WFID FROM [WFINSTANCE] (nolock)
WHERE ACTIVE = 1) AND TRANSDATE <= @dateVar
AND MEMO IS NULL;
Thanks!
------------------------------
Sankar Ganesh V S
DXC Technology
------------------------------