External table mirror bulk apply

CDC Replication performs this ordering on a group of source transactions, also known as a unit of work (UOW). You specify a latency threshold, which is the maximum amount of additional time that CDC Replication should delay the replication of operations to build up a large UOW. The latency threshold is expressed as the number of seconds that CDC Replication is allowed to add operations to the UOW before trying to submit it. You can also specify the amount of parallelism that should be used for apply and for image building.

The parameter to the user exit is: number of apply threads:latency threshold:number of image builder threads, where:

number of apply threads

Mandatory. An integer starting at 1.

latency threshold

Mandatory . An integer in seconds starting at 1. The default is 60. The default is used if no value is specified.

number of image builder threads

Optional. An integer starting at 1. The default is the number of apply threads.

For example, to use one apply thread, the default latency threshold, and one image builder thread, you would specify 1::1. This is the recommended setting. To specify one apply thread, a latency threshold of 30 seconds, and the default number of image builders, you would specify 1:30. To specify two apply threads, a latency threshold of 45 seconds, and three image builder threads, you would specify 2:45:3.

Update operations are transformed into a delete followed by an insert. If an inserted row is subsequently deleted in the same UOW, both the insert and delete are thrown away. Multiple updates of the same row in the same UOW are transformed into a single delete followed by a single insert of the last set of column values. For this reason, this form of apply is sometimes called a “net effect” apply. For each table, all of the delete operations are performed first, followed by all of the insert operations. The tables are applied in the order that the first operation on a table is seen in the current UOW.

For example, if the source system had performed the following operations:

INSERT INTO TABLE1(keycol, othercol) VALUES (keycolval1,othercolval1);
DELETE FROM TABLE1 WHERE keycol = keycolval2;
DELETE FROM TABLE2 WHERE keycol = keycolva11;
INSERT INTO TABLE2(keycol, othercol) VALUES (keycolval1,othercolval1);
UPDATE TABLE1 SET othercol = othercolval3 WHERE keycol = keycolva13;
INSERT INTO TABLE3 (keycol, othercol) VALUES (keycolval1,othercolval1);
INSERT INTO TABLE2(keycol, othercol) VALUES (keycolval2,othercolval2);
INSERT INTO TABLE3 (keycol, othercol) VALUES (keycolval2,othercolval2);
INSERT INTO TABLE2(keycol, othercol) VALUES (keycolval3,othercolval3);
DELETE FROM TABLE2 WHERE keycol = keycolval3;
INSERT INTO TABLE1(keycol, othercol) VALUES (keycolval4,othercolval4);

CDC Replication applies them on the target system as:

DELETE FROM TABLE1 WHERE keycol = keycolval2;
DELETE FROM TABLE1 WHERE keycol = keycolva13;
INSERT INTO TABLE1(keycol, othercol) VALUES (keycolval1;
INSERT INTO TABLE1 (keycol, othercol) VALUES (keycolval3,othercolval3);
INSERT INTO TABLE1(keycol, othercol) VALUES (keycolval4,othercolval4);

DELETE FROM TABLE2 WHERE keycol = keycolva11;
INSERT INTO TABLE2(keycol, othercol) VALUES (keycolval1,othercolval1);
INSERT INTO TABLE2(keycol, othercol) VALUES (keycolval2,othercolval2);

INSERT INTO TABLE3 (keycol, othercol) VALUES (keycolval1,othercolval1);
INSERT INTO TABLE3 (keycol, othercol) VALUES (keycolval2,othercolval2);

Reordering of the operations allows CDC Replication to use “external tables” to perform the deletes and inserts. For each UOW, all of the deletes from a given table are performed by a single delete with an external table statement, and all of the inserts from a given table are performed by a single insert with an external table statement. The pseudo-syntax for delete is: delete from tablename where keycolnamelist IN (select * from externaltablename). The pseudo-syntax for an insert is: insert into tablename select * from externaltablename.

If more than one apply thread is specified, the tables are distributed round-robin to the apply threads in the order that the first operation on a table is seen. In the above example, if the number of apply threads had been set to 2, then TABLE1 and TABLE3 would have been applied by thread 0, and TABLE2 would have been applied by thread 1.

If any exceptions occur when trying to apply the operations for a given table by using external tables, replication for the subscription stops. Unlike the other fast apply algorithms, external table mirror bulk apply does not have the ability to roll back the UOW transaction and reapply it serially in the original order.