Is there a way to programmatically delete suspended messages?

Question

Question

Kirsten Whatley · Feb 14

#Health Connect

Is there a way to delete suspended messages rather than using the WebMgmt gui?

We have over three million in one namespace, and using the gui I can only do 2000 at a time.

I was thinking of using delete in sql like Ens.MessageHeader where Status = 'Suspended'

But I don't know if that is a clean way to do it.

Product version: IRIS 2023.1

$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2023.1.4 (Build 580_0_23807U) Mon Nov 25 2024 18:17:37 EST [HealthConnect:5.1.0-3.m4]

Discussion (15)4

Log in or sign up to continue

Enrico Parisi · Feb 14

Whatever you use, portal, code or SQL, I'd suggest NOT to delete the suspended messages. If you delete a message, only the message header will be deleted, leaving all the associated requests/responses orphaned.

I suggest discarding the messages using SQL.

Be careful doing so with a single update SQL statement for 2M messages, with more that 1000 records lock escalation will lock the entire Ens.MessageHeader table/class and your production will have big trouble. To avoid it use %NOLOCK.

Using Display mode:

Update %NOLOCK Ens.MessageHeader set Status ='Discarded' where Status = 'Suspended'

Using Logical or ODBC mode:

Update %NOLOCK Ens.MessageHeader set Status = 4 where Status = 5

2 0

Jeffrey Drumm · Feb 15

The class Ens.MessageHeader has a classmethod Purge() that deletes message headers and bodies based on the number of days to keep along with a few other criteria; those are used in an SQL query to select the set of messages to purge. That query along with the associated purge code should work as an example to see what's involved in carefully removing messages without collateral damage ... search table indices need to be maintained, for example.

There's also the Ens.Util.MessagePurge task definition that is used for scheduled purges, it has much of the same code but offers a multi-threaded purge feature that leverages the work queue.

@Enrico Parisi's solution moves the actual purge of the messages off to the scheduled Ens.Util.MessagePurge task for messages that exceed the DaysToKeep limit, as long as that is configured to delete bodies too. This is likely the safer solution and requires significantly less effort 😉

2 0

Vic Sun · Feb 24

On a bit of a tangent but I might recommend reviewing the following post by my colleague and considering whether a 2 phase purge schedule would make sense for you. One purge task running as standard to clean up the completed messages and one running on a lag to catch any suspended / leftover messages that are old enough that you think they could be removed:

https://community.intersystems.com/post/why-keep-integrity-important-when-purging-healthshareensemble-data

0 0

score 0 · Answer 1 · 2025-02-17T14:24:19-05:00

Thank you for the answers! I have a follow up question.

I have been deleting suspended messages already. So how do I go about cleaning up the orphans I've created?

For instance, in one namespace where I know I deleted some suspended messages (and where all messages older than 7 days are supposed to have been purged), I ran this query:

select * from Ens.MessageHeader nolock
where timecreated < '2025-02-11 00:00:00.000'
order by timecreated

I get 18298 messages - under four distinct session ids.

How can I mark these so that will be purged? Or is there some other method of purging these orphans since I deleted their message header?

Note: These are the settings on our Purge task:

score 0 · Answer 2 · 2025-02-17T12:08:50-05:00

Thank you for the answers! I have a follow up question.

I have been deleting suspended messages already. So how do I go about cleaning up the orphans I've created?

For instance, in one namespace where I know I deleted some suspended messages (and where all messages older than 7 days are supposed to have been purged), I ran this query:

select * from Ens.MessageHeader nolock
where timecreated < '2025-02-11 00:00:00.000'
order by timecreated

I get 18298 messages - under four distinct session ids.

How can I mark these so that will be purged? Or is there some other method of purging these orphans since I deleted their message header?

Note: These are the settings on our Purge task:

score 0 · Answer 3 · 2025-02-17T14:53:28-05:00

If you're certain that you want to have those messages purged, you can run an UPDATE query against Ens.MessageHeader using the same criteria, but setting the Status field to 4 (Discarded).

Status	ID
Created	1
Queued	2
Delivered	3
Discarded	4
Suspended	5
Deferred	6
Aborted	7
Error	8
Completed	9

