Script for Cycling SMAT database

[David Speare]

We are upgrading to 6.1 does anyone have a cycle SMAT database script that they might share?

Topic

[Brent Fenderson]

David, I’m sure everyone has some variation of the hcicyclesavemsgs.pl found in the contrib folder. Cycling smat is the same function. You”ll just need to manage the *.old.smatdb files.

[Keith McLeod]

If you disable all SMAT DB configurations, you can essentially continue to use your current form of cycling the saved messages.  If you choose to use SMATDB, it appears that you must first configure in the site options and then in the process configuration within your network configuration tool select Automatic SMAT Cycling and establish a threashhold in KBytes.  This appears to be the ruling configuration from my observations.  It moves the SMATDB to SmatHistory under each process.  The file will use a time stamp in its name.

You will probably need to query differently for daily stats if you were accustomed to cycling on a fixed period…

I am still exploring this new method and welcome others advice and feedback.

[Peter Heggie]

Can you move SMAT DB files from SmatHistory to another folder, and be able to search and display messages from that other folder? Or at least copy them back into SmatHistory for searching and viewing?

We want the older content to be backed up on another file system. So far, in just casual testing, we are unable to use the GUI Smat DB search & view functions on Smat DB files that are moved from SmatHistory to another folder. Maybe it is the way we are moving the files.

Does anyone know what each individual Smat DB file type is used for and how it should be treated?

smatdb

smatdb-shm

smatdb-wal

?

Peter

Peter Heggie

[Keith McLeod]

Looks like you are bound to this folder structure in order to use the SMATDB GUI.  Of course you could make use of symbolic links to establish SMATDBs on a separate file system.

[Peter Heggie]

If we used symbolic links, then it would have to be done for SmatHistory folder itself?

I second the idea of having a webinar devoted to Smat DB, for someone to gather and distill all the knowledge and recommendations of using Smat DB and share them, and also allow questions and answers.

Peter Heggie

[Keith McLeod]

Another option would be to create a link at the process directory level.

[Peter Heggie]

We already have each site on another file system, with the soft link established under $HCIROOT. We wanted to have ~tertiary~ locations for very old smat files. We are reviewing our entire setup with our move to 6.1. Currently we use hcicyclesavemsgs to copy and compress SMAT files to ./Inboundsave, then run nightly scripts to move 15 day old SMAT files to a secondary location, and another nightly script to move 30 day old SMAT files to a tertiary location. Each of these locations is on a different file system. I’m not really sure what the value of this setup is – it pre-dated me here. I’m guessing it is because the older the file is, the less likely we will have to access it and that may help the company’s data storage software/backup solution (ex: TMS) avoid backing up files. But thinking more about it, that idea is kind of weak – TMS or other solutions are pretty efficient, so the location should not matter.

Does anyone have any good reasons why anyone would need more than one backup location for SMAT databases?

Peter Heggie

[James Cobane]

I was playing with this a little bit in our DEV environment.

[Peter Heggie]

so did you manually (scripted) move or copy the SMAT DB files out of SmatHistory into your linked /logs folder?

Peter Heggie

[James Cobane]

We don’t have the SMAT History turned on, so currently the cycle just creates the .old files in the exec/processes/xxx directory.  The script runs the cycle save commands, then copies any .old files to the /logs/ddd directory (where ddd is the respective day of the week – Mon, Tue…).  But the script could be modified to move the files from the SmatHistory folder, if needed.

Jim Cobane

Henry Ford Health

[Alice Kazin]

We are using the Smat Databases with Smat history enabled.  However, we did not configure “automatic smat cycling” under the process configuration.  We use a script to cycle save the smat files daily and compress them.  We liked the idea of the cycled Smat files having a date time stamp.  Then you don’t have the issue of one “.old” file overlaying another.

[Peter Heggie]

Does your script move the SMat DB files to another directory outside of ./SmatHistory? If so, are you able to uncompress them and search them using the GUI?

Peter Heggie

[Alice Kazin]

Right now we are using SmatHistory.  We are working on plan to move archved Smat database files to separate box.   You can unzip the files in SmatHistory and search them with the SmatDatabase tool.

[Keith McLeod]

I just created a subdirectory under SmatHistory.  It shows up in the GUI with check boxes.  This leads me to believe that you could archive to either a subfolder or across file systems with a symbolic link. I would guess that your directory would have to at least be under the root of where the Smat GUI allows you to point to.

[Peter Heggie]

looks good – I tried the same thing, moved smat dbg files into the new sub folder and was able to search them using the GUI.

Not sure why I couldn’t get it to work before.

Still don’t know what the *-shm and *-wal files are for – I deleted them and was still able to search and display.

But this holds promise for flexibility.

Peter Heggie

[James Cobane]

