Trying to understand Orphaned Messages

We are constantly running into issues where there are billions of Orphaned messages in our system that cause problems, and we have to manually run a cleanup to fix performance issues.

 In the following article about orphaned messages... https://community.intersystems.com/post/ensemble-orphaned-messages it mentions either programmatically eliminating the Orphaned messages or using a Utility like Demo.Util.CleanupSet in ENSDEMO.

I have had it explained to me is basically all messages have to go somewhere, if they aren't then it creates orphaned messages. 

So how should I go about getting rid of the problematic code that is causing the Orphaned messages either in a Business Router, or a Business Process?

Is there a way we can automate the cleanup process in the Task scheduler to run every so often so we don't have to run it manually? Does anyone have some helpful hints or suggestions to go about doing this? I understand the code, just the implementation of how to do it is what I am having problems with.

 

Thanks

Scott

  • 0
  • 0
  • 77
  • 5
  • 1

Answers

Scott, 

I have the same problem as well. The EnsLib_HL7.Messages had records going back years. While I have not found the offending code that creates the orphaned records, I do have a task that purges the records. We happen to have multiple databases and namespaces, so I added this to a general library database called ACLIB. By doing this I can use the same task code to target multiple namespaces. 

I based my code by tracing through some of the InterSystems purge code. 

Include (Ensemble, %sySite)

/// Deletes EnsLib_HL7.Message records without corresponding header resulting in orphaned records
Class ACLIB.Utils.PurgeOrphanedMessages Extends %SYS.Task.Definition
{

/* Task to purge any orphaned messages
Orphaned Message - Message data without corresponding header. Author: Daniel Lee
Date: 2019-07-16
*/
/// How many days of messages should not be purged
Property NumberOfDaysToKeep As %Integer(MINVAL = 0) [ InitialExpression = 30 ]; Property MaxCountToPurge As %Integer(MINVAL = 0) [ InitialExpression = 10000 ]; /// Which NameSpace to execute the query to delete messages

/* Note that you will want to modify the namespace list or exclude it and modify the code if you only have one namespace*/
Property SourceNamespace As %String(DISPLAYLIST = ",HIE,IMMUNIZATION,PHI,PM,RESULTS", VALUELIST = ",hie,immunization,phi,pm,results") [ Required ];

/// The OnTask() Method is called to execute the task
Method OnTask() As %Status
{

// These time variables are only for logging 
Set tTime = $ZH
Set tDisplayTime = $ZDATETIME($HOROLOG,1,1,9)
Set tDeletedCount = -1


Write tDisplayTime_": Begin purge orphaned messages for "_..SourceNamespace_"."


Set tSC = ..PurgeOrphaned(.tDeletedCount, ..NumberOfDaysToKeep, ..MaxCountToPurge, ..SourceNamespace)
Set tTime = $ZH - tTime
If $$$ISOK(tSC) {
Set msg = "Purged "_tDeletedCount_" records, keeping the last "_..NumberOfDaysToKeep_" days in "_tTime_"s" 
$$$LOGINFO(msg)
Do $$$LOGMSG(msg,0,1)
Write !,msg
}
else
{
Set msg = "Error purging"_..SourceNamespace_" keeping the last "_..NumberOfDaysToKeep_" : "_ $$$StatusDisplayString(tSC)
$$$LOGERROR(msg)
Do $$$LOGMSG(msg,0,3)
Write !,msg
}
Quit tSC
}

ClassMethod PurgeOrphaned(Output pDeletedCount As %Integer, pDaysToKeep As %Integer = 30, pMaxCountToPurge As %Integer = 100000, pNameSpace As %String) As %Status
{
/*
pDeletedCount - number of rows deleted
pDaysToKeep - number of days of records to keep
pMaxCountToPurge - number of records to delete at any one time, added to keep system from timing out */
New %tID,%tMaxCountToPurge Set %tMaxCountToPurge = pMaxCountToPurge, %tID=""

//This method to get the date was adapted from Ens purge code ... 
New %tDoNotDeleteDate Set %tDoNotDeleteDate = $$$timeUTCHtoUTC($s($ztimezone'<0:($H-pDaysToKeep+1)_","_($ztimezone*60),1:($H-pDaysToKeep)_","_($ztimezone*60+86400)))

Set SQLCODE=0, pDeletedCount=0, tDeletedCount=0, tCursorRowCount=0
Set tNamespace = $NAMESPACE //Used to return to previous namespace
Set $NAMESPACE = pNameSpace //Grab oldest records that exist prior to the time created value as these are orphaned records without headers
 

&sql(DECLARE C1 CURSOR FOR
Select TOP :%tMaxCountToPurge ID, TimeCreated Into :%tID,:%tTimeCreated From EnsLib_HL7.Message 
Where TimeCreated < :%tDoNotDeleteDate 
Order By TimeCreated Asc) &sql(OPEN C1For 
&sql(FETCH C1)  
Quit:SQLCODE


&sql(Delete From EnsLib_HL7.Message Where ID=:%tID)


Set tDeletedCount=tDeletedCount+1
Write !,tDeletedCount_"|"_%tID_"|"_%tTimeCreated
Set tCode=SQLCODE &sql(CLOSE C1Set:'SQLCODE SQLCODE=tCode
Set pDeletedCount=tDeletedCount
Quit:SQLCODE&&(SQLCODE'=100) $$$ERROR($$$EnsErrGeneral,"Purge error at ID "_%tID _"; SQLCODE = "_SQLCODE)
Set tSC = (SQLCODE=100) &sql(CLOSE C1)
Set $NAMESPACE = tNamespace Quit tSC
} }

 

Note that I kept my SQL statement very simple. The full SQL statement to check for orphaned messages is here:

SELECT msg.TimeCreated, COUNT(msg.Id) FROM EnsLib_HL7.Message msg LEFT JOIN Ens.MessageHeader hdr ON msg.Id=hdr.MessageBodyId WHERE hdr.MessageBodyId IS NULL AND msg.Name NOT LIKE '%ACK%' ORDER BY msg.TimeCreated ASC

I tried to format this code by using the ObjectScript button but it looked ugly in the preview. 

Hopefully, this helps. 

Comments

Does the basic purge work for orphans? I thought that worked off the Header that the orphans don't have?

From what I read is the basic purge does not take care of the orphaned messages. That's why there is this manual step we have to configure.

In my case, we already have standard purge tasks but were still left with orphaned messages. I seem to recall InterSystems support telling me that the standard purge tasks work from the header. 

As I understand it, anywhere in code that you have an EnsLib.HL7.Message object that is created and saved, but not sent with a send request, it won't create the header and makes it an orphan. If you look through any of your custom code for anywhere you create one with a %New, or an ImportfromString or ImportfromStream, that might help track down where you are generating them from. One place you might look is if you have archiveIO turned on for anything. The messages that are generated from that tend to be orphans for me.  I have a process that is similar to Daniel's for getting rid of them.