SQL Remote User's Guide
Principles of SQL Remote Design
How data types are replicated
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 Adaptive Server Anywhere 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.
Blobs can be replicated to and from Adaptive Server Enterprise as long as they fit into the Message Agent memory.
Sybase Open Client CTLIB applications that manipulate the CS_IODESC structure must not set the log_on_update member to FALSE.
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.
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).
The Adaptive Server 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.
By setting BLOB_THRESHOLD to a high value in remote Adaptive Server Anywhere databases, blobs are not broken into pieces, and operations can be applied to Adaptive Server Enterprise by the Message Agent. Each SQL statement must fit within a message, so this only allows replication of small blobs.