Synchronizing customers in the Contact sample

The synchronization scripts for the Customer table illustrate timestamp-based synchronization and partitioning rows. Both of these techniques minimize the amount of data that is transferred during synchronization while maintaining consistent table data.

See:

Business rules

The business rules governing customers are as follows:

Customer information can be modified at both the consolidated and remote databases.
Periodically, customers may be reassigned among sales representatives. This process is commonly called territory realignment.
Each remote database contains only the customers they are assigned to.

Downloads

download_cursor The following download_cursor script downloads only active customers for whom information has changed since the last successful download. It also filters customers by sales representative.

SELECT cust_id, Customer.name, Customer.rep_id FROM Customer key join SalesRep WHERE Customer.last_modified >= ? AND SalesRep.ml_username = ? AND Customer.active = 1

download_delete_cursor The following download_delete_cursor script downloads only customers for whom information has changed since the last successful download. It deletes all customers marked as inactive or who are not assigned to the sales representative.

SELECT cust_id FROM Customer key join SalesRep WHERE Customer.last_modified >= ? AND ( SalesRep.ml_username != ? OR Customer.active = 0 )

If rows are deleted from the Customer table at the consolidated database, they do not appear in this result set and so are not deleted from remote databases. Instead, customers are marked as inactive.

When territories are realigned, this script deletes those customers no longer assigned to the sales representative. It also deletes customers who are transferred to other sales representatives. Such additional deletes are flagged with a SQLCODE of 100 but do not interfere with synchronization. A more complex script could be developed to identify only those customers transferred away from the current sales representative.

The MobiLink client performs cascading deletes at the remote database, so this script also deletes all contacts who work for customers assigned to some other sales representative.

Uploads

Customer information can be inserted, updated, or deleted at the remote database. The scripts corresponding to these operations are as follows:

upload_insert The following upload_insert script adds a row to the Customer table, marking the customer as active:

INSERT INTO Customer( cust_id, name, rep_id, active ) VALUES ( ?, ?, ?, 1 )

upload_update The following upload_update script modifies the customer information at the consolidated database. Conflict detection is not done on this table.

UPDATE Customer SET name = ?, rep_id = ? WHERE cust_id = ?

upload_delete The following upload_delete script marks the customer as inactive at the consolidated database. It does not delete a row.

UPDATE Customer SET active = 0 WHERE cust_id = ?

Discuss this page in DocCommentXchange.