What are the advantages of using SQLite with Cloverleaf?

[Michael Brande]

What are the advantages of using SQLite with Cloverleaf?

Our current Hospital houses about 2-3 million transactions a day.

Topic

[Jim Kosloskey]

In what regard?

To do normal integrations I don’t think there is a need to use SQL Lite.

But maybe you are thinking of some other use with Cloverleaf.

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

[David Coffey]

Using a database such as SQLite allows for dynamic 2 column tables etc as opposed to the Cloverleaf tables which are static.  

We have many interfaces built to reference labs that require us to save the MRN using the order number as the key.  This is used when the result is received and allows us to ensure the MRN is correct before passing the result to the EMR.  This is one simple example of what we have done using SQLite.

[Paul Bishop]

We use SQLITE in a variety of ways both within the interface engine and in scripts outside the interface engine.  Some examples are:

– A table with doctor information that doesn’t come out on the messages (i.e. messages come out with internal number, but vendor needs the NPI).

– Store information about orders when they come out of the EMR that are then pulled back in when the results are sent from the vendor without that information

– Keep track of embedded PDF documents sent to our scanning system to make sure what we send we get back

– Build various reports by using a script to read the backup files and write to the database.

– keeping track of when lab orders were last paged out to the phlebotomists so they don’t get paged multiple times

We read and write to SQLITE databases within translates, pre and post tcl scripts and in tcl scripts in batch jobs.

Paul Bishop
Carle Foundation Hospital
Urbana, IL

[Michael Brande]

Initially I had wondered if sqlite could be used for recovery & error databases. We have had many times where those have become corrupt when the number of messages gets somewhere over 50k. Was wondering what could be done to overcome that. If sqlite cannot be used in this way, that is ok. I was just curious. And I had read on here somewhere that it looked like sqlite would eventually replace RAIMA in Cloverleaf.

I had also heard it could be used for SMAT (which would also be something I would be interested in learning).

And yes, using it for multi-column tables would be great too!

[Peter Heggie]

SQLite is free software with a small footprint and relatively small maintenance.

It is also helpful in disaster recovery because it is on the same server as Cloverleaf, and coordinating a point in time, synchronized recovery is not difficult.

That being said, if you are not on Windows, you can’t build a GUI front end to anything in SQLite. If you used SQL Server or Oracle, you can build web apps or desktop applications that can access the remote data using their supplied connectivity software.

Peter Heggie

[David Coffey]

Using SQLite for the Error and Recovery databases.   I think it is safe to say this cannot be done.

You state you have 50K messages in your databases. Which one?  Recovery or Error?  It seems your shop needs to adopt some policies regarding the maintenance of these databases.   This would prevent corruption.

I had read in the documentation for 6.x and up you can use SQLite for SMAT but I know nothing as we are not using it for that purpose….yet.

[Michael Brande]

David Coffey wrote:

Using SQLite for the Error and Recovery databases.

[David Coffey]

Only under extreme circumstances do we allow large numbers of messages to accumulate in the recovery database.  These circumstances are most often when an endpoint is down for a LONG period of time.  As a 3rd party solutions provider we most often do not have control over the system at the far end of the connection and have no control over how quickly the customer responds with a fix to restore the failed application.  We are tasked with holding their messages until they figure out a fix and bring the application back up.  

We do have an exercise when we are concerned about the volume which is to set the outbound thread to file protocol with the destination being nul:   Once this is done the recovery DB is cleared.  We are then required to manage the replay exercise using the messages contained in the various SMAT files.  

The best maintenance for the recovery DB is to ensure that it is not holding tens of thousands of messages by ensuring they get delivered.

[Paul Bishop]

For our system, we have the normal alerts that check if messages are queued up on outbound threads, but we also have a script that runs three times a day (8:30 AM, 12:30 PM, and 4:30 PM) that checks both the recovery and error databases for every site.  If the number of messages in either reaches a certain threshold (1000 for recovery, 500 for error), then it will alert us via email, paging, and/or texting so it can be checked into.  It’s come in handy sometimes when somebody sets up a new connection and forgets to setup the alert on it.

We also have a script that runs nightly that lists any files greater than 10,000,00 bytes.  We can then go through and hcidbinit those sites that need it when the database files grow too large.  It’s amazing how much that will improve throughput of messages once they are back down to a reasonable size.

Paul Bishop
Carle Foundation Hospital
Urbana, IL

[Michael Brande]

David Coffey wrote:

Only under extreme circumstances do we allow large numbers of messages to accumulate in the recovery database.

[David Coffey]

Micheal,

In the past we have stored in our recovery db close to 75,000 messages.  At the time we were VERY concerned about issues resulting from database corruption and the like.

As to the use of SMAT files.  Our production system is 5.8.5 on Windows.  We have our system configured to create daily smat files so each individual tout file  for an outbound interface is never that large in either physical size or count of messages.  I have never seen issues with using large SMAT files to re-transmit data.

I dont think my shop does anything special or out of the ordinary as to recovery steps or Raima db maintenance.

David Coffey

[Michael Brande]

David Coffey wrote:

Micheal,

In the past we have stored in our recovery db close to 75,000 messages.

[Author]

Replies