SQL Anywhere database replication - Granular control of Synchronize Subscription?

Author

Granular control of Synchronize Subscription?

Carol Stone

2005-11-14, 11:23 am

Sybase Adaptive Server Anywhere Database Engine Version 7.0.4.3362

Let's say we have a publication named BIGONE.

This publication has, oh, 100 tables or more.

We have decided that we want to add a subscribe by restriction to one of
the tables in BIGONE.

We would like to synchronize the affected table, not the whole subscription.

Is this possible/what should I be looking for in the help? It looks like
Synchronize Subscription will only do the 100 tables. There is no way
we're going to do that.

Thanks for your help,
carol stone at ctcommunitycare org

Reg Domaratzki

2005-11-15, 9:23 am

You are correct that synchronize subscription will synchronize the entire
subscription. There is currently no way to only synchronize a single table
within the publication. I'll propose two alternatives for you :

1) Move the table in question from the BIGONE publication to a seperate
publication and synchronize that subscription. I haven't properly thought
through the side effects if you have two publications on the consolidated
that replicate to a remote where all the tables are contained in a single
publication, but I don't think it would be a problem, and a few simple tests
in your test environment could prove or disprove this quite easily.

2) Do a manual synch subscription for each remote user that only deletes and
re-adds the table to the new table. Make sure the nobody is modifying the
database, and execute something similar to the non-tested pseudo code below
:

create procedure Synch_t1 ( in @rem_user varchar(128), in @sub_by integer )
begin
declare stmt long varchar;
declare c1 cursor for select all_columns from t1 where sub_by = @sub;
set stmt = 'passthrough only for ' || @rem_user || ';';
set stmt = stmt || 'delete from t1;'
open c1;
for each row in c1
set stmt = stmt || 'insert into t1 values ( ' || <values in current row>
|| ');'
next c1
close c1;
set stmt = stmt || 'passthrough stop;';
execute immediate stmt;
end;

create procedure do_all_synch ()
begin
declare cursor c2 for select user_name, subscribe_by
from syssubscriptions
where publication_name = 'BIGONE';
open c2
for each row in c2
call synch_t1( cur_user, cur_sub_by );
next c2;
close c2;
end;

This could use some testing as well of course. I wouldn't trust anything I
wrote in the morning before my first coffee, and it's VERY important that
the database is not in use at the time. Having rows changing in the t1
table while this process is running is a Very Bad Idea that would result in
Bad Things Happening.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Certified SQL Anywhere Associate
Please reply only to the newsgroup

ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL and click "GO"

"Carol Stone" <""carol.stone \"@ ctcommunitycare . org"> wrote in message
news:4378c1da$1@foru
ms-1-dub...
> Sybase Adaptive Server Anywhere Database Engine Version 7.0.4.3362
>
> Let's say we have a publication named BIGONE.
>
> This publication has, oh, 100 tables or more.
>
> We have decided that we want to add a subscribe by restriction to one of
> the tables in BIGONE.
>
> We would like to synchronize the affected table, not the whole
subscription.
>
> Is this possible/what should I be looking for in the help? It looks like
> Synchronize Subscription will only do the 100 tables. There is no way
> we're going to do that.
>
> Thanks for your help,
> carol stone at ctcommunitycare org

Carol Stone

2005-11-15, 11:23 am

Oh, interesting. I had completely forgotten about the ONLY keyword with
PASSTHROUGH. Thank you! I think the trick here is going to be getting
this to work on the tester environment (DUH). But I think you've pointed
me to the way to have even more granular control than a table - this
strategy might actually allow us to make modifications to the really
large tables over a number of days.

And lucky for me, we're set up so that people either replicate or run
our application - never both at the same time. Looks like it's time to
be happy about the setup.

Thanks a lot!!!

Sponsored Links