RTL Compilation and Bulk Apply

During compilation, RTL rearranges data to be replicated by clustering the data together based on each table, and each insert, update, and delete operation, and then compiling the operations into net row operations.

RTL distinguishes different data rows by the primary key defined in a replication definition. If there is no replication definition, all columns except for text and image columns are regarded as primary keys.

If a replicate table contains multiple unique keys, the primary key in the table replication definition must contain all columns which are named in the unique indexes. Otherwise, replication may produce duplicate key errors.

For the combinations of operations found in normal replication environments, and given a table and row with identical primary keys, RTL follows these compilation rules for operations:

An insert followed by a delete results in no operation.
A delete followed by an insert results in no reduction.
An update followed by a delete results in a delete.
An insert followed by an update results in an insert where the two operations are reduced to a final single operation that contains the results of the first operation, overwritten by any differences in the second operation.
An update followed by another update results in an update where the two operations are reduced to a final single operation that contains the results of the first operation, overwritten by any differences in the second operation.

Other combinations of operations result in invalid compilation states.

Example 1

This is an example of log-order, row-by-row changes. In this example, T is a table created earlier by the command: create table T(k int , c int)

1. insert T values (1, 10)
2. update T set c = 11 where k = 1
3. delete T where k = 1
4. insert T values (1, 12)
5. delete T where k =1
6. insert T values (1, 13)

With RTL, the insert in 1 and the update in 2 can be converted to insert T values (1, 11). The converted insert and the delete in 3 cancel each other and can be removed. The insert in 4 and the delete in 5 can be removed. The final compiled RTL operation is the last insert in 6:

insert T values (1, 13)

Example 2

In another example of log-order, row-by-row changes:

1. update T set c = 14 where k = 1
2. update T set c = 15 where k = 1
3. update T set c = 16 where k = 1

With RTL, the update in 1 and 2 can be reduced to the update in 2. The updates in 2 and 3 can be reduced to the single update in 3 which is the net-row change of k = 1.

Replication Server uses uses an insert, delete, and update table in an in-memory net-change database to store the net-row changes it applies to the replicate database. Net-row changes are sorted by replicate table and by type of operation—insert, update, or delete—and are then ready for bulk interface.

RTL directly loads insert operations into the replicate table. Since Sybase IQ does not support bulk update and delete, RTL loads update and delete operations into temporary worktables that RTL creates inside the IQ temporary store. RTL then performs join-update or join-delete operations with the replicate tables to achieve the final result. The worktables are created and dropped dynamically.

In Example 2, where compilation results in update T set c = 16 where k = 1:

RTL creates the #rs_uT(k int, c int) worktable.

RTL performs an insert into the worktable:

insert into #rs_uT(k, c) location ‘idemo.db’ {select * from rs_uT}

RTL performs the join-update:

update T set T.c=#rs_uT.c from T,#rs_uT where T.k=#rs_uT.k

As RTL compiles and combines a larger number of transactions into a group, bulk operation processing improves; therefore, replication throughput and performance also improves. You can control the amount of data that RTL groups together for bulk apply by adjusting RTL sizes with configuration parameters.

There is no data loss, although RTL does not apply row changes in the same order in which the changes are logged:

For different data rows, the order in which row changes are applied does not affect the result.
In the same row, applying delete before insert after compilation maintains consistency.