Question
Mike Dawson · May 4, 2021

Update SQL Server from Cache/Iris

Hello Community,

We want to send ALL updates from our Cache server to a remote SQL Server instance that will be used as a reporting data warehouse.

Has anyone done this already and if so what is the best way to go about setting this up?

Thanks

Regards

Mike 

Product version: Caché 2018.1
00
1 0 2 71
Log in or sign up to continue

Replies

It depends on...

Who is sitting at the other end? A Cache/IRIS server or a third-party product?

If Cache/IRIS: Mirroring, shadowing are the catchwords, you have to look for. In case of third-party SQL-DB: how fast (how often) want to do your updates? Once a day or (nearly)realtime?

I did something like that several years ago... the procedure is (just as a starting point):

Our application uses objects, so all the involved classes have an %OnAfterSave() method, something like this

Method %OnAfterSave(insert As %Boolean) As %Status
{
   do ..addToTransfer(..%Id())
}

with some smartness, like do not add if the record is already in the transfer queue, etc.  If you use SQL instead of objects, triggers are your friend.

We have also a task,  which crates (based on the class definition) a series of INSERT/UPDATE statement(s) and does the transfer with the help of  %SQLGatewayConnection.

in order to provide efficient updates for DeepSee there is a feature named DSTIME.
It writes kind of a log about inserts, deletes, updates. This may suit your requirements.
About 2 years ago I have written an article about the subject. Take a look if this is what you need.
https://community.intersystems.com/post/synchronize-data-dstime