Homepage › Clovertech Forums › Read Only Archives › Cloverleaf › Cloverleaf › CL 6.1 SMAT file extensions?
- This topic has 16 replies, 11 voices, and was last updated 9 years, 1 month ago by Max Drown (Infor).
-
CreatorTopic
-
November 3, 2014 at 11:03 pm #54450Mary KobisParticipant
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.
-
CreatorTopic
-
AuthorReplies
-
-
November 3, 2014 at 11:06 pm #81516Mary KobisParticipant
Shut them off and I’ve got the usual .idx & .msg. Still what are they? and who uses them? Thanks!
-
November 3, 2014 at 11:38 pm #81517James CobaneParticipant
I believe that is the sqlite database files when the database option for saved messages is enabled.
Jim Cobane
Henry Ford Health
-
November 4, 2014 at 5:14 pm #81518Mary KobisParticipant
thank you…
-
November 5, 2014 at 5:06 pm #81519Steve WilliamsParticipant
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
-
November 6, 2014 at 3:07 pm #81520Terry KellumParticipant
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 descWant 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 -
November 6, 2014 at 4:30 pm #81521Jim KosloskeyParticipant
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.
-
November 6, 2014 at 5:25 pm #81522Steve WilliamsParticipant
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. 🙂
-
November 6, 2014 at 5:46 pm #81523Terry KellumParticipant
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.
-
December 15, 2014 at 6:04 pm #81524Alice KazinParticipant
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”.
-
June 25, 2015 at 10:16 pm #81525glen goldsmithParticipant
The SQLite website discusses hthe -wal files (write ahead logs) and -shm (shared memory) files…
-
June 26, 2015 at 1:09 pm #81526Peter HeggieParticipant
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
-
June 26, 2015 at 4:25 pm #81527glen goldsmithParticipant
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…..
-
June 29, 2015 at 10:34 pm #81528Max Drown (Infor)Keymaster
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)
-
June 30, 2015 at 12:16 pm #81529Keith McLeodParticipant
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….
-
August 17, 2015 at 5:09 pm #81530Elijah ParkParticipant
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?
-
August 17, 2015 at 5:12 pm #81531Max Drown (Infor)Keymaster
Shoot me an email, and I’ll send you the instructions.
-- Max Drown (Infor)
-
-
AuthorReplies
- The forum ‘Cloverleaf’ is closed to new topics and replies.