Snapshot synchronization

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 and could also cost more in telecom charges.

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 for downloading all rows 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.

Implement snapshot-based synchronization

Leave the upload scripts undefined unless remote users update the values.
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. For the latter approach, 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 download_delete_cursor scripts.
Write a download_cursor script that selects all the rows you want to include in the remote table.

Deleting rows when using snapshot synchronization

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 pre-existing row with the same primary key.

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

There is no upload script. This reflects a business decision that products cannot be added at remote databases.
There is no download_delete_cursor, reflecting an assumption that products are not removed from the list.
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 Synchronization of sales representatives in the Contact sample.

Discuss this page in DocCommentXchange.