go to post Daniel Lee · Oct 2, 2019 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 recordsClass ACLIB.Utils.PurgeOrphanedMessages Extends %SYS.Task.Definition{ /* Task to purge any orphaned messagesOrphaned Message - Message data without corresponding header. Author: Daniel LeeDate: 2019-07-16*//// How many days of messages should not be purgedProperty 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 taskMethod OnTask() As %Status{ // These time variables are only for logging Set tTime = $ZHSet 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 - tTimeIf $$$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 deletedpDaysToKeep - number of days of records to keeppMaxCountToPurge - 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=0Set 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 FORSelect TOP :%tMaxCountToPurge ID, TimeCreated Into :%tID,:%tTimeCreated From EnsLib_HL7.Message Where TimeCreated < :%tDoNotDeleteDate Order By TimeCreated Asc) &sql(OPEN C1) For { &sql(FETCH C1) Quit:SQLCODE &sql(Delete From EnsLib_HL7.Message Where ID=:%tID) Set tDeletedCount=tDeletedCount+1Write !,tDeletedCount_"|"_%tID_"|"_%tTimeCreated } Set tCode=SQLCODE &sql(CLOSE C1) Set:'SQLCODE SQLCODE=tCodeSet pDeletedCount=tDeletedCountQuit: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.
go to post Daniel Lee · Feb 27, 2019 Thank you for all the excellent responses. The solution was far simpler. #;Do not include Cache.DAT as part of the sourceDB path, just folder path set impliedNamespace = "^^"_sourceDB New $NAMESPACE set $NAMESPACE = impliedNamespace This changes the namespace to an implied namespace and allows me to export the classes and routines. This documentation is what got me on the right path: https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...