cancel
Showing results for 
Search instead for 
Did you mean: 

clear application level caches on sql-remote sync

Former Member
2,241

Hello,

we have a business application which uses a lot of caching for a better user experience. When we data is stored on the server we use JMS to invalidate the caches of all sessions/instances accessing that given database.

We did now add a replicated database at another physical location and use ftp replication to exchange data.

Now our problem:

  • When data is changed on server 1, it is replicated to server 2
  • The caches on server 2 (at application level) are not informed of the changes and therefore do not invalidate their caches.

The same is true when data is changed on server 2 and then replicated to server 1.

What we would need is a way to trigger a JMS message when a record is added/modified/deleted by sql remote replication only.

I did think about doing this via triggers, but I'm not sure if that will work, as triggers actions apparently are not replicated.

André

VolkerBarth
Contributor
0 Kudos

@André: Feel free to leave a comment in case the underlying problem has been solved (or becoming irrelevant) in the meantime:)

Former Member
0 Kudos

I'm just implementing it, I should be able to give feedback in 1-2 weeks

Accepted Solutions (1)

Accepted Solutions (1)

VolkerBarth
Contributor

Your statement:

I did think about doing this via triggers, but I'm not sure if that will work, as triggers actions apparently are not replicated.

could need some clarification, methinks: This is just the (sensible) default setup and can be changed by the DBREMOTE -t option:

-t Replicates all triggers. - If you use this option, you must ensure that the trigger actions are not carried out twice at remote databases, once by the trigger being fired at the remote site, and once by the explicit application of the replicated actions from the consolidated database.

To ensure that trigger actions are not carried out twice, you can wrap an IF CURRENT REMOTE USER IS NULL ... END IF statement around the body of the triggers. Using the CURRENT REMOTE USER special value.

In my understanding, SQL Remote by default does not replicate the actions of triggers (except some cases of BEFORE triggers) as it assumes the triggers are defined at the remote side, too, and therefore the remote triggers will take the appropriate action. This obviously means that triggers are fired on DML that is executed via the Message Agent.

So, in your particular situation, you might use triggers with an if CURRENT REMOTE USER IS NOT NULL logic to handle DML done via the Message Agent:

create trigger ...
begin
  -- common trigger actions...

if current remote user is not null then
    -- DBRemote has sent updates, force to reload the cached data...
  end if;
end;

That being said, the difficult part will be to choose an appropriate measure when exactly to reload the cached data. I guess it would not be senseful to force this for every single row-level operation, particularly as this could happen within transcations and as such, violate RI constraints.

Therefore, a better approach might be to use SQL Remote hooks like sp_hook_dbremote_message_apply_end or sp_hook_dbremote_end to force a cache-reload once the complete remote operation is done.

Answers (0)