ALTER Processing

The Apply section of the Advanced dialog for properties of an incremental group contains the settings DDL Replication, ADD, DROP, and MODIFY that control how SQDR handles ALTER changes to the source tables. DDL Replication determines when the subscription is run in reaction to a source ALTER, while ADD, DROP and MODIFY determine what action is taken for the destination table. The default values are defined in the Replication tab of SQDR Service Properties.

Note that these settings determine the action that is taken on the destination table; modifications to the intermediate Db2 LUW staging tables used by SQDR Plus are handled automatically.

DDL Replication: choose the desired value from the dropdown field. This field determines whether to perform DDL replication of source table ALTER statements such as ADD or DROP columns automatically or when the I/R group or subscription is run.

*DEFAULT - use the SQDR Service property (Replication tab) for DDL Replication
On Demand - the user must select the group or subscription and select RUN to apply the DDL change
Automatic - automatically perform the DDL change
Off - disables "DDL Replication" functionality.  The behavior is equivalent to SQDR prior to 5.10 (see below for details).

ADD: choose the desired value from the dropdown field. This field determines how to react to an ALTER TABLE ADD operation of the source table.

          *DEFAULT - use the SQDR Service property (Replication tab) for ADD.
           Ignore - ignore the Column Add, leaving the destination table unchanged with respect to the newly added source column
           Perform - Adds the column in the destination table (with null attribute)

DROP: choose the desired value from the dropdown field. This field determines whether to perform. This field determines how to react to an ALTER TABLE DROP operation on the source table.

          *DEFAULT - use the SQDR Service property (Replication tab) for DROP.
           Ignore - Preserves existing destination column data, and alters the column to permit null values if originally created as Non-Nullable.
           Perform - Drops the column in the destination table

MODIFY: choose the desired value from the dropdown field. This field determines how to react to an ALTER TABLE ALTER COLUMN (or ALTER TABLE MODIFY) operation on the source table that results in a change of data type, data type name, precision, scale, or nullability.

          *DEFAULT - use the SQDR Service property (Replication tab) for MODIFY.
           Ignore - ignore the ALTER COLUMN operation, leaving the destination table unchanged. Replication may encounter issues such as truncation errors.
           Perform - Modifies the column in the destination table

No action occurs if you drop or modify a column that is not being replicated.

In SQDR prior to 5.10, an ALTER to a source table would cause a subscription to be flagged as needing a baseline; to recover, the user needed to view the subscription properties and select Merge with Source on the Columns tab, and then Run the subscription or group.

In SQDR 5.10 & later, if DDL Replication is set to On Demand, then the subscription is flagged as needing a baseline, but it is only necessary to Run the subscription or group to perform the ADD, DROP or MODIFY actions as configured. However, you have the option of inspecting and possibly modifying the changes to be made by viewing the subscription properties and selecting Merge with Source on the Columns tab before running the subscription or group.  

If DDL Replication is set to Automatic, then the ADD, DROP or MODIFY actions will occur automatically for supported DBMS platforms.

Limitations

Automatic ALTER handling with continuous replication is supported from the following source DBMS platforms

For other source DBMS platforms (SQL Server and Informix), DDL changes require a new baseline, which is handled automatically if Automatic Snapshot is enabled on the Advanced panel of the Incremental Group.

Automatic ALTER handling is limited to:

Changes to keys (primary key, foreign key, unique constraint, etc) will result in a flagged subscription (pre-5.10 behavior).

Keywords that specify column position e.g. ADD COLUMN BEFORE (Db2 for i), ADD COLUMN FIRST/AFTER (MySQL) are not supported.

Automatic ALTER handling does not occur for subscriptions where the destination table is configured as Use existing table for baseline or Create if does not exist, else use existing.

It is possible that some destination DBMS platforms may not support every ALTER operation, and manual intervention may be required.

DBMS-specific information for ALTER processing on the destination:

ADDRPYLE SEQNBR(1500) MSGID(CPA32B2) RPY(I)

NOTE: This setting is not needed for IBM i 7.4 TR1 and IBM i 7.3 TR7 & later.

For example, an ALTER COLUMN SET NOT NULL operation on the source will have no affect on a Db2 z/OS destination; the replication statistics will show a 'Successful' status, but no DDL operation is performed on the destination:

DDL replicated successfully, with elapsed time 00:00:00. 0 ALTER TABLE statement(s) generated for destination.

This is because Db2 z/OS will not allow an existing column to be set to NON-NULL, so SQDR is not attempting the change, and instead relies upon the source database to enforce the non-null characteristic.

Other Considerations: