on 2021 Apr 14 4:00 AM
Can somebody advise me for such a problem?
A remote Database (sql Anywhere 17) replicating to another cons. DB, has two connections A & B. for some reasons we need to exclude the transactions of connection B on this remote DB from being replicated to the cons., whereas we need to keep transactions of connection A to replicate to cons.
The simplest way is to add an extra column in each replicating table, and let each connection write its own value, so that the publication can distinguish where the trnasactions are coming from. This would not help for our case.
What we need is to use alter publication P1 delete Table T1 which affects only the transactions of Connection B (without affecting Connection A).
Request clarification before answering.
How do you distinguish these transactions? Are they done by different users? And are these "differences" noticeable in the data?
AFAIK, SQL Remote does replicate all transactions of all published articles, no matter what user/transaction has run those. However, in case you can somehow distinguish the transactions you might be able to add logic at the cons (say, by using INSERT/UPDATE/DELETE triggers) that could handle incoming data differently - say, by ignoring insert/updates for a certain user. - Note, that's just a very wild guess, and I would suspect that this could easily lead to data inconsistency...
That being said, an easier approach would be to add a "DoNotReplicate" flag in the according table(s) and use an article with an according WHERE DoNotReplicate = 0 clause or the like. Even here, you need to assure that no foreign key relationships are broken for published data.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I can define a new user, and can always execute those "unwanted" transactions in the name of this user.
It is difficult to handle it on the cons. because all those "unwanted" transactions are delete transactions.
My explanation of the problem above was not complete, so all the transactions coming of Connection B (unwanted transactions) are delete transactions, and they are executed from a daily triggered event (different connection)
A very different approach: Could you decide which rows to delete from within the consolidated?
Because you could use a PASSTHROUGH ONLY session to send delete statements to the remote that would only delete there "locally", i.e. without being replicated back to the cons.
That could be either via an explicit list of rows to delete or say, by calling a stored procedure that would then delete the according rows.
So basically you would trigger your event not locally but via the cons.
At the consolidated database, you execute the GRANT REMOTE command, but at the remote database, you execute GRANT CONSOLIDATE, to identify the database immediately above the current database in a SQL Remote hierarchy.
Find the database where the SQL below returns zero rows to find the consolidated database
select count(*) from SYS.SYSREMOTEUSER where consolidate='Y'
Reg
Baron asked:
and in case of mobilink, how can I tell that the Database is a consolidate? if exists select 1 from ml_subscription Or, is there another way?
If you are using MobiLink and want to delete data on a remote database without synchronizing these deletes, it's way easier: You can embrace the delete statement with a pair of STOP SYNCHRONIZATION DELETE and START SYNCHRONIZATION DELETE statements, see the sample here.
This has only effect on the current transaction, so it might just be exactly what you are looking for - in case you actually are using MobiLink and not SQL Remote.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
According to the documentation I can understand that after executing STOP SYNCHRONIZATION DELETE all delete statements will not be logged (in transaction log files), and so they would not be sent to cons.
I did a small test:
Then I translated the transaction log file, and I still see the delete statement in it! Do I understand it false?
The deletes are still logged into the transaction log, as they will be needed in case of recovery, but they are ignored by dbmlsync when scanning the transaction log and will not be uploaded.
User | Count |
---|---|
87 | |
9 | |
9 | |
8 | |
6 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.