CL 6.1 SMAT file extensions?

[Mary Kobis]

We just loaded CL 6.1 and our first load of inbound HL7 messages resulted in our IB/OB smat files with extension of .smatdb, .smatdb-shm, and .smatdb-wal. Anybody know what they are? I’ve looked around and  found that the smat setup for the site was set to “save into DB” and “data encryption enabled”. Would this be the cause? If so, why are they so different from previous versions? Thank you in advance of any help on the subject…Mary.

ps: I’m turning them off to see what happens.

Topic

[Mary Kobis]

Shut them off and I’ve got the usual .idx & .msg. Still what are they? and who uses them? Thanks!

[James Cobane]

I believe that is the sqlite database files when the database option for saved messages is enabled.

Jim Cobane

Henry Ford Health

[Mary Kobis]

thank you…

[Steve Williams]

Mary, this is a “new” feature built in to v6.1 and documentation is sparse.

It appears to be a site wide setting affecting all site processes and threads.

By default, new sites have this turned on. In the SiteInit GUI, the are check boxes at the bottom for activating/deactivating this feature (see screenshot 1).

In the Site Options GUI dialog, the a section at the top for changing the settings (see screenshot 2).

There’s also a new GUI tool called SMAT Database that provides some primitive access to saved messages in the SQLite db files. I’m still trying to figure it out.

SQLite is not a concurrent writable database, but for reading purposes, it works just fine. My guess is Infor is looking to solve the “SMAT refresh” issue with SMAT files by using SQLite queries which should “reread” the file data whenever a query is refreshed. Over time the tools should get better.

Meanwhile, SQL tools like AquaData Studio or SQLite Studio should be able to *slice and dice* the SMAT_db files with ease.

You can read this short document for information when to use SQLite and not: http://www.sqlite.org/whentouse.html

A resource on SQLite Studio is http://sqlitestudio.pl/docs/manual.pdf

[Terry Kellum]

This grew out of conversations a couple of years back regarding message archives.  IMHO, this will be a great help to the analyst in looking at HL7 files.  Imagine a TCL proc that reads that SQLite database table every 2 minutes and puts the transactions into a bigger database system.  You can then have a script that looks at the new transactions and creates index tables from the fields in the transaction.  Now imagine someone asks you what orders came from SCU between 9 and 10.  You type;

Code:

select * from transindex
where msh09 = ‘OMG^O19’
and pv103 like ‘SCU%’
and msh07 like ‘2014110609%’
order by msh07 desc

Want the messages?  Join in the raw message table, and you can view or export them.

No big deal.  I can get that from SMAT.  How about matching up Lab Orders to Lab Results to find unfulfilled orders?  Can your smat do that?

Code:

SELECT *
FROM (
SELECT obr.msgnum AS Message , msh07 AS OrderDate, orc04 AS OrderNumber, msh06 AS Fac, `obr04-4` AS Test
FROM outobr AS obr
LEFT JOIN outtrans AS tr ON tr.msgnum = obr.msgnum
LEFT JOIN outorc AS orc ON orc.msgnum = obr.msgnum
LEFT JOIN outindex AS idx ON idx.msgnum = obr.msgnum
WHERE `obr04-4` = ‘TACRO’
AND msh09 = ‘ORM^O01’
ORDER BY obr.msgnum DESC
) AS Orders
LEFT JOIN (
SELECT obr.msgnum AS Message , msh07 AS ResultDate, obr03 AS OrderNumber, msh06 AS Fac, `obr04-4` AS Test
FROM outobr AS obr
LEFT JOIN outtrans AS tr ON tr.msgnum = obr.msgnum
LEFT JOIN outorc AS orc ON orc.msgnum = obr.msgnum
LEFT JOIN outindex AS idx ON idx.msgnum = obr.msgnum
WHERE `obr04-4` = ‘TACRO’
AND msh09 = ‘ORU^R01’
ORDER BY obr.msgnum DESC
) AS Results ON Orders.OrderNumber = Results.OrderNumber
WHERE Results.Message IS NULL

[Jim Kosloskey]

I guess the real question is – should this new behavior be turned on by default?

My take is no – it will cause issues for those shops which are not yet ready to migrate.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Steve Williams]

Hi Terry,

  I was not criticizing the change. The inclusion of SMAT into SQLite is a good one. I was simply pointing out that Infor changed the “default” behavior “out of the box”. A process they have done in the past that tends to catch people off guard and they’ll need to learn a new skill. 🙂

[Terry Kellum]

I agree that it should not be the “Default” setting.  There is more that they need to do, first of which is to replace the full SMAT GUI Functionality (plus the catch-up they need to do) with the SQLite Backend.  That needs to include a quick way added to the GUI to generate the old style SMAT files out of the DB tables, and full training of the current analysts in the field.

THEN they might THINK about making it the default, but we are still a long way from that gate.

Still, If I had the new version, I would create a site with the DB option turned on and raw-route some data to play with.

[Alice Kazin]

You can use “hcismatconvert” to convert the database to .idx and .msg files.  

For example:   smatdb file name is “alicein.smatdb”

“hcismatconvert alicein -o alicein2”

Now you will have “alicein2.idx” and “alicein2.msg”.

[glen goldsmith]

The SQLite website discusses hthe -wal files (write ahead logs) and -shm (shared memory) files…

https://www.sqlite.org/tempfiles.html

[Peter Heggie]

Thank you – very helpful. So when using some kind of script to manage SQLite SMAT databases, we may not have to include them in the process.

Technically, we should not be ignoring them while a database is in use (at least one connection remains). It would be nice if we could shutdown the processes using them, ensure these files no longer exist (all connections closed cleanly) and then perform the maintenance (move them to long-term storage location). It is good that we do not have to compress them. It may not be easy to find a time to shutdown all threads that feed them, but if we separate them by thread, or by process, and not have one smat db per site, then that should make it easier to do a quick stop, archive and  start for each set.

Peter Heggie

[glen goldsmith]

Actually, you don’t have to do this.

When you issue a save_cycle command, either from the GUI or via hcicmd, it takes care of these tmp files.

These are not actual databases.

When you invoke the sqlite command on a DB that has these tmp files, they empty themselves and go away automatically, and gracefully.  (IE no lost data, the temp files populate the smatdb like it should)

What this means…… on the old .msg/.idx files, we have a live SMAT viewer, you could see transactions being saved in real time — it was useful in troubleshooting.  This won’t work on the new smatdb’s…..

[Max Drown (Infor)]

In my opinion, it’s going to be painful to not move to the new best practice model for SMAT introduced in CIS 6.1 and further improved in CIS 6.1.1. You’ll be fighting the current so to speak.

The combination of these 4 steps will make SMAT much more powerful that it has every been in the past. Management will by much easier. Performance is much faster. And searching is much better.

1. Enable SMAT DB (and hopefully encryption) under Site Options.

-- Max Drown (Infor)

[Keith McLeod]

I found that if I moved forward or backward in the smatdb, current messages would be there.  I just don’t see a refresh menu selection….

[Elijah Park]

We just upgraded to 6.1 this weekend and used SMAT DB right out the chute – we found searching across multiple files super helpful during testing.

The Java regex it uses takes a little getting used to. For example, if I wanted to search for an “&” after the PID segment and cross carriage returns, I would key this:

Code:

(?s)PID.*&

One question: Is there a way to run SQLite queries directly against the encrypted db files?

[Max Drown (Infor)]

Shoot me an email, and I’ll send you the instructions.

-- Max Drown (Infor)

[Author]

Replies