When the subscribe_by_remote option is set to On, operations from remote databases on rows with a SUBSCRIBE BY value of NULL or an empty string assume the remote user is subscribed to the row. By default, the subscribe_by_remote option is set to On.

The subscribe_by_remote option solves a problem that otherwise would arise with some publications. The following publication uses a subquery for the Customers table subscription expression because customers can belong to several sales representatives:

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
   ),
);

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

On the consolidated database, SQL Remote carries out the insert of the Customers row and SQL Anywhere records the subscription value in the transaction log, at the time of the insert.

Later, when the SQL Remote scans the transaction 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 statement.

As long as subscribe_by_remote is set to On, the SQL Remote assumes the row belongs to the Sales representative who inserted it, the INSERT statement 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.

See also

• subscribe_by_remote option [SQL Remote]