Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

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:

• Timestamp-based downloads
• Partitioning rows among remote databases

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.

• 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 carries out cascading deletes at the remote database, so this script also deletes all contacts who work for customers assigned to some other sales representative.

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 carried out 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 = ?