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.

The following table describes how Q Capture handles different types of DDL operations and what you need to do for any affected Q subscriptions.

Table 1. How Q Capture handles DDL changes to the source database and what you need to do
DDL operation	How it is handled	What you need to do
CREATE TABLE	Linux®, UNIX, and Windows Automatically replicated if the new source table matches the schema- and table-naming pattern in a schema-level subscription. When the Q Capture program detects a CREATE TABLE operation in the log that matches a schema-level subscription, it informs the Q Apply program to create a matching target table. A table-level Q subscription is also created that maps the new source and target tables. z/OS® No automatic replication of CREATE TABLE operations.	Linux, UNIX, and Windows Ensure that newly created source table matches the schema- and table-naming pattern in a schema-level subscription. z/OS Create a table-level Q subscription for the new source table and use the replication administration tools to create a matching target table, or use an existing target table.
DROP TABLE	Linux, UNIX, and Windows Automatically replicated if the source table is part of a schema-level subscription. When the Q Capture program detects a DROP TABLE operation in the log that matches a schema-level subscription, the associated table-level Q subscriptions for all queues are also dropped. z/OS Q Capture leaves the Q subscription active, but there are no log records to read for the source table. On z/OS, the ASN0197W warning message is issued.	Linux, UNIX, and Windows Ensure that source table is included in a schema-level subscription. z/OS When you drop a table the Q subscription for the table still exists. To remove, stop the Q subscription and then delete the Q subscription.
ALTER TABLE ADD COLUMN	If you set the value of the REPL_ADDCOL column in the IBMQREP_SUBS table to Y (yes), when you add new columns to a table the columns are automatically added to the Q subscription and added to the target table if they do not already exist. z/OS: Replication of ALTER TABLE ADD COLUMN is not supported for large object (LOB) columns.	If you want to automatically add the new column to the target table and the Q subscription, specify REPLICATE ADD COLUMN YES in the ASNCLP CREATE QSUB command or click the Automatically replicate new columns added to the source table check box when you are creating or changing the properties of a Q subscriptions in the Replication Center. For more detail, see Enabling automatic replication of newly added columns from the source table.
TRUNCATE TABLE	z/OS A TRUNCATE operation is logged similarly to a mass delete, so the operation is replicated as a series of single row deletes. Linux, UNIX, Windows The TRUNCATE operation is replicated if the value of the CAPTURE_TRUNCATE column in the IBMQREP_SUBS table is T. The operation is not replicated if the target is a noncomplete CCD table.	z/OS No action is required. If the target table has rows that are not in the source table, those rows are not deleted and are logged as exceptions. Linux, UNIX, Windows If you need to perform a TRUNCATE operation on a target table in addition to its source, use the CAPTURE_TRUNCATE T option in the ASNCLP CREATE QSUB command to ensure that the CAPTURE_TRUNCATE column in the IBMQREP_SUBS table is set to T.
ALTER TABLE ALTER COLUMN SET DATA TYPE (when the Q subscription is active)	Automatically replicated for Version 10.1 and later. The data type of the corresponding target table column is changed and replication continues normally. Restriction: Not supported for LOB columns.	See Replication of SET DATA TYPE operations. Before altering LOB columns in the source table you must stop the Q subscription, perform the ALTER at both the source and target, and then start the Q subscription.
ALTER TABLE DROP COLUMN	Automatically replicated for ARCH_LEVEL 1021 and later	See Replication of DROP COLUMN operations
ALTER TABLE RENAME COLUMN	Automatically replicated with Version 10.2.1 extended content PTF and later if Q Capture and Q Apply programs are both on z/OS.	If the original source and target column names matched but you do not want the Q Apply program to rename the target column, set the REPL_RENAME_COL column in the IBMQREP_TARGETS table to N. Otherwise, Q Apply automatically renames the target column to match the new source column name. See Replication of RENAME COLUMN operations.
ALTER TABLE ALTER COLUMN SET NOT NULL ALTER TABLE ALTER COLUMN DROP NOT NULL	Automatically replicated on Linux, UNIX, and Windows (including native Oracle targets) with Version 10.5 Fix Pack 9 and later. Restriction: Not supported for LOB columns.	REPL_ALTER_COL=Y must be set in the IBMQREP_TARGETS table Before altering LOB columns in the source table you must stop the Q subscription, perform the ALTER at both the source and target, and then start the Q subscription.
RENAME of a source table	Not automatically replicated	You must manually change the control table information about the source table and if necessary its corresponding target table. See Handling RENAME TABLE on tables subscribed for replication for details.
Other DDL that alters the structure of a table	Q Capture leaves the Q subscription unchanged.	Stop the Q subscription. Alter the source and target tables. Alter or recreate the Q subscription (for example, if you changed the name of a source table column, you need to update the table mapping within the Q subscription so that the replication programs recognize the changed column). Start the Q subscription.
DDL that does not alter the table structure Examples: CREATE INDEX ALTER FOREIGN KEY ADD CONSTRAINT	Q Capture leaves the Q subscription active.	Ensure that unique constraints, primary keys, and referential integrity constraints match between the source and target tables. If you change any of these properties at the source, make a corresponding change to the target to avoid unexpected behavior. Also, restart the Q Apply program so that it recognizes the change.