score 0 · Answer 4 · 2025-02-17T17:00:56-05:00

@Jeffrey Drumm , it's too late, he has already deleted the headers so the bodies are already orphaned.

@Kirsten Whatley , there is no query using the Ens.MessageHeader that can return orphaned message bodies info/reference. The very definition of orphaned messages is that have lost the link form Ens.MessageHeader.

Do you know the MessageBodyClassName of the messages you have deleted?
If it's a custom defined persistent class, can you provide some detail of the message(es) class(es)?

score 0 · Answer 5 · 2025-02-17T17:21:57-05:00

@Enrico Parisi,

I assumed that she wanted to purge the messages returned by this query:

select * from Ens.MessageHeader nolock
where timecreated < '2025-02-11 00:00:00.000'
order by timecreated

If they'd already been purged, there would be no headers returned.

score 0 · Answer 6 · 2025-02-21T11:37:16-05:00

In the process of working on this, I discovered that the purge task for the namespace has been erroring, which muddied the waters for me. I had been assuming that the scads of messages I was seeing in that query were the orphans, but I realize now that is not the case.

https://wrc.intersystems.com/wrc/eucustprob2.csp?OBJID=996659

score 0 · Answer 7 · 2025-02-21T11:35:29-05:00

I am pretty sure that the bulk of the orphans are this message class:

Class AH.AHLIB.Custom.NextGate.Message.Patient Extends (Ens.Request, %Persistent, %JSON.Adaptor, %XML.Adaptor)

{

Parameter %JSONIGNOREINVALIDFIELD = 1;

Property header As AH.AHLIB.Custom.NextGate.Message.Patient.Header;

Property event As AH.AHLIB.Custom.NextGate.Message.Patient.Event;

Property entity As list Of AH.AHLIB.Custom.NextGate.Message.Patient.Entity;

Property additionalReferences As AH.AHLIB.Custom.NextGate.Message.Patient.AdditonalReferences;

Storage Default

{

<Subscript>"Patient"</Subscript>

<Value>header</Value>

</Value>

<Value>event</Value>

</Value>

<Value>entity</Value>

</Value>

<Value>additionalReferences</Value>

</Value>

</Data>

<DefaultData>PatientDefaultData</DefaultData>

<Type>%Storage.Persistent</Type>

}

score 0 · Answer 8 · 2025-02-22T06:58:04-05:00

You can query the message body class table with a left join to Ens.MessageHeader to get the orphaned message body identifiers :

select 
  %NOLOCK bod.Id
from
  MySample.BodyTable bod
  left join Ens.MessageHeader hdr on bod.Id=hdr.MessageBodyId and hdr.MessageBodyClassName='MySample.BodyTable'
where
 hdr.MessageBodyId is NULL

score 0 · Answer 9 · 2025-02-18T08:19:00-05:00

Hi Kirsten,

You can use the method DeleteMessage() as explained in the doc: https://docs.intersystems.com/irislatest/csp/documatic/%25CSP.Documatic....

score 0 · Answer 10 · 2025-02-18T11:39:04-05:00

@David.Satorres6134 , in the class EnsPortal.SuspendedMsg, DeleteMessage() is an instance method and cannot be invoked directly.

Anyway, what it does is simply invoking:

Set tStatus = ##class(Ens.MessageHeader).%DeleteId(pHeaderId)

But, as explained in my previous post, this leave orphaned messages and should be avoided.

score 0 · Answer 11 · 2025-02-27T11:18:35-05:00

Kirsten Whatley · Feb 27

This is intriguing and might be really helpful, thank you!!

0 0

score 0 · Answer 12 · 2025-03-12T17:16:02-04:00

Glad you found that interesting! One warning I can add is that if you do decide to add a "no-integrity" type purge and if you've never run one, depending on how many messages you have you may trigger a very large purge (and a lot of journaling activity as a result). Often times when making significant purge changes, it can be safer to increment the time period you are purging bit by bit to make sure you're not asking too much of a single purge.

ex. If you run daily message purges then every day you are purging 1 day's worth. If you have "expired" messages going back a few years and decide to purge them all of a sudden, that might be a surprising number.