Attached is our version of a hcicyclesavemsgs script.  It is configured to handle the .old files as well as files under SmatHistory.  You may find it helpful in creating your own version.  Usage is simply to setsite prior to running the script, then run the script.  We have this configured in cron to run for each site; when it moves the files, it prefixes them with the name of the site.

Hope this helps.

Jim Cobane

Henry Ford Health

[Peter Heggie]

thank you – I’ll look at this. We would add something to only move files that are X days old.

Does it help to compress Smat DB files? Should we use SQLite vacuum command?

Peter Heggie

[James Cobane]

I don’t think there would be any advantage to running the SQLite VACUUM command as you aren’t really deleting any records from the SMATDB files.  Compressing/zipping them with ‘compress’ or ‘gzip’ may save some space, but you would have to try it and see.

Jim Cobane

Henry Ford Health

[Max Drown (Infor)]

Out of curiosity, (for those who aren’t) how come you aren’t using the built-in cycling combined with an archive tool for moving old data off the disk? It’s much easier to manage. SMAT DB searches across files, so timed archives (ex. every day at 1am) are no longer needed.

Cloverleaf will watch log files and SMAT files and move them to the LogHistory and SmatHistory sub directories when they reach the specified file size. The Site Options contain settings for pruning these two sub-directories.

For example, set the processes to auto cycle logs and SMAT files at 10mb. Set the LogHistory options to only keep 100 files (the oldest file will automatically be deleted). Set the SmatHistory options to keep 90 days worth of data; data older than 90 dates is automatically deleted. Set your archive software to watch these directories and backup data off disk to wherever you’d like. Adjust the numbers to your needs.

This allows you to predict pretty accurately how much disk you’ll need with older data archived off-disk. Using SMAT DB, it’s very easy to search across multiple SMAT files.

-- Max Drown (Infor)

[James Cobane]

Since we haven’t upgraded to 6.1 yet, we don’t have the SMAT DB to search across multiple SMAT saves.  We like to keep a nice clean snapshot of each day, so cycling by size might not generate a save for the day if the file is not large enough; and setting it too low would create multiple files to search through (on file-based SMAT).  A nice enhancement would be to allow a ‘daily’ auto-cycle, instead of just based on file size.

Hope that makes sense.

Jim Cobane

Henry Ford Health

[Alice Kazin]

We are also looking for a daily “auto cycle”.  We are gzipping our archived Smat database files and it makes a big difference.

[Peter Heggie]

Just another comment – We do want to keep 90 days worth of Smat data, but I’m not sure we want to search through all of it to retrieve messages that are only a few days old.

So I’m thinking about using the site options to specify archiving the Smat DB to /SmatHistory after one day and using a script to move and compress the Smat DB files to another file system after 7 days.

This gives us 7 days worth of Smat data to search through. If older data is desired, it would have to be manually copied back and uncompressed into /SmatHistory.

I am thinking that having 90 days worth of Smat data will require a much longer search time than 7 days worth of data. So that is why I’d like at least three different retention thresholds – 1 day, 7 days and 90 days.

If 7 days worth of data (approximately 100,000 messages per day), is searchable without appreciable delay, then I’ll keep 7 days worth in /SmatHistory, and move older content somewhere else. And I’ll have to set the archive to occur after 1 day, so that a script can easily find the files older than 7 days and move them.

Unless you can tell me that searching 90 days worth of data is not appreciably longer than 7 days worth.

Peter Heggie

[Max Drown (Infor)]

Peter, I sent that on to R&D. Good ideas.

-- Max Drown (Infor)

[Peter Heggie]

Thank you

And just to clarify – I think searching seven days, based on comments so far, will not be significantly longer than one day. So we will always select both the process directory and the SmatHistory sub-directory.

And to get the ’round-trip’ or complete life-cycle view, we will frequently select all Smat DB files under the /exec/processes folder. Hopefully this will perform well.

So as long as we can uncompress and move files back into /SmatHistory, we can use scripts to move back and forth.

But it is a nice-to-have to get additional built-in functionality to help us move and/or compress to a true archive (non-searchable) directory.

Also, we expect to use this approach from Global Monitor, and hope to turn over searching and reporting functions to users that are not Cloverleaf administrators, so we would like to keep the process of  selecting Smat DB Files as simple as possible.

Peter Heggie

[Max Drown (Infor)]

Peter, here’s the response from R&D.

Good Suggestions. I

-- Max Drown (Infor)

[Peter Heggie]

That is exactly what I had in mind. Good to know about the compress result too!

thank you

Peter Heggie

[Alice Kazin]

Note: for SMAT DB, the compressed file is larger than the raw file. So no need to compress.  I don’t think this is correct.

From our experience, the compressed (gzipped) SmatDB file was smaller than the raw file.

[Keith McLeod]

Still learning the ins and outs of SMATDB.

[Max Drown (Infor)]

Keith, that would be an excellent enhancement request to submit via Infor Xtreme. I have also passed the question on directly to R&D.

-- Max Drown (Infor)

[James Cobane]

From Keith’s post, it indicates there is a limit on the number of SQLite databases.  Can you expand/explain what that limitation is?  Is it the number of database files that you can search on via the tool?  Am I going to have an issue if I have more than 59 database files for a given thread?

For example, if I am cycling the SMAT file for a thread based on size and I create more than 59 history files for that thread, I won’t be able to search across all of them?

Any additional info is appreciated.

Thanks,

Jim Cobane

Henry Ford Health

[Keith McLeod]

I have seen  both numbers of 59 and 62.  I believe the 59 was in the release notes and 62 was a SQLite limitation.  It would give me an error when more than 59 were selected in my search.

Besides experiencing the error, if I can find the wording that confirmed it, I will add it to a post.

[Bob Schmid]

All the more reason to hold on to existing flat idx smat architecture while Infor “whittles” away at integrating this architecture into Cloverleaf with efficient management of the system provided.  

whittle

[ ˈ(h)witl ]

VERB

carve (wood) into an object by repeatedly cutting small slices from it.

synonyms: pare

[Max Drown (Infor)]

The limit is on the number of smat db files that can be searched at the same time. There is no limit on the number of smat files that can be configured and used by Cloverleaf, just like there is no limit on the number of smat flat files that can be used. You can select for searching all of the smat db files in a site or a process for example. There is an AR in  to increase or eliminate this limit.

-- Max Drown (Infor)

[Jeff Dinsmore]

Here’s a link to SQLite’s discussion of max database files that can be ATTACH’ed to a given DB connection.

<a href="https://www.sqlite.org/lang_attach.html&#8221; class=”bbcode_url”>https://www.sqlite.org/lang_attach.html

Jeff Dinsmore
Chesapeake Regional Healthcare

[Keith McLeod]

Here is what that previous link takes you to regarding attaced Databases.

Maximum Number Of Attached Databases

The ATTACH statement is an SQLite extension that allows two or more databases to be associated to the same database connection and to operate as if they were a single database. The number of simultaneously attached databases is limited to SQLITE_MAX_ATTACHED which is set to 10 by default. The maximum number of attached databases cannot be increased above 125.

The maximum number of attached databases can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_ATTACHED,size) interface.

[Jeff Dinsmore]

OK… So, it sounds as if Cloverleaf has probably compiled SQLite with a max attach value that’s different than the default of 10.

Let’s say, for the sake of discussion, that it’s set to the SQLite-imposed max of 125.  I’m currently keeping 120 days in my current active SMAT store. At one file per day, that would just do it for my current environment.

Most of the searching I do, however, is limited to the most recent several days – but I’m not really sure when a particular event occurred – whether it was yesterday or late last week…

One approach I’ve used successfully in the (non-Cloverleaf) past is to search through archives in reverse chronological order and present the pertinent results of the search to the user as they’re matched.  When the user sees what they perceive to be enough found messages, they can cancel the search and look at what’s been found.

Something like this pseudo-code:

Code:

set startDate “beginning of time”
set endDate “now”
while { $endDate >= $startDate && ! $cancelled } {
 open smatDB(endDate)
   search for “search criteria” in the currently open smatDB
     >> present any matches found to user
 close smatDB(endDate)
 incr endDate -1
}

This allows a default end date of “now” and an undefined start date.  The search walks back through time until it’s stopped by the user.  You can, of course, specify start/end date/time, but it’s handier to just let the search walk back through time until you’ve found what you want.

This approach would eliminate the max attached DB limitation altogether since they’d be opened, then closed one at a time.

Jeff Dinsmore
Chesapeake Regional Healthcare

[Terry Kellum]

The ability to work with these databases using TCL is really quite robust.

My preference would be to automate the management of the SMAT data lifecycle by processing the SMAT transactions into a master database that could be managed by timestamps.  If you prefer, you could spin out the raw trans to a file before deleting from the database.  If you use large files and are adding/deleting routinely, make sure you look at the VACUUM command in SQLite also.

[glen goldsmith]

When you compress a smatdb and it doesn’t get smaller — its encrypted.

You can’t compress encrypted files.

Currently – we have a perl script that cycles all the smatdb’s daily.

We have SMAT History turned on.

We have another script that archives any files in the SmatHistory that are +7 days old.

These are compressed with 7za (7zip) (which is much better than gzip… 3 or 4 times better, but a tad slower)

The encryption option, causes me wrinkled nose expression:

1.

[Author]

Replies