ALTER ADD COLUMN command (multidirectional replication)

Use the ALTER ADD COLUMN command to add a column to a Q subscription for multidirectional replication.

Syntax

Read syntax diagramSkip visual syntax diagramALTER ADD COLUMN USING SIGNAL ( ,colname  )QSUBsubnameUSING REPQMAPqmapnameWITH BEFORE IMAGEPREFIX 'single_character'SOURCE table_owner.table_name

Parameters

colname
Specifies one or more columns (separated by a comma) to add to the definition of the active Q subscription.
QSUB subname
Specifies the name of the Q subscription.
WITH BEFORE IMAGE
Specifies that the before-image value of each added column will be replicated.
PREFIX 'single_character'
Specifies a single-character prefix for each before-image column. If you do not specify a prefix, the default of X is used. If this prefix generates invalid names, other letters will be used beginning with Y until valid names are generated.
USING REPQMAP qmapname
Specifies the name of the replication queue map that is used by the Q subscription.
SOURCE table_owner.table_name
Specifies that the columns are added to all of the Q subscriptions and publications for the source table.

Usage notes

  • The column needs to exist in the source table already and should not be part of any existing Q subscription.
  • The Q subscription must be active.
  • The column must be nullable or have a default value on the source table.
  • The column name on the target table will be named the same as the column name on the source table.
  • For LONG VARCHAR or GRAPHIC types, the DATA CHANGES INCLUDE VARCHAR COLUMNS option must be enabled. VARCHAR COLUMNS are variable length character columns. The DATA CHANGES INCLUDE VARCHAR COLUMNS is an option set on the source table by altering the table attributes with SQL.
  • A maximum of 20 columns can be inserted into the statement.
  • The option to specify a different name for the target table column is not supported for multidirectional replication.

Example 1

To add the columns PHONE and ADDRESS to the EMPLOYEE0001 Q subscription:
ASNCLP SET SESSION TO Q REPLICATION;
SET SERVER CAPTURE TO DB ALIAS BIDISERVER1;
SET SERVER TARGET TO DB ALIAS BIDISERVER2;
ALTER ADD COLUMN USING SIGNAL (PHONE, ADDRESS) QSUB EMPLOYEE0001
USING REPQMAP BIDISERVER1_ASN_TO_BIDISERVER2_ASN;

Example 2

To add the PHONE, ADDRESS, and EMAIL columns to all Q subscriptions and publications for the EMPLOYEE table.
ASNCLP SET SESSION TO Q REPLICATION;
SET SERVER CAPTURE TO DB ALIAS P2PSERVER1;
SET SERVER TARGET TO DB ALIAS P2PSERVER2;
ALTER ADD COLUMN USING SIGNAL (PHONE, ADDRESS, EMAIL) SOURCE DB2ADMIN.EMPLOYEE;