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).

asked 14 Apr '21, 03:00

Baron's gravatar image

Baron
2.1k134146174
accept rate: 46%

edited 14 Apr '21, 03:01

Both Volker and I have assumed that when you say "replication" that you mean you are using SQL Remote, as opposed to synchronizing with dbmlsync and MobiLink.

Can you please confirm that assumption correct?

Thanks, Reg

(14 Apr '21, 08:55) Reg Domaratzki
Replies hidden
1

Well, this assumption is also based on the "dbremote" tag in my case...

(14 Apr '21, 09:03) Volker Barth

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.

permanent link

answered 14 Apr '21, 05:10

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

converted 14 Apr '21, 06:12

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)

(14 Apr '21, 06:10) Baron
Replies hidden

Hm, what's the actual requirement? Are you locally deleting data on the remote(s) that should stay permanently at the cons?

(14 Apr '21, 06:46) Volker Barth

Yes, almost. But can't simply go on Remote and say ignore all delete statements coming from remotes, since some of them are from Connection A and should be deleted on Cons.

(14 Apr '21, 06:52) Baron

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.

(14 Apr '21, 07:08) Volker Barth

This is a super good idea, despite it changes my whole plan, but never thought of it.

Thank you.

(14 Apr '21, 09:58) Baron

One more question, what is the best way to identify the Cons. database?

Usually in Cons. there are >1 rows in SYSREMOTEUSERS but, we have some installations with only one remote :(

(14 Apr '21, 10:20) Baron
Replies hidden
2

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

(14 Apr '21, 10:39) Reg Domaratzki

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?

(14 Apr '21, 16:55) Baron
showing 2 of 8 show all flat view

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.

permanent link

answered 15 Apr '21, 02:24

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

Thanks a lot.

We are using both mobilink and SQL Remote, and sometimes standalone installations.

(15 Apr '21, 02:52) Baron

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:

  1. STOP SYNCHRONIZATION DELETE
  2. delete from table1

Then I translated the transaction log file, and I still see the delete statement in it! Do I understand it false?

(15 Apr '21, 08:59) Baron
Replies hidden
1

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.

(15 Apr '21, 09:07) Reg Domaratzki

Then I would say the explanation in the documentation is misleading:

"You can temporarily suspend automatic logging of delete operations using the STOP SYNCHRONIZATION DELETE statement"

(15 Apr '21, 09:18) Baron

That's why it is called "DocCommentXchange" - feel free to leave a clarifying comment there...

(15 Apr '21, 10:11) Volker Barth
Comment Text Removed
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×159
×78
×60
×19
×13

question asked: 14 Apr '21, 03:00

question was seen: 835 times

last updated: 15 Apr '21, 17:39