CL 6.1 SMAT file extensions?

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

Jim Cobane

Henry Ford Health

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

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;

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?

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.

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. 🙂

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.

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”.

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

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

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.

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…..

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.

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….

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:

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

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