Sybase DC38133-01-0902-01 manual Publishing only some rows using a subscription expression

Chapter 7. SQL Remote Design for Adaptive Server Anywhere

♦The following is a single-article publication sending relevant order information to Samuel Singer, a sales rep:

CREATE PUBLICATION pub_orders_samuel_singer (

TABLE sales_order WHERE sales_rep = 856

)

☞For more information, see the “CREATE PUBLICATION statement”

[ASA SQL Reference, page 385].

SUBSCRIBE BY

The create publication statement also allows a SUBSCRIBE BY clause.

This clause can also be used to selectively publish rows in SQL Remote.

However, it is ignored during MobiLink synchronization.

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 Publications using a subscription expression are more compact, easier to

expressionsunderstand, 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.

97