Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Remote » SQL Remote Installation Design » Sharing rows among several subscriptions

Territory realignment with a many-to-many relationship Next Page

Using the subscribe_by_remote option with many-to-many relationships


When the subscribe_by_remote option is On, operations from remote databases on rows with a subscribe by value of NULL or an empty string will assume the remote user is subscribed to the row. By default, the subscribe_by_remote option is set to On. In most cases, this setting is the desired setting.

The subscribe_by_remote option solves a problem that otherwise would arise with some publications, including the Policy example. This section describes the problem, and how the option automatically avoids it.

The publication uses a subquery for the Customers table subscription expression, because each Customers may belong to several Sales Reps:

CREATE PUBLICATION SalesRepData (
   TABLE SalesReps,
   TABLE Policy SUBSCRIBE BY rep_key,
   TABLE Customers SUBSCRIBE BY (
      SELECT rep_key FROM Policy
      WHERE Policy.cust_key =
         Customers.cust_key
   ),
);

Marc Dill is a Sales Rep who has just arranged a policy with a new customer. He inserts a new Customers row and also inserts a row in the Policy table to assign the new Customers to himself.

The Policy table has foreign keys to both the Customers and SalesReps tables.

As the INSERT of the Customers row is carried out by the Message Agent at the consolidated database, SQL Anywhere records the subscription value in the transaction log, at the time of the INSERT.

Later, when the Message Agent scans the log, it builds a list of subscribers from the subscription expression, and Marc Dill is not on the list, as the row in the Policy table assigning the customer to him has not yet been applied. If subscribe_by_remote were set to Off, the result would be that the new Customers is sent back to Marc Dill as a DELETE operation.

As long as subscribe_by_remote is set to On, the Message Agent assumes the row belongs to the Sales Rep that inserted it, the INSERT is not replicated back to Marc Dill, and the replication system is intact.

If subscribe_by_remote is set to Off, you must ensure that the Policy row is inserted before the Customers row, with the referential integrity violation avoided by postponing checking to the end of the transaction.