SQL Remote User's Guide
  SQL Remote Design for Adaptive Server Anywhere
    Sharing rows among several subscriptions

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 Customer table subscription expression, because each Customer may belong to several Sales Reps:

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

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

As the INSERT of the Customer row is carried out by the Message Agent at the consolidated database, Adaptive Server 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 Customer 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 Customer row, with the referential integrity violation avoided by postponing checking to the end of the transaction.