Q Replication and Event Publishing

Replicating Data Definition Language (DDL) operations

You can speed up and simplify the configuration and maintenance of DB2® continuous availability and disaster recovery solutions by taking advantage of the Data Definition Language (DDL) replication features in InfoSphere® Data Replication Version 10.1.3 and later.

Automatic replication of Data Definition Language (DDL) operations eliminates or greatly reduces the need for administrator interventions when tables are added or changed at the primary database. When you take advantage of this feature, the replication programs automatically subscribe newly created tables for replication and create or drop these tables at targets.

DDL replication helps ensure that the failover database truly reflects the standby database. The following topics provide more detail about DDL replication support.

Schema-level subscriptions and DDL replication
The mechanism for replicating Data Definition Language (DDL) changes such as CREATE TABLE or DROP TABLE between databases is a schema-level subscription.
How Q Capture handles DDL operations at the source database
The Q Capture program automatically replicates some Data Definition Language (DDL) operations at the source database. Other DDL changes require you to take action at the target database.
Creating schema-level subscriptions
You create a schema-level subscription to replicate supported DDL operations such as CREATE TABLE and DROP TABLE for one or more schemas in a database.
Creating profiles for table-level Q subscriptions
You can create a profile that specifies what options the replication programs should use when they automatically create table-level Q subscriptions for a schema-level subscription.
DATA CAPTURE CHANGES and schema-level subscriptions
To enable replication of CREATE TABLE operations, newly created tables that are part of a schema-level subscription must have the DATA CAPTURE CHANGES attribute set.
Starting schema-level subscriptions
You start a schema-level subscription to instruct the Q Capture program to begin capturing SQL operations such as CREATE TABLE and DROP TABLE for tables that are part of the subscription. You can also optionally start all of the included table-level Q subscriptions.
Changing table-level options used by schema-level subscriptions
You cannot update the profile that is used to create table-level Q subscriptions, but you can create a new profile with the changes that you require and then point the schema-level subscription to start using the new profile.
Stopping schema-level subscriptions
You stop a schema-level subscription to instruct the Q Capture program to quit capturing SQL operations such as CREATE TABLE and DROP TABLE for tables that are part of the subscription. You can also optionally stop all of the included table-level Q subscriptions.
Deleting schema-level subscriptions
You can delete schema-level subscriptions that are not being actively processed and optionally specify to delete all associated table-level Q subscriptions.
Schema-level subscriptions for temporal tables
You can include temporal tables and their associated history tables in schema-level subscriptions. A few additional considerations are required for system-period temporal tables and bitemporal tables.
Enabling automatic replication of newly added columns from the source table
You can set up your Q subscriptions so that when new columns are added to the source table, they are automatically added to the target table and replicated.
Adding existing columns to a Q subscription (unidirectional replication)
You can add existing columns from the source table to a unidirectional Q subscription while the replication programs are running. If the columns are not existing and your servers are at Version 10.1 or later, you use a different procedure.
Adding existing columns to a Q subscription (bidirectional or peer-to-peer replication)
You can add existing columns from the source table to a bidirectional or peer-to-peer Q subscription while the replication programs are running. If the columns are not existing and your servers are at Version 10.1 or later, you use a different procedure.
Automatic replication of ALTER TABLE ALTER COLUMN SET DATA TYPE operations
Starting with Version 10.1 on z/OS® and Linux, UNIX, and Windows, ALTER TABLE ALTER COLUMN SET DATA TYPE operations at the source table are automatically replicated. The data type of the corresponding target table column is changed and replication continues normally.
Replication of DROP COLUMN operations (z/OS)
When you drop one or more subscribed columns from a replication source table on z/OS, the columns are automatically removed from the Q subscription and dropped from the target table after you perform a REORG operation on the table space.

Parent topic: Creating Q subscriptions

Send your feedback | Information roadmap | The Q+SQL Replication Forum

Last updated: 2013-10-25