Sybase DC38133-01-0902-01 manual Customer

Using the Subscribe_by_remote option with many-to-many relationships

When the SUBSCRIBE_BY_REMOTE option is ON, operations that arrive from remote databases on rows with a subscribe by value of NULL or ‘’ 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 publications including the Policy example. This section describes how the option automatically avoids the problem.

The database uses a subscription-list column for the Customer table, because each Customer may belong to several Sales Reps:

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. Assuming that the subscription-list column is not included in the publication, the operation at Marc’s remote database is as follows:

Customer

cust1010

cust_name

Policy

pol2345

cust1010

195

SalesRep

195

Marc Dill

As the INSERT of the Customer row is carried out by the Message Agent at the consolidated database, Adaptive Server Enterprise 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 to the new row, using the subscription value stored in the log, and Marc Dill is not on that list. 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 that, as the subscription-list column is NULL, the row belongs to the Sales Rep that inserted it. As a result, the INSERT is not replicated back to Marc Dill, and the replication system is intact.

You can use a trigger, which executes after the INSERT, to maintain the subscription-list column.

164