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

    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.

Deleting rows

Rather than deleting rows from the consolidated database, mark them for deletion. You must know the row values to delete them from the remote database. Select only unmarked rows in the download_cursor script and only marked rows in the download_delete_cursor script.

The download_delete_cursor script is executed before the download_cursor script. If a row is to be included in the download, 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.

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 server upon the next synchronization, but you can prevent this upload using the STOP SYNCHRONIZATION DELETE statement. For example, 

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

See Writing download_delete_cursor scripts.

Snapshot 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 is updated. If the product is new, a row is 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.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0