Article
Robert Cemper · Mar 26, 2019 2m read
Open Exchange

Synchronize Data with DSTIME

 For Data Synchronization inside Caché you have a range of ways to synchronize objects and tables.
At DB level you can use Shadowing  or Mirroring 

This works excellent and if you need just a part of your data to be synchronized you may split your
data into smaller pieces using Global mapping 
Or if you need bi-directional synchronization on Class/Table level you can use the Object Synchronization Feature 


The limit of all these excellent features:
They just work from Caché/IRIS to Caché/IRIS.

If you need to synchronize your data to some external DB you require some other solution.

The solution is available in Caché/IRIS since quite some time and works excellent.
^OBJ.DSTIME does the magic.

It was built to allow data synchronization with Deep See.
It keeps a very simple journal on Object / Table changes by signaling  Modified,New,Deleted
This could be useful not only for DeepSee but for any other type of Data Synchronization.

The Global ^OBJ.DSTIME has 2 additional features

  • It is wrapped in a persistent class %SYSTEM.DSTIME   so you can use it also as normal SQL Table to select your changes  
  • It maintains a version ID (named DSTIME)  that allows control of synchronized junks:             - you fetch the last version             - increase the version             - and then you upload your changes wherever you require them based on the fetched version

And as you do the synchronization by pure SQL your target can be just any DB understanding SQL.

I extended the class %SYSTEM.DSTIME   and placed an example here to try it in SAMPLES
 

This is a coding example working on Caché 2018.1.3 and IRIS 2020.2 
It will not be kept in sync with new versions 
It is also NOT serviced by InterSystems Support !

 

#Databases #Distributed Data Management #Interoperability #Caché #Ensemble #InterSystems IRIS #Open Exchange
Check the related application on InterSystems Open Exchange
110
2 2 6 386

Replies

Andre Wessels · Apr 8, 2019

Thank you very much for exposing and extending this capability to the community.

How could a Property like timestamp as $H be added to the DSTIME objects ?

00
Robert Cemper · Apr 8, 2019

from docs:
Class parameter  DSTIME

• parameter DSTIME;
If the DSTIME parameter is set to AUTO then the most recent filing operation in the current DSTIME value for each object will be recorded in a global, ^OBJ.DSTIME:
^OBJ.DSTIME(ExtentName,DSTIME,objectID) = filing operation
For DSTIME=AUTO the DSTIME value is recorded in ^OBJ.DSTIME and is set by the consumer of DSTIME data.
Refer to %DeepSee documentation for more information on how DSTIME is used by %DeepSee.
The filing operations are:
Code
Operation
0
Update
1
Insert
2
Delete

If the DSTIME parameter is set to MANUAL then the user is responsible for journaling object filing operations.

With MANUAL you are free to add whatever you like to your global ^OBJ.DSTIME
at the price to add your own code manually for any %Save , %Delete,  and also all SQL Triggers.

I didn't mention this. If you do it, there is no advantage to use DSTIME. you could do it with any class or global.
You just use the idea.  

10
Eduard Lebedyuk · Apr 8, 2019

As a simple solution for the same problem I often add these properties to persistent classes:

/// Creation timesptamp (UTC)
Property createdOn As %TimeStamp(POPORDER = -1) [ InitialExpression = {$ZDATETIME($ZTIMESTAMP, 3, 1, 3)}, Required ];

/// Last modification timestamp (UTC)
Property updatedOn As %TimeStamp(POPORDER = -1) [ Required, SqlComputeCode = { set {*} = $ZDATETIME($ZTIMESTAMP, 3, 1, 3)}, SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ];

They track object creation time and last change time. This works for both SQL and Object access.

00
Robert Cemper · Apr 8, 2019

That's an excellent approach. But it has its limits.

  • You require the freedom to add properties to the existing class /table 
  • to feed the Global ^OBJ.DSTIME you require the object ID. That's available for  %%UPDATE, not for %%INSERT
    see https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=ROBJ_property_sqlcomputeonchange
    Any event-triggered computation occurs immediately before validation and normalization (which themselves are followed by writing the value to the database).
    so   You don't have yet the ID at insert time.
  • and there is evidently no chance for handling deletions.

so you really need to define your  triggers or have it in common for objects and SQL as in this example: 

Trigger AfterInsert [ Event = INSERT, Foreach = row/object, Time = AFTER ]
{
 // find id according to object or SQL
 set %ok=1 quit $$$OK
}

Trigger AfterUpdate [ Event = UPDATE, Foreach = row/object, Time = AFTER ]
{
    // find id according to object or SQL
   if $d(%rowid) set id=%rowid
    else  if $isobject(pNew) set id=pNew.%Id()
  do whatever ....
 set %ok=1 quit $$$OK
}

Trigger AfteDelete [ Event = DELETE, Foreach = row/object, Time = AFTER ]
{
 // find id according to object or SQL
 set %ok=1 quit $$$OK
}

// find id according to object or SQL  
This highlights the fact that the ID is in different variables for object and SQL
and not all SQLTriggers have it in %rowid.
So real debugging is required.

10
Peter Steiwer · Jul 17, 2019

Hi Robert,

One thing you might want to include is a warning about using this + DeepSee. DeepSee will remove entries from this global during a synchronize. It looks like you also have some code to clean up entries. It appears that they will negatively interact with each other.

10