temporary stop in replication DBREMOTE

[Baron]

Hi all, I have a scenario in which two databases are replicating against each other (SQL Anywhere 10) using SQL Remote technology. Situation: I need to insert huge amount of new rows on the publishing database, but I want to exclude these new rows of being sent to the subscriber. Tried solution: I tried the following: 1- Alter publication P1 delete table T1 2- Insert into T1 values (Line1) 3- Alter publication P1 add table T1 4- Insert into T1 values (Line2).

Question: why is the insert command of Line2 is still excluded from being replicated????

[Baron]

Sorry for the inconvinience, but I have found the solution. It was my mistake in describing the above scenario. Actually instead of the 'insert' statement in step 4 above, I have executed 'load' statement.

Now, I have revealed that all load statements will not take part in replication (why? because they will be interpreted in the log file as 'load' statment and not as 'insert' statement).

Thanks again

[VolkerBarth]

As to the LOAD TABLE statement:

Starting with v12.0.0, with the according option WITH ROW LOGGING, you can use that in a SQL Remote/ML Client setup, cf. the docs:

Inserts are not recorded in the transaction log file unless WITH ROW LOGGING clause is specified, so the inserted rows may not be recovered in the event of a failure depending upon the logging type. [...] In addition, the LOAD TABLE statement should not be used without the WITH ROW LOGGING clause in databases that are used as MobiLink clients, or in a database involved in SQL Remote replication, because these technologies replicate changes through analysis of the log file.

(Your reasoning is still true, but with that load option, LOAD TABLE is not logged as single LOAD statement but as an INSERT statement for each row. - Note that nevertheless even with that option LOAD TABLE does not fire insert triggers on the table, so it's not truly the same as a mass-insert.)

[Baron]

Thank you very much for the explanation. Regards

[regdomaratzki]

LOAD TABLE is exactly what I would suggest using to insert a large amount of data that you don't want to replicate, and make sure you DON'T use the WITH ROW LOGGING option in v12+.

Thanks for posting the solution to your own problem when you figured it out yourself.

[VolkerBarth]

Sigh, Reg gets it right as usually - while I forgot that the original intent was to insert data without replicating them...:)

So no need to ALTER PUBLICATION DROP/ADD TABLE... (which is something rather dangerous in a running setup, one might add...).

[Baron]

Thanks for the comments. One drawback for the load statement, is that it is unrecoverable. Since the load statement is interpreted in the transaction log file as 'load' and not as 'insert', then the rollback command will not have any effect.

[VolkerBarth]

One drawback for the load statement, is that it is unrecoverable.

What do you mean by "unrecoverable"? LOAD TABLE does an automatic commit, so you cannot rollback it within a transaction. However, you can certainly recover a database with LOAD TABLE statements in the transaction log - unless you had used the WITH FILE NAME LOGGING option and the specified file is no longer available during recovery. IMHO the recovery of the different LOAD TABLE methods is fully documented in the cited doc topic, methinks.

[regdomaratzki]

Volker is correct. If the file referenced in the LOAD TABLE still exists in the exact same location and is the exact same file, receovery will be possible.

Paranoid Reg always recommends a full backup after a successful LOAD TABLE so you have a new starting point for your recovery. I don't like having to rely on the existance of anything except the backed up database and transaction logs in a recovery situation.

[VolkerBarth]

Hm, wouldn't option WITH CONTENT LOGGING do the trick for you, i.e. not requiring a backup and still assuring only the database and translog are enough for recovery?

(Apparently "paranoid" comes in several degrees, too:))

[regdomaratzki]

Yes, WITH CONTENT LOGGING would also work.

My expertize with backup/recovery was back in the v5.5 -> v10 days. I'm going to pretend I knew that option existed and say that the question was for v10, so I wanted to make sure the inital poster got a paranoid answer to suit the version they were using. 🙂

Reg