Incremental Atomic Materialization

You can avoid long-running transactions at the replicate database by using the incrementally option.

The incremental option sends materialization data to the replicate database in a series of transactions, rather than in one large transaction. Otherwise, incremental and non-incremental atomic materialization are identical. Subscription data is available but incomplete until materialization has completed and the subscription is validated.

Rows are removed from the stable queue after they have been successfully inserted, so less partition space is required. You can truncate the database transaction log during materialization, if necessary.

Users at the replicate site will see partial subscription data during materialization, which may invalidate some queries. However, they will have access to inserted rows sooner, which may be beneficial.

The publishers_rep replication definition presented in the topics on how to manage replicated tables is used in the following example to create a subscription. The create subscription command in the example has no where clause, so the subscription causes Replication Server to replicate all the rows in the replication definition. The incrementally keyword ensures that the replicate database transaction log does not become full. Clients at the replicate site can be suspended or warned that the publishers table is materializing and will contain incomplete data until the process has completed.

create subscription publishers_sub
for publishers_rep
with replicate at SYDNEY_DS.pubs2
incrementally