Sybase DC38133-01-0902-01 manual Partitioning the Customer table in the Contact example

Chapter 7. SQL Remote Design for Adaptive Server Anywhere

♦Those contacts belonging to the relevant customers, from the Contact table.

Partitioning the Customer table in the Contact example

The Customer table can be partitioned using the rep_key value as a subscription expression. A publication that includes the SalesRep and Customer tables would be as follows:

CREATE PUBLICATION SalesRepData ( TABLE SalesRep

TABLE Customer SUBSCRIBE BY rep_key

)

Partitioning the Contact table in the Contact example

The Contact table must also be partitioned among the sales representatives, but contains no reference to the sales representative rep_key value. How can the Message Agent match a subscription value against rows of this table, when rep_key is not present in the table?

To solve this problem, you can use a subquery in the Contact article that evaluates to the rep_key column of the Customer table. The publication then looks like this:

CREATE PUBLICATION SalesRepData ( TABLE SalesRep

TABLE Customer SUBSCRIBE BY rep_key

TABLE Contact

SUBSCRIBE BY (SELECT rep_key FROM Customer

WHERE Contact.cust_key = Customer.cust_key )

)

The WHERE clause in the subscription expression ensures that the subquery returns only a single value, as only one row in the Customer table has the cust_key value in the current row of the Contact table.

☞For an Adaptive Server Enterprise consolidated database, the solution is different. For more information, see “Partitioning tables that do not contain the subscription column” on page 149 .

Territory realignment in the Contact example

In territory realignment, rows are reassigned among subscribers. In the present case, territory realignment is the reassignment of rows in the Customer table, and by implication also the Contact table, among the Sales

107