Publishing only some rows using a subscription expression

Publishing only some rows using a WHERE clause

SQL Remote User's Guide
SQL Remote Design for Adaptive Server Anywhere
Publishing data

Publishing only some rows using a subscription expression

You can specify a subscription expression to include a different set of rows in different subscriptions to publications containing the article.

For example, in a mobile workforce situation, a sales publication may be wanted where each sales rep subscribes to their own sales orders, enabling them to update their sales orders locally and replicate the sales to the consolidated database.

Using the WHERE clause model, a separate publication for each sales rep would be needed: the following publication is for sales rep Samuel Singer: each of the other sales reps would need a similar publication.

CREATE PUBLICATION pub_orders_samuel_singer (
   TABLE sales_order
      WHERE sales_rep = 856
)

To address the needs of setups requiring large numbers of different subscriptions, SQL Remote allows a subscription expression to be associated with an article. Subscriptions receive rows depending on the value of a supplied expression.

Benefits of subscription expressions

Publications using a subscription expression are more compact, easier to understand, and provide better performance than maintaining several WHERE clause publications. The database server must add information to the transaction log, and scan the transaction log to send messages, in direct proportion to the number of publications. The subscription expression allows many different subscriptions to be associated with a single publication, whereas the WHERE clause does not.

To create an article using a subscription expression (Sybase Central)

Connect to the database as a user with DBA authority.
In the left pane, select the Publications folder.
From the File menu, choose New > Publication.

The Publication Creation wizard appears.
Type a name for the publication and click Next.
On the Tables tab, configure the desired values for that table.
On the SUBSCRIBE BY Restrictions tab, use the controls to create the subscription expression.
Follow the remaining instructions in the wizard.

To create an article using a subscription expression (SQL)

Connect to the database as a user with DBA authority.
Execute a CREATE PUBLICATION statement that includes the expression you wish to use as a match in the subscription expression.

Examples

The following statement creates a publication that publishes the id, company_name, city, and state columns of the customer table, and which matches the rows with subscribers according to the value of the state column:
```
CREATE PUBLICATION pub_customer (
   TABLE customer (
      id,
      company_name,
      city,
      state )
   SUBSCRIBE BY state
)
```
The following statements subscribe two employees to the publication: Ann Taylor receives the customers in Georgia (GA), and Sam Singer receives the customers in Massachusetts (MA).
```
CREATE SUBSCRIPTION
TO pub_customer ('GA')
FOR Ann_Taylor ;
CREATE SUBSCRIPTION
TO pub_customer ('MA')
FOR Sam_Singer
```

Users can subscribe to more than one publication, and can have more than one subscription to a single publication.