MobiLink Synchronization User's Guide
  Synchronization Techniques

Snapshot synchronization

Timestamp-based synchronization is appropriate for most synchronizations. However, occasionally you may want to update a snapshot of your data.

Snapshot synchronization of a table is a complete download of all relevant rows in the table, even if they have been downloaded before. This is the simplest synchronization method, but can involve unnecessarily large data sets being exchanged, which can limit performance.

You can use snapshot synchronization for downloading all the rows of the table, or in conjunction with a partitioning of the rows as described in Partitioning rows among remote databases.

When to use snapshot synchronization 

The snapshot method is typically most useful for tables that have both the following characteristics.

• Relatively few rows    When there are few rows, the overhead associated with downloading all of them is small.

• Rows that change frequently    When most rows in a table change frequently, there is little to be gained by explicitly excluding those that have not changed since the last synchronization.

A table that holds a list of exchange rates could be suited to this approach because there are relatively few currencies, but the rates of most change frequently. Depending on the nature of the business, a table that holds prices, a list of interest rates, or current news items could all be candidates.

To implement snapshot-based synchronization

1. Leave the upload scripts undefined unless remote users update the values.

2. If the table may have rows deleted, write a download_delete_cursor script that deletes all the rows from the remote table, or at least all rows no longer required. Do not delete the rows from the consolidated database; rather, mark them for deletion. You must know the row values to delete them from the remote database.

   For more information, see Writing download_delete_cursor scripts.

3. Write a download_cursor script that selects all the rows you want to include in the remote table.

The download_delete_cursor script is executed before the download_cursor script. If a row is to be included in the download stream, you need not include a row with the same primary key in the delete list. When a downloaded row is received at the remote location, it replaces a preexisting row with the same primary key.

For more information, see Writing scripts to download rows.

An alternative deletion technique 

Rather than delete rows from the remote database using a download_cursor script, you can allow the remote application to delete the rows. For example, immediately following synchronization, you could allow the application to execute SQL statements that delete the unneeded rows.

Rows deleted by the application are ordinarily uploaded to the MobiLink synchronization server upon the next synchronization, but you can prevent this upload using the STOP SYNCHRONIZATION DELETE statement, as follows.

STOP SYNCHRONIZATION DELETE;
DELETE FROM table-name
   WHERE expiry_date < CURRENT TIMESTAMP;
COMMIT;
START SYNCHRONIZATION DELETE;

Naturally, a different condition may be required in the WHERE clause, depending on the business logic of the application.

Example 

The ULProduct table in the sample application is maintained by snapshot synchronization. The table contains relatively few rows, and for this reason, there is little overhead in using snapshot synchronization.

1. There is no upload script. This reflects a business decision that products cannot be added at remote databases.

2. There is no download_delete_cursor, reflecting an assumption that products are not removed from the list.

3. The download_cursor script selects the product identifier, price, and name of every current product. If the product is pre-existing, the price in the remote table will be updated. If the product is new, a row will be inserted in the remote table.

   SELECT prod_id, price, prod_name
   FROM ULProduct

For another example of snapshot synchronization in a table with very few rows, see Synchronizing sales representatives in the Contact sample.