· Aug 2, 2018

"Listen" to a cache db?

Hello everyone,

Im just wondering if there is any possibility to "Listen" to a cache DB? We have our cache DB somewhere else provided by a different company, we are provided the interface to connect to that cache DB so we can extract the cache DB every night.

Im just curious if theres a way to "listen" to the cache DB, so if theres any changes on the table in the cache DB, I could make a trigger to extract the table again.

I know i could just set my ETL every hour or so... but that would extract all the tables in cache DB.

Thanks a lot for any help and information.

Kind regards,


Discussion (9)1
Log in or sign up to continue

Hi Mark,

Several years ago we faced the similar problem. External system needed to pull new requests from our Caché DB and to push back the responses. We maintained a transition table in Caché where we placed new requests, external system polled the table each N seconds fetching the requests from the table and placing the responses back. Communication was implemented via ODBC.

You can do something like this filling transition table on remote Caché side using triggers associated with the "main" table.

Hi Alexey,

Thanks for your quick reply. Could you maybe elaborate how this solution works? The only problem is, i dont have any rights to do something with the source cache db. I just have the interface to the cache db to connect via ODBC.

What i want to do is write a program in python to listen to cache db and everytime a change happens to a cache table objects, is to fire a trigger to extract this table or update the record in my destination database.

Is there any documentation out there?



Our case differed from yours as we had full access to Caché DB and did all this development on the  Caché side.

If you need to implement triggers, etc, you need full access to Caché DB.

Otherwise, if time_stamp_of_modification field presents in Caché table, the table can be queried with SQL SELECT statement based on the time of previous query on regular basis (e.g. each 5 seconds). Anyway, I have no idea how to achieve your goal without some development on the Caché side.

All I know about python is  "Monty Python" smiley


The table you want to control needs to get a parameter   Parameter DSTIME = "AUTO";  and a recompile

Then you can use this class to trace  changes, new, delete

/// Handle DSTIME using SQL
select * from OBJ.DSTIME where version = lastversion
/// to show all
Class OBJ.DSTIME Extends %Persistent [ Final, SqlRowIdPrivate {
Index idx On (Table, Version, RowID) [ IdKey ];
Property Version As %Integer [ ReadOnly, SqlColumnNumber = 2 ];
Property Table As %String [ ReadOnly, SqlColumnNumber = 3 ];
Property RowID As %String [ ReadOnly, SqlColumnNumber = 4 ];
Property Signal As %Integer(DISPLAYLIST = ",Modified,New,Deleted", VALUELIST = ",0,1,2")
Calculated, , SqlComputedSqlColumnNumber = 5,
     SqlComputeCode = { set {*}=^OBJ.DSTIME({Table},{Version},{RowID})}];
Property LastVersion As %Integer [ Calculated, SqlComputed, SqlColumnNumber = 6,
SqlComputeCode = { set {*}=+$G(^OBJ.DSTIME) } ];

/// to get actual last version and switch to new version
/// select top 1 LastVersion,OBJ.DSTIME_NewVersion(LastVersion) from OBJ.DSTIME
ClassMethod NewVersion(anycolumn As %String) As %Integer [ SqlProc ]
 Quit $I(^OBJ.DSTIME) }

Storage Default {


 so get your actual changes

select * from OBJ.DSTIME where version = lastversion

and switch to next version by 

select top 1 LastVersion,OBJ.DSTIME_NewVersion(LastVersion) from OBJ.DSTIME


But you have to have full access to Caché as you have to make the DB "talking"  to be able to "listen"