BLOBs are LONG VARCHAR, LONG BINARY, TEXT, and IMAGE data types: values that are longer than 256 characters.

SQL Remote includes a special method for replicating BLOBs between databases.

The Message Agent uses a variable in place of the value in the INSERT or UPDATE statement that is being replicated. The value of the variable is built up by a sequence of statements of the form

SET vble = vble || 'more_stuff';

This makes the size of the SQL statements involving long values smaller, so that they fit within a single message. The SET statements are separate SQL statements, so that the BLOB is effectively split over several SQL Remote messages.

Using the verify_threshold option to minimize message size

The verify_threshold database option can prevent long values from being verified (in the VERIFY clause of a replicated UPDATE). The default value for the option is 1000. If the data type of a column is longer than the threshold, old values for the column are not verified when an UPDATE is replicated. This keeps the size of SQL Remote messages down, but has the disadvantage that conflicting updates of long values are not detected.

There is a technique allowing detection of conflicts when verify_threshold is being used to reduce the size of messages. Whenever a BLOB is updated, a last_modified column in the same table should also be updated. Conflicts can then be detected because the old value of the last_modified column is verified.

Using a work table to avoid redundant updates

Repeated updates to a BLOB should be done in a work table, and the final version should be assigned to the replicated table. For example, if a document in progress is updated 20 times throughout the day and the Message Agent is run once at the end of the day, all 20 updates are replicated. If the document is 200 KB in length, this causes 4 MB of messages to be sent.

The better solution is to have a document_in_progress table. When the user is done revising a document, the application moves it from the document_in_progress table to the replicated table. The results in a single update (200 KB of messages).

Controlling replication of BLOBs

The SQL Anywhere blob_threshold option allows further control over the replication of long values. Any value longer than the blob_threshold option is replicated as a BLOB. That is, it is broken into pieces and replicated in chunks, before being reconstituted by using a SQL variable and concatenating the pieces at the recipient site.