re-replicate statements received from Consolidated DB

[Baron]

We are using SQL-Remote for replication between multiple Remotes and 1 Consolidated. We have a speceific table 'TABLESTAT' in each DB to periodically build statistics for each remote and then collect them all in Cons. There is a procedure responsible for refreshing the contents of 'TABLESTAT', which I need to call it centrally from Cons. and then propagate the call over PASSTHROUGH to be called on each Remote.

Problem: the procedure is executed on each Remote and the 'TABLESTAT' is really refreshed, but all those changes are no more replicated to Cons. (when I call the procedure manually on each Remote locally then it works w/o Problem of course)

Question: is there a way to enforce specific replicated statements to be re-replicated to sender? Or is there a way to let statements passed over PASSTHROUGH to be executed in the name of another local user?

[VolkerBarth · Accepted solution]

AFAIK the effects of SQL statements called via PASSTHROUGH mode are not replicated. That is by design.

To work around that, you could adapt the procedure call to trigger an newly created event that does the real modifications on your table. As events run in their own connection, their work would be replicated.

There are different approaches, like delaying the procedure call to be run within a SQL Remote hook procedure or using a "request queue", but all those would separate the command to run the procedure from the actual work on the tables.

[Baron · Accepted solution]

calling the procedure over event did exactly what I want. Thanks. One more question: how to overcome the problem of passing conditional SQL statements over PASSTHROUGH? IF statements are not allowed within PASSTHROUGH block (SQL Anywhere 10.0.1.4213)

[VolkerBarth · Accepted solution]

That restriction does hold for current versions, as well. The solution is the similiar to that discussed here: Put the conditional logic within a stored procedure and create, execute and drop that via PASSTHROUGH mode. (In that case without using an event or hook procedure.)

[Baron · Accepted solution]

Thanks for the reply. The problem is that the conditional logic itself contains (CREATE PROCEDURE) statement. So far as I know it is not accepted to create procedure within another procedure!! Am I right?

[regdomaratzki]

I'll clarify Volker's statement slightly, since is does not just apply to passthrough statements. Any SQL statement applied by SQL Remote in a message from user X will not be sent back to user X.

One way to workaround this would be to send down a CREATE PROCEDURE statement in passthrough that looked similar to :

CREATE PROCEDURE run_before_send ()
BEGIN
  CALL refesh_tablestat()
END

Now, you can define an sp_hook_dbremote_send_begin stored procedure, that will be called just before SQL Remote begins the send phase, that will check for the existence of the run_before_send stored procedure. If it exists, execute the stored procedure and then drop the procedure. The key here is that operations that execute in the sp_hook_dbremote_send_begin stored procedure are not applied as part of a message, so they will replicate back to the consolidated in this same SQL Remote session. 

Reg

[VolkerBarth]

The advantage of Reg's suggestion over a procedure that triggers an event would be that the hook procedure is called synchronously within dbremote's flow of operations whereas an event is run asynchronously and might or might not be finished when dbremote enters its sending phase...

[Baron]

Thanks, it has also worked with sp_hook_dbremote_send_begin. Nice trick also to delete it after calling it once, as we really not always need to refresh/replicate the tablestat.

[VolkerBarth]

If there is a requirement to refresh those stats repeatedly, I would not use PASSTHROUGH mode for that. Instead, I would add a replicated table with a row for each remote (with "subscribe by" for the according remote) and would simply set a bit column (say "refresh_stats") in that row when stats need to be refresehed. You would then not need the mentioned procedure. Instead, you would use the hook procedure to check whether the "refresh_stats" column is set, and if so

1. call the refresh_tablestat() procedure and
2. reset the "refresh_stats" column.

IMHO, that would mean there's no need to create, spread and drop code via PASSTHROUGH mode. Instead, the "command to resfresh" would simply be transported as a regular DML statement within the usual SQL Remote data flow.

[Baron]

Yes, this approach will eliminate the need for using PASSTHROUGH for triggering the refresh_tablestat().

The drawback in this case is that it will cost 2 replication rounds (1st replication to update the value from "refresh_stats" from cons. to remote; in 2nd replication the hook will have effect and will replicate the TABLESTAT from remote to cons.).

[VolkerBarth]

I don't think it will take two roundtrips. The hook should be called at the end of the receiving phase (say, as a sp_hook_dbremote_receive_end) and its result will then be contained in the sending phase.