StarQuest Technical Documents

How to Avoid a Baseline Replication after Source Table Schema Change

Updated: 25 Ocober 2019
Product: StarQuest Data Replicator
Version: 3.63 or later
Article ID: SQV00DR023

Abstract

NOTE: This procedure may not be necessary for source systems supported by the Automatic DDL Replication feature added in SQDR 5.10. Contact StarQuest Support for advice.

 

SQDR Plus detects source table schema alterations, such as the addition or removal of a column, and will notify the SQDR client that the subscription must be updated with the current schema information. The subscription will automatically be stopped until its definition is updated using either the Merge with Source or the Rebuild Column List function (in the Columns dialog of the subscription properties). The implication is that the destination table must be dropped and re-created, and that a new baseline run. Normally this is not a problem but it could cause significant downtime if the table is very large.

The document provides a method for avoiding downtime due to an altered source table schema. Specifically, it illustrates how to minimize the time needed to re-subscribe to the new version of the table, while exploiting the existing destination table and data.

These instructions are intended to be used before any source table schema changes are made.

Summary of Tasks

The following summarizes the steps involved.

1. Prepare the SQDR incremental replication (IR) group by modifying the baseline replication schedule property.
2. Alter the source table schema, as desired.
3. Update the SQDR subscription with the schema change.
4. Modify the destination table schema to match the new source table definition.
5. Resume the updates for the IR group.

Solution

Step 1: Prepare the SQDR incremental replication (IR) group.

The IR group should be temporarily modified to prevent SQDR from automatically starting baseline replications.

1. Launch the Data Replicator Manager.
2. Locate the IR group containing the affected subscription. Right-click it and select Pause Updates.
3. Right-click the group again and select Properties. Under the Schedule tab, record the current setting.  Change the schedule property  to run baselines "On Demand". Click OK to save the group.

Step 2: On the DB2 host, alter the source table schema.

It is critical that updates to the table not occur while making schema changes.

1. Halt all updates to the source table. If necessary, create an exclusive lock on the table or restrict access.
2. Alter the source table as needed. For example, add a new column to the table, supplying a default value for the newly added column if desired.
3. Restore access to the source table and make it available for updates.
   

Step 3: Update the SQDR subscription with the schema change.

The subscription must be updated with the current table definition.

1. Launch the Data Replicator Manager.
2. Locate the affected subscription from under the Sources folder. The subscription should have a status of "Stopped" and be flagged with a yellow exclamation point. On the right pane, double-click the most recent replication event and verify that the message indicates that source table has been altered.
3. Locate the IR group containing the affected subscription. Right-click it and select Pause Updates.
4. Double-click the affected subscription from either the Sources or under the Members folder for the IR group.
   1. Modify the subscription destination properties to select the options Use existing table for baseline, Append replicated rows to existing data, and Manual synchronization.
   2. On the Columns dialog, use either Merge with Source or Rebuild Column List to update the subscription with the new source schema. If the source table contains a newly added column, verify that this column is displayed in the column list and take note of the data type used as the destination data type.
   3. Click OK save the subscription. Accept the warning that a new baseline will be required. If an error occurs stating that the subscription could not be validated, click Yes to save it anyway.

Step 4: Modify the destination table schema.

If the destination table must match the new source table schema, alter it manually by issuing the appropriate SQL commands. Be sure to specify the correct data type to use, as noted in step 4b above.

Step 5: Resume and run the group.

1. Launch the Data Replicator Manager.
2. Locate the IR group containing the affected subscription. Right-click it and select Resume Updates.
3. Right-click it again and select Run Group. Since the manual synchronization option is in effect, running the group will not replicate any data and the destination table contents will be unaffected. At this point the group and all member subscriptions should be in an "Active" state.
4. Right-click the group once again and select Properties. Revert the scheduling property back to original value from step 2.

At this point it is safe to resume updates the source table and all updates should be reflected on the destination table.

Important Note: Once a subscription is configured for Manual synchronization, changing this setting will necessitate a new baseline.