BLOBs

BLOBs include the LONG VARCHAR, LONG BINARY, TEXT, and IMAGE data types.

When SQL Remote replicates an INSERT or UPDATE statement, it uses a variable in place of the BLOB value. That is, the BLOB is broken into pieces and replicated in chunks. At the recipient database, the pieces are reconstituted by using a SQL variable and concatenated. The value of the variable is built up by a sequence of statements of the form:

SET vble = vble || 'more_stuff';

The variable makes the size of the SQL statements involving long values smaller, so 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.

Controlling replication of BLOBs

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 statement). The default value for the option is 1000. When the data type of a column is longer than the threshold, old values for the column are not verified when an UPDATE statement is replicated. This reduces the size of SQL Remote messages, but has the disadvantage that conflicting updates of long values are not detected.

Use the following technique to detect conflicts when the verify_threshold is being used to reduce the size of messages.

To detect conflicts with BLOB values when the verify_threshold is set

Configure your databases so that whenever a BLOB is updated, a last_modified column in the same table is also updated.
Configure your publications so that the last_modified column is replicated with the BLOB column.

When the BLOB column and the last_modified column are replicated, the values in the last_modified column can be verified. If there is a conflict with the last_modified column, then there is a conflict with the BLOB column as well.

See verify_threshold option [SQL Remote].

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 SQL Remote is run once at the end of the day, all 20 updates are replicated. If the document is 200 KB in length, then 4 MB of messages are sent.

It is recommended that you use 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. This results in a single update (200 KB of messages).

BLOBs

Controlling replication of BLOBs

Using the verify_threshold option to minimize message size

To detect conflicts with BLOB values when the verify_threshold is set

Using a work table to avoid redundant updates

See also