EnsLib.HL7.Message Querying

Question

Question

Scott Roth · Mar 18, 2022

We are trying to track down the source of Orphaned messages and noticed that we are unable to query EnsLib.HL7.Message with any kind of WHERE or ORDER BY clauses in our SQL statement.

I know EnsLib.HL7.Message is a system table, but is there a way we can add additional Indexes to the table to make the query run better/faster without affecting the system?

Product version: Caché 2018.1

$ZV: Cache for UNIX (IBM AIX for System Power System-64) 2018.1.3 (Build 414U) Mon Oct 28 2019 11:24:02 EDT

Discussion (4)2

Log in or sign up to continue

Jeffrey Drumm · Mar 19, 2022

Hi Scott,

You can get a list of message body IDs (and their associated event types) that have no corresponding header record with this query:

SELECT HL7.ID, HL7.Name FROM EnsLib_HL7.Message HL7 LEFT JOIN Ens.MessageHeader hdr ON HL7.Id=hdr.MessageBodyId WHERE hdr.MessageBodyId IS NULL AND HL7.OriginalDocId IS NULL

And yes, this query can take a very long time to run ... long enough that it will usually time out in the Management Console SQL UI. It shouldn't time out if you run it from the Caché (or IRIS) SQL Shell though.

What would you want to use for WHERE or ORDER BY criteria? The list of "fields" (properties) available to reference directly via SQL can be viewed in the Body panel of an HL7 message displayed in the Message Viewer, but their usefulness in determining what made them orphans is limited.

The problem with orphans is that knowing "where they came from" is a bit challenging. The source and destination services/processes/operations in the Production are stored in the message header table and not the body table; the reason the messages are orphans is because they're no longer linked (by MessageBodyId) to any records in the Message Header table.

The most common reason for orphans is the configuration of the message purge task. There's a "bodies too" checkbox that, if left unchecked, will prevent message bodies from being deleted. The headers still get deleted, though, and that makes the bodies "orphans."

0 0

score 0 · Answer 1 · 2022-03-19T13:45:47-04:00

Believe what you’re after us a query like this:

select *

from enslib_hl7.message as hl7

where hl7.id not in (select messagebodyid from ens.messageheader where messagebodyclassname = ‘EnsLib.HL7.Message’)

apologies for poor formatting, typing on a phone while a kiddo naps. But that should help identify the message bodies that are orphaned.

score 0 · Answer 2 · 2022-03-21T08:40:26-04:00

Yeah tracking down the messages is the hardest part in trying to figure out what is causing it. I am trying to hunt down the problems by using the TimeCreated from EnsLib.HL7.Message and looking for messages around the timeframe of the message in question.

Others have told us that a message always needs to go somewhere, so does that mean if we use a "WHEN" statement within a Router that we should use OTHERWISE too if the message hits the rule but it is not used?

score 0 · Answer 3 · 2022-03-21T08:53:13-04:00

If a message fails to match any rules in a routing rule, it gets marked as "Completed." It still went somewhere; its final destination was the Business Process running the routing rule