synchronizing tables outside sysarticles (DBRemote)

[Baron]

We have a problem with DBREMOTE (SQL Anywhere 17) as follows:

The consolidate DB has 3 publication with total say 5 tables (mytable1, mytable2, mytable3, mytable4, mytable5). I tried to synchronize all the 3 publications.

 SYNCHRONIZE SUBSCRIPTION TO mypublication1 FOR myuser1;
 SYNCHRONIZE SUBSCRIPTION TO mypublication2 FOR myuser1;
 SYNCHRONIZE SUBSCRIPTION TO mypublication3 FOR myuser1;

On the remote side will more tables synchronized, so in the log file I see something like this:

...
delete from mytable1;
delete from mytable2;
delete from mytable3;
delete from mytable4;
delete from mytable5;
delete from mytable6;--WHY???
...

What could be the reason that the consolidate made the decision to synchronize mytable6 despite this table not in sysarticles is!

[VolkerBarth]

Has mytable6 be part of a former publication?

[Baron]

Yes, it was.

How can I enforce the cons. DB to forget this table?

[Baron]

actually mytable6 (in our case many other tables) where excluded from the publication long time ago using the alter publication delete table mytable6 statement.

Apparently in order to "permanently" remove a table from a publication we should drop the publication and recreate it again.

[VolkerBarth]

Has the ALTER PUBLICATION DELETE TABLE mytable6 statement been run at the remotes, too (probably via a PASSTHROUGH statement at the consolidated, so it is sent to the remotes, as well)?

(I don't know how exactly the SYNCHRONIZE SUBSCRIPTION statement decides which tables to delete from, i.e. whether that information is taken from the current definition of the publication in the consolidated or during the application at the remote from its current publication definition there. The INSERT statements for the according tables of course have to be created at the consolidated.)

[Baron]

No, the ALTER PUBLICATION DELETE TABLE mytable6 statement was executed only on the cons. mytable6 was in our case not bidirectional replicating (but only from cons. to remotes, so that there was no need to execute it on the remotes).

My test shows that the decision is made on the cons., so each table included in the synchronisation is equipped with two statements (1x delete + 1x insert into for the whole table contents).

In our case I can't see the logs on the remotes till the end, because deleting unwanted tables (mytable6) causes missing PK for FK and the replication (synchronization) stopps with a rollback.

According to my test, the problem can be solved by dropping the publication and recreating it again, what however not a funny job is in the production.

[VolkerBarth]

OK, then I'm out of my wits, I have never used SQL Remote just as unidirectional replication. Hopefully @Reg Domaratzki can clarify...

[Baron]

Regardless of how/who decides whicht tables to delete, our main concern is now: How to enforce the server to forget about the old tables?

As per my test the only way is to drop the publication and recreate it again, but this is not so easy in a running production system, so is there another cheaper solution?

[VolkerBarth]

We have never dropped publications nor dropped tables from publications in production systems (instead we have "emptied" tables that were no longer used), so I can't tell. However, instead of dropping a table you could alter the article by adding a WHERE clause for the table that is never true, so the table's rows won't be published anymore.

But I guesss that would also behave in an undesired way when it comes to a SYNCHRONIZE SUBSCRIPTION because it would probably also sent a DELETE for the whole table, followed by no INSERT as no more rows fulfill the article's condition.

Why do you need to use SYNCRHONIZE SUBSCRIPTION?

[Baron]

I need to 'synchronize subscription' each time I add a new remote or each time I want to overwrite/correct data on a/all remote/s.

We did not use the other way with dbextract.

[VolkerBarth]

Ah, I see. Well, we always have used dbextract (or a similar custom extration process), so we rarely had to use SYNCHRONIZE SUBSCRIPTION at all, I guess only to fix remote data in very rare and individual cases...

[Baron]

We use (similar custom extraction process), but for that we should have a fix status on the consolidated and start the subscription for the client (somehow complex with timing).

In contrast, synchronize subscription can be executed anytime, and at the end we have guaranteed synchronized tables between cons. and the client, without having concerns about timing...

[Baron]

Unfortunately I don't find any other means to solve my problem rather than dropping the publication and recreating it again (without the unwanted tables).

This is so far not so bad, but what makes this a bad solution is the fact that ISYSSUBSCRIPTION has a FK-PK realtionship to ISYSPUBLICATION (with cascading delete), which means dropping a publication will result in dropping all its subscriptions which at the end means that all transactions before this point will be ignored from DBREMOTE.

I dont have the permission to drop this relationship and I also am not sure whether this could solve the problem (that DBREMOTES ignores all previous transactions).

Is there any other way to solve my main problem without the need to drop the publications/subscriptions?