RTL compilation and bulk apply

During compilation, RTL rearranges data that is 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.

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 – there is no reduction

• An update followed by a delete – results in no operation

• An insert followed by an update – results in an insert where the two operations are reduced to a final single operation. The final single operation 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. The final single operation contains the results of the first operation, overwritten by any differences in the second operation.

Other combinations of operations result in invalid compilation states.

0. 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 all together. The final compiled RTL operation is the last insert in 6:

insert T values (1, 13)

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 an insert, delete, and update table in an in-memory net change database to store the net row changes which it applies to the replicate database. Net row changes are sorted by replicate table and by type of operations—insert, update, or delete—and are then ready for bulk interface. RTL loads insert operations into the replicate table directly. Since Sybase IQ and Adaptive Server do not support bulk update and delete, RTL loads update and delete operations into temporary work tables 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 work tables are created and dropped dynamically.

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

1. RTL creates the #rs_uT(k int, c int) work table.

2. RTL performs an insert into the work table with this statement:

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

3. RTL performs the join-update:

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