Data distribution configurations (Q Replication to SQL Replication)

In a data distribution or fan-out configuration that uses Q Replication and SQL Replication, transactions are replicated from a source table to a target table in Q Replication, and the target table then acts as the source table for one or more SQL Replication Apply programs.

The fan-out configuration uses a three-tier model:

  • Tier 1 is the Q Replication source, a read-write table from which changes are gathered by the Q Capture program.
  • Tier 2 is the Q Replication target, a read-only consistent-change data (CCD) table that is updated by the Q Apply program.
  • Tier 3 consists of one or more Apply programs, which pull data from the CCD table and apply it to read-only targets.

Figure 1 shows the components of a data distribution configuration that uses Q Replication and SQL Replication.

Figure 1. Data distribution or fan-out configuration from Q Replication to SQL Replication
Data is replicated from a source to a CCD table and then distributed to multiple target tables

Fan-out configurations take advantage of the low latency and high throughput capabilities of Q Replication and the data distribution capabilities of SQL Replication.

The Q Capture program at tier 1 sends WebSphere® MQ messages that contain transactions from the source table, and the multithreaded Q Apply program rapidly updates the CCD table to stay in synch with a high volume of changes from the source. Then, multiple Apply programs can simultaneously read the CCD table and distribute the same transactions to hundreds of identical targets. The Apply programs can update these targets in real time or batch mode based on the business need.

Important for Version 10.1 on Linux, UNIX, and Windows: If the source database is Db2® 10.1 for Linux®, UNIX, and Windows with multiple Db2 pureScale® members, Q Replication does not support CCD target tables. With Version 10.1 as a source, CCD targets are only supported in single-member databases with the Q Capture compatibility parameter set to 0907 or lower.

SQL Replication control tables at the Q Apply server

For fan-out configurations, the Q Apply program requires two Capture control tables, IBMSNAP_REGISTER and IBMSNAP_PRUNCNTL, to be created at the database or subsystem where the Q Apply program runs. Q Apply updates the SYNCHPOINT and SYNCHTIME columns in both tables and the CCD_OLD_SYNCHPOINT column in the IBMSNAP_REGISTER table to inform the Apply program about the status of replication that involves registered CCD tables.

The IBMSNAP_REGISTER table also holds metadata about Q Replication CCD tables that are registered as sources for SQL Replication. In addition, when you register the CCD table for SQL Replication, the following two columns in the IBMSNAP_REGISTER table are updated to identify which Q Apply program is managing the SQL registration:

  • The CD_OWNER column is updated with the Q Apply schema.
  • The CD_TABLE column is updated with the name of the receive queue that was specified for the Q subscription.

Although the Q Apply program needs only these two Capture control tables, to set up a fan-out configuration you use the ASNCLP command-line program or Replication Center to create an entire set of Capture control tables at the Q Apply server.

SQL_CAP_SCHEMA parameter for the Q Apply program

Before a Q Apply program can be used to manage registrations for SQL Replication, you must provide the name of the Capture schema in which the CCD tables are registered. You specify this information in the SQL_CAP_SCHEMA parameter of the Q Apply program.

Each Q Apply program can manage registrations for only one Capture schema.

Target CCD table

When you create Q subscriptions to replicate transactions between tiers 1 and 2 in Q Replication, you define the target table as a CCD table. You then register the table as an external CCD table for SQL Replication.

Although all types of CCD tables are supported as the second tier in this configuration, in most cases you specify the CCD table as condensed and complete. A complete CCD table contains every row of interest from the source table and is initialized with a full set of source data. A condensed CCD table contains one row for every key value in the source table and contains only the latest value for the row.

Initial loading of data

When you create Q subscriptions for the target CCD tables, you can specify that the Q Apply program load the targets with data from the source when the Q subscriptions are started for the first time. You can also specify a manual load in which the Q Apply program waits for you to invoke your own load utility, and then begins applying transactions from the source table after you signal that the loading process is complete.

Important: If you plan to load the CCD table, you must specify a complete CCD table when you create the Q subscription.

During the loading process, the Q Apply program updates the SYNCHPOINT and SYNCHTIME columns in the IBMSNAP_REGISTER table to null to ensure that the Apply program does not begin to fetch data from the CCD table until the loading of the CCD table is finished.

After the CCD table is loaded and the Q Apply program applies all changes from the source that were temporarily held in the spill queue, Q Apply updates the IBMSNAP_REGISTER and IBMSNAP_PRUNCNTL tables with special values before changing the state of the Q subscription to A (active) at the target.

You can also specify that the target tables at tier 3 are loaded with data from the source CCD table at tier 2 when you create the subscription set for the registered CCD tables.