Disjoint partitioning

Partitioning is controlled by the download_cursor and download_delete_cursor scripts for each table involved in synchronization. These scripts take two parameters, a last download timestamp and the MobiLink user name supplied in the call to synchronize.

To partition a table among remote databases

Include in the table definition a column containing the synchronization user name in the consolidated database. You need not download this column to remote databases.

Include a condition in the WHERE clause of the download_cursor and download_delete_cursor scripts requiring this column to match the script parameter.

The script parameter can be represented by a question mark or a named parameter in the script. For example, the following download_cursor script partitions the Contact table by employee ID.

SELECT id, contact_name
FROM Contact
WHERE last_modified >= {ml s.last_table_download}
AND emp_id = {ml s.username}

See download_cursor table event and download_delete_cursor table event.

Example

The primary key pool tables in the CustDB sample application are used to supply each remote database with its own set of primary key values. This technique is used to avoid duplicate primary keys, and is discussed in Using primary key pools.

A necessary feature of the method is that primary key-pool tables must be partitioned among remote databases in a disjoint fashion.

One key-pool table is ULCustomerIDPool, which holds primary key values for each user to use when they add customers. The table has three columns:

pool_cust_id A primary key value for use in the ULCustomer table. This is the only column downloaded to the remote database.
pool_emp_id The employee who owns this primary key.
last_modified This table is maintained using the timestamp technique, based on the last_modified column.
For information about timestamp synchronization, see Timestamp-based downloads.

The download_cursor script for this table is as follows.

SELECT pool_cust_id
FROM ULCustomerIDPool
WHERE last_modified >= {ml s.last_table_download}
 AND pool_emp_id = {ml s.username}

When not using a variable or named parameter, you can use a join or sub-selection that includes the ? placeholder.

See Synchronizing customers in the Contact sample and Synchronizing contacts in the Contact sample.

Send feedback about this page via email or DocCommentXchange