DDL resiliency

Prerequisites

DDL Awareness must be enabled. This means that the Db2® for z/OS® version must be 12 or later and DATACAPTURE must be enabled on SYSTABLES and SYSCOLUMNS. SYSTABLES and SYSCOLUMNS must be reorged if the client upgraded to Db2 12 and has not reorged these tables since the upgrade. If the target does not support describe-while-mirror, then when an ALTER COLUMN or DROP COLUMN DDL operation is seen the subscription stops, event 9526 is issued, and you must describe the subscription and for DROP COLUMN reassign the table before the subscription can resume. All CDC Replication Java™ targets whose CDC Replication version is at least as new as the version of a CDC Replication source that supports DDL resiliency support describe-while-mirror. Therefore, it is highly recommended that any client that intends to use this feature ensure that the CDC Replication target version is at least as new as the source version. All other CDC Replication targets, such as the native z/OS target, do not support describe-while-mirror.

How to enable

DDL resiliency is disabled by default. To enable, set the instance-level CDC Replication system property mirror_on_ddl_operation to RESILIENT. To disable, delete the setting of the property or set it to STOP (the current default). The instance must be restarted before these changes take effect.

Handling of different DDL operations

Here is how the supported DDL operations are handled:

ADD COLUMN

The CDC Replication metadata is automatically updated to include the new column. The column is not added to the target table. The added column is omitted from the mapping. Event 9523 is issued. The event indicates the DDL type (ADD COLUMN), the table name, and the column name. Mirroring continues.

ALTER COLUMN

The CDC Replication metadata is automatically updated to reflect the altered source column definition. The target column definition is not altered. The altered source column values might be truncated to fit into the target column if the old data type is too small to hold the post-DDL source column values. Event 9524 is issued. The event indicates the DDL type (ALTER COLUMN), the table name, and the column name. DDL handling also differs depending on whether the target supports describe-while-mirror:

Supports describe-while-mirror

The new source column definition is automatically described to the target and mirroring continues.

Does not support describe-while-mirror

Event 9526 is issued and the subscription stops. You must describe the subscription before restarting the subscription. If multiple DDLs occurred on this table and the table is mapped in more than one subscription, the table must be refreshed.

DROP COLUMN

The CDC Replication metadata is automatically updated to reflect the dropped column. The column is not dropped from the target table. The target column that was mapped to the dropped source column is mapped to the target database default for that column. Event 9525 is issued. The event indicates the DDL type (DROP COLUMN), the table name, and the column name. DDL handling also differs depending on whether the target supports describe-while-mirror:

Supports describe-while-mirror

The dropped column is automatically described to the target and mirroring continues.

Does not support describe-while-mirror

Event 9526 is issued and the subscription stops. You must describe the subscription and reassign the table before restarting the subscription. If multiple DDLs occurred on this table and the table is mapped in more than one subscription, or if the reassign was not done by using the dmreassign utility with the -a option, the table must be refreshed.

Special considerations

Note the following special considerations:

Source tables mapped in more than one subscription

The first subscription that encounters the DDL while mirroring updates the metadata and sets the requiresdescribe flag for that table for all subscriptions that have that table mapped. If all of the subscription are running when this occurs, each subscription automatically performs the describe by using the describe-while-mirror feature. However, if any subscription that has the table mapped is not running when a running subscription with that table mapped encounters the DDL, you must describe the subscription and for DROP COLUMN reassign the table before restarting the subscription.

Refresh

If a table is flagged for refresh and the table definition in the CDC Replication metadata does not match the latest definition in the database system catalog, critical DDL Event 9505 is issued. You must re-add the table definition, re-describe the subscription, and reassign the table. This is the same behavior as if DDL resiliency is not enabled. This situation can occur if a DDL operation occurred on the table while the subscription was stopped, or the subscription was stopped before the DDL was encountered. To avoid this, ensure that the subscriptions are running when a DDL operation occurs. Note that this is the opposite of the DDL handling procedure for DDL awareness.