Partitioning tables that do not contain the subscription expression

Data Replication with SQL Remote
PART 2. Replication Design for SQL Remote
CHAPTER 7. SQL Remote Design for Adaptive Server Anywhere

Partitioning tables that do not contain the subscription expression

In many cases, the rows of a table need to be partitioned even when the subscription expression does not exist in the table.

The Contact example

The Contact database illustrates why and how to partition tables that do not contain the subscription expression.

Example

Here is a simple database that illustrates the problem.

Each sales representative sells to several customers. At some customers there is a single contact, while other customers have several contacts.

The tables in the database

The three tables are described in more detail as follows:

Table

Description

SalesRep

All sales representatives that work for the company. The SalesRep table has the following columns:

rep_key     An identifier for each sales representative. This is the primary key.

name     The name of each sales representative.

The SQL statement creating this table is as follows:

CREATE TABLE SalesRep ( rep_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, PRIMARY KEY (rep_key) )

Customer

All customers that do business with the company. The Customer table includes the following columns:

cust_key     An identifier for each customer. This is the primary key.

name     The name of each customer.

rep_key     An identifier for the sales representative in a sales relationship. This is a foreign key to the SalesRep table.

The SQL statement creating this table is as follows:

CREATE TABLE Customer ( cust_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, rep_key CHAR(12) NOT NULL, FOREIGN KEY REFERENCES SalesRep, PRIMARY KEY (cust_key) )

Contact

All individual contacts that do business with the company. Each contact belongs to a single customer. The Contact table includes the following columns:

contact_key     An identifier for each contact. This is the primary key.

name     The name of each contact.

cust_key     An identifier for the customer to which the contact belongs. This is a foreign key to the Customer table.

The SQL statement creating this table is:

CREATE TABLE Contact ( contact_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, cust_key CHAR(12) NOT NULL, FOREIGN KEY REFERENCES Customer, PRIMARY KEY (contact_key) )

Table	Description
SalesRep	All sales representatives that work for the company. The SalesRep table has the following columns: rep_key An identifier for each sales representative. This is the primary key. name The name of each sales representative. The SQL statement creating this table is as follows: CREATE TABLE SalesRep ( rep_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, PRIMARY KEY (rep_key) )
Customer	All customers that do business with the company. The Customer table includes the following columns: cust_key An identifier for each customer. This is the primary key. name The name of each customer. rep_key An identifier for the sales representative in a sales relationship. This is a foreign key to the SalesRep table. The SQL statement creating this table is as follows: CREATE TABLE Customer ( cust_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, rep_key CHAR(12) NOT NULL, FOREIGN KEY REFERENCES SalesRep, PRIMARY KEY (cust_key) )
Contact	All individual contacts that do business with the company. Each contact belongs to a single customer. The Contact table includes the following columns: contact_key An identifier for each contact. This is the primary key. name The name of each contact. cust_key An identifier for the customer to which the contact belongs. This is a foreign key to the Customer table. The SQL statement creating this table is: CREATE TABLE Contact ( contact_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, cust_key CHAR(12) NOT NULL, FOREIGN KEY REFERENCES Customer, PRIMARY KEY (contact_key) )

Replication goals

The goals of the design are to provide each sales representative with the following information:

The complete SalesRep table.
Those customers assigned to them, from the Customer table.
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 Info For an Adaptive Server Enterprise consolidated database, the solution is different. For more information, see Partitioning tables that do not contain the subscription column.

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 Reps.

When a customer is reassigned to a new sales rep, the Customer table is updated. The UPDATE is replicated as an INSERT or a or a DELETE to the old and new sales representatives, respectively, so that the customer row is properly transferred to the new sales representative.

For information on the way in which Adaptive Server Anywhere and SQL Remote work together to handle this situation, see Who gets what?.

When a customer is reassigned, the Contact table is unaffected. There are no changes to the Contact table, and consequently no entries in the transaction log pertaining to the Contact table. In the absence of this information, SQL Remote cannot reassign the rows of the Contact table along with the Customer.

This failure will cause referential integrity problems: the Contact table at the remote database of the old sales representative contains a cust_key value for which there is no longer a Customer.

Use triggers to maintain Contacts

The solution is to use a trigger containing a special form of UPDATE statement, which does not make any change to the database tables, but which does make an entry in the transaction log. This log entry contains the before and after values of the subscription expression, and so is of the proper form for the Message Agent to replicate the rows properly.

The trigger must be fired BEFORE operations on the row. In this way, the BEFORE value can be evaluated and placed in the log. Also, the trigger must be fired FOR EACH ROW rather than for each statement, and the information provided by the trigger must be the new subscription expression. The Message Agent can use this information to determine which subscribers receive which rows.

Trigger definition

The trigger definition is as follows:

CREATE TRIGGER UpdateCustomer

BEFORE UPDATE ON Customer
REFERENCING NEW AS NewRow
   OLD as OldRow
FOR EACH ROW
BEGIN
   // determine the new subscription expression 
   // for the Customer table
   UPDATE Contact
   PUBLICATION SalesRepData
   OLD SUBSCRIBE BY ( OldRow.rep_key )
   NEW SUBSCRIBE BY ( NewRow.rep_key )
   WHERE cust_key = NewRow.cust_key;
END;

A special UPDATE statement for publications

The UPDATE statement in this trigger is of the following special form:

UPDATE table-name

PUBLICATION publication-name

{ SUBSCRIBE BY subscription-expression |

OLD SUBSCRIBE BY old-subscription-expression

NEW SUBSCRIBE BY new-subscription-expression }

WHERE search-condition

Here is what the UPDATE statement clauses mean:
The table-name indicates the table that must be modified at the remote databases.
The publication-name indicates the publication for which subscriptions must be changed.
The value of subscription-expression is used by the Message Agent to determine both new and existing recipients of the rows. Alternatively, you can provide both OLD and NEW subscription expressions.
The WHERE clause specifies which rows are to be transferred between subscribed databases.

Notes on the trigger

If the trigger uses the following syntax:
```
UPDATE table-name
PUBLICATION pub-name
   SUBSCRIBE BY sub-expression
WHERE search-condition
```
the trigger must be a BEFORE trigger. In this case, a BEFORE UPDATE trigger. In other contexts, BEFORE DELETE and BEFORE INSERT are necessary.

If the trigger uses the alternate syntax:

UPDATE table-name 
PUBLICATION publication-name
   OLD SUBSCRIBE BY old-subscription-expression  
   NEW SUBSCRIBE BY new-subscription-expression }
WHERE search-condition

The trigger can be a BEFORE or AFTER trigger.

The UPDATE statement lists the publication and table that is affected. The WHERE clause in the statement describes the rows that are affected. No changes are made to the data in the table itself by this UPDATE, it makes entries in the transaction log.
The subscription expression in this example returns a single value. Subqueries returning multiple values can also be used. The value of the subscription expression must the value after the UPDATE.

In this case, the only subscriber to the row is the new sales representative. In Sharing rows among several subscriptions, we see cases where there are existing as well as new subscribers.

Information in the transaction log

Here we describe the information placed in the transaction log. Understanding this helps in designing efficient publications.

Assume the following data:
- SalesRep table
rep_key

name

rep1

Ann

rep2

Marc
- Customer table
cust_key

name

rep_key

cust1

Sybase

rep1

cust2

ASA

rep2
- Contact table
contact_key

name

cust_key

contact1

David

cust1

contact2

Stefanie

cust2
Now apply the following territory realignment Update statement
```
UPDATE Customer 
SET rep_key = 'rep2'
WHERE cust_key = 'cust1'
```
The transaction log would contain two entries arising from this statement: one for the BEFORE trigger on the Contact table, and one for the actual UPDATE to the Customer table.
```
SalesRepData - Publication Name
rep1 - BEFORE list
rep2 - AFTER list
UPDATE Contact 
SET contact_key = 'contact1', 
    name = 'David', 
    cust_key = 'cust1' 
WHERE contact_key = 'contact1'
SalesRepData - Publication Name
rep1 - BEFORE list
rep2 - AFTER list
UPDATE Customer 
SET rep_key = 'rep2' 
WHERE cust_key = 'cust1'
```
The Message Agent scans the log for these tags. Based on this information it can determine which remote users get an INSERT, UPDATE or DELETE.

In this case, the BEFORE list was rep1 and the AFTER list is rep2. If the before and after list values are different, the rows affected by the UPDATE statement have "moved" from one subscriber value to another. This means the Message Agent will send a DELETE to all remote users who subscribed by the value rep1 for the Customer record cust1 and send an INSERT to all remote users who subscribed by the value rep2.

If the BEFORE and AFTER lists are identical, the remote user already has the row and an UPDATE will be sent.

rep_key	name
rep1	Ann
rep2	Marc

cust_key	name	rep_key
cust1	Sybase	rep1
cust2	ASA	rep2

contact_key	name	cust_key
contact1	David	cust1
contact2	Stefanie	cust2