Loading (Refreshing) Tables Externally While Mirroring is Running

External Table Load (Refresh) While Mirroring is a feature that allows clients to refresh a table while mirroring is running. Without this feature, all refreshes, both internal and external, must begin and finish before mirroring can start. When you flag a table for internal refresh, then start a subscription for mirroring, all tables that were flagged are refreshed before mirroring actually begins. Each internal refresh is done serially; there is no parallelism when internally refreshing tables in the same subscription.  Any regular external table loads need to completed before mirroring actually begins or event 9730 is issued and the subscription stops. For any tables flagged for External Refresh While Mirror when the subscription starts, the user is allowed to start the external refresh after any internal refreshes have completed. Once all internal refreshes have completed, event 9765 will be issued for each table flagged for External Refresh While Mirror. When the user sees this message, they can call the dmmarkexternalunloadstart utility, then begin the external load.  An error will be raised if they attempt to call dmmarkexternalunloadstart before this point. When they finish externally loading the table, they call dmmarkexternalunloadend to let CDC knows the refresh has completed. The refreshes can be done in parallel; there is no limit on the number of concurrent external table refreshes while mirroring.

An external refresh-while-mirror must be completed and the table set to active before mirroring stops. A table becomes active only after dmmarkexternalunloadend is called and all mirrored operations captured during the refresh have been successfully applied. If the subscription stops at any point before the table becomes active, the entire external table load process must be repeated

 Enabling External Table Load While Mirroring

Method 1: Set the CDC system property table_load_while_mirror_type to EXTERNAL at the instance level (the default is NONE). This causes all tables flagged for refresh to be treated as if they were explicitly flagged for external refresh while mirror. Then before the subscription starts, flag each table that will be externally refreshed using standard methods such as MC, CHCCLP, dmflagforrefresh, or the Java API. This property can also be set at the subscription level and table level if the client only wants to externally load while mirror tables for a specific subscription or specific tables.

Method 2: Use the -em option with the dmflagforrefresh utility before the subscription starts. This flags the specified table for external table load while mirror. Example:

dmflagforrefresh -I <instancename> -s <subscriptionname>  -t <schema.table> -em

How it Works

For each table flagged for external refresh while mirror, the user calls the dmmarkexternalunloadstart utility, then performs the external refresh. They can do all external refreshes in parallel or they can be done one-at-a-time.  Any operations that are mirrored while a table is being loaded are temporarily stored in a “spill queue” on the target. There is a separate spill queue for each table being externally loaded while mirroring.  Once the user indicates they have finished refreshing a table by calling the dmmrkexternalunloadend utility, the operations in the spill queue for that table are read and applied. The table load is considered to have completed only when all spilled operations have been successfully applied and the corresponding bookmark updated and committed on the target. Only then does the table become active.

A spill queue stores the operations in-memory but will spill the operations to disk if memory becomes unavailable. Spill queues are not persisted when a subscription stops. So if a subscription stops, any spill queues associated with tables in that subscription are lost.

Operations in the spill queue are applied by a separate “drain pipeline” that runs in parallel with the main mirror pipeline. There is no parallelism within a drain pipeline. The number of image builder and apply threads is set to 1.  The drain pipeline will use Mirror Bulk Apply (MBA) or fast apply if the parent pipeline is using it. The fast apply algorithm will be the same as what the parent is using.  Here is a diagram showing the main target pipeline and a drain pipeline for a subscription that is not using fast apply:

Restrictions/Issues

Target tables with referential integrity constraints are not supported, but this is not enforced. It is up to the client to handle any referential integrity issues on the target when externally loading the table. Native Z, Classic, or System I sources or targets are not supported.

There is a small chance that the source table will be so busy during the load and after it ends that the drain pipeline never catches up. This condition is called “infinite draining”. If this occurs, the client will need to stop the subscription controlled to get the drain pipeline to stop and to allow the table to become active. An indication of this would be the table not becoming active long after the user called dmmarkexternalunloadend. Please note that this scenario is very rare and has never been encountered, so it may just be theoretical. The subscription will not stop until all operations drained from the spill queue have been applied.

Conclusion

External Table Load (Refresh) While Mirroring allows a client to load/refresh tables much faster than when CDC loads the tables internally. In addition to being able to load multiple tables at the same time, tables are being loaded while mirroring is running. So if, for example, a table takes 3 days to load, mirroring will no longer fall behind by 3 days. Instead, since mirroring will have been running the entire time, latency will be no worse than it would have been if the table had not been refreshed. There is an extra expense to store the operations on the table in a “Spill Queue” while the refresh is occurring, but unless the table is very busy during this time the overhead is small.