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.

Before you begin

Before you begin

  • The Q subscriptions that specify the table must be in A (active) state at all servers.
  • Linux®, UNIX, and Windows: If the data type of the column is LONG VARCHAR or GRAPHIC, the source database or subsystem must be configured with DATA CAPTURE CHANGES INCLUDE VARCHAR COLUMNS.

About this task

Restrictions

  • For pre-V12 Db2 for z/OS: If you add a column with a non-system default, for example ALTER TABLE OEUSR01.SRC_TABLE ADD COLUMN N01 CHAR(1) NOT NULL WITH DEFAULT 'Y', and then update that column with the system default value, the column does not replicate until you perform a REORG on the source table space. All other values replicate. For more information about the need for REORG, see When replication requires table space REORG after ALTER TABLE (z/OS).
  • You cannot alter the default value of a newly added column until the ADDCOL signal for that column is processed.
  • If you are adding more than 20 columns, you must issue a Db2 COMMIT statement after 20 ALTER ADD COLUMN operations at the source table or after inserting 20 ADDCOL signals. To avoid previous issues with ALTER ADD operations that exceed 20 columns, apply APAR PI88530.
  • The target cannot be a stored procedure.

About this task

For Version 10.1 or later: You do not need to use this procedure when you are adding new columns to the source table if the participating servers are at Version 10.1 or later on both z/OS® and Linux, UNIX, and Windows. 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. To make this setting, 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.

To use this procedure, first you alter a table at one server to add a column. Then you insert an SQL signal at the server. When the signal is processed, the versions of the table at the other servers are automatically altered to add the column, unless you added it manually. The signal also adds the column to the Q subscription definitions at all servers.

You can add any number of columns to the source table at a time. You can do this while the Q Capture and Q Apply programs are running or stopped.

Recommendation: Insert one ADDCOL signal at a time and issue a COMMIT before inserting the next ADDCOL signal or doing any other transactions.

Procedure

To add columns to replicate in bidirectional or peer-to-peer replication:

Procedure

  1. Alter the logical table at one of the servers to add the column.

    If the ALTER TABLE operation that adds the column to the source table fails, all the Q subscriptions in the peer-to-peer group will be deactivated.

  2. Use one of the following methods to signal the Q Capture program that you want to add the column to the Q subscription for the source table.
    Method Description
    ASNCLP command-line program Use the ALTER ADD COLUMN command. For example, the following command adds the column BONUS to the DEPARTMENT0001 Q subscription:
    ALTER ADD COLUMN USING SIGNAL (BONUS)
QSUB DEPARTMENT0001
USING REPQMAP SAMPLE_ASN_TO_TARGET_ASN;
    Q Replication Dashboard On the Subscriptions tab, select a Q subscriptions from the table and click Actions > Add Columns.
    SQL Use a command prompt or one of the Db2® command-line tools to insert an ADDCOL signal into the IBMQREP_SIGNAL table at the Q Capture server. For example:
    insert into schema.IBMQREP_SIGNAL(
     SIGNAL_TIME,
     SIGNAL_TYPE,
     SIGNAL_SUBTYPE,
     SIGNAL_INPUT_IN,
     SIGNAL_STATE
 ) values (
      CURRENT TIMESTAMP,
     'CMD',
     'ADDCOL',
     'subname;column_name',
     'P' );
    schema
    Identifies the Q Capture program at the server where you altered the table.
    subname;column_name
    The name of a Q subscription that originates at the Q Capture server where you altered the table, and the name of the column that you are adding, separated by a semicolon. These names are case-sensitive and do not require double quotation marks to preserve case.

Example

Consider the following example. A peer-to-peer configuration has three servers: ServerA, ServerB, and ServerC, and six Q subscriptions: subA2B, subB2A, subA2C, subC2A, subB2C, and subC2B for the EMPLOYEE table.

You add a column, ADDRESS, to the EMPLOYEE table on ServerA. Then you insert an ADDCOL signal for the Q subscription that handles transactions from ServerA to ServerB, and specify subA2B;ADDRESS for the Q subscription name and column name. Only one ADDCOL signal is required. The replication programs automatically add the ADDRESS column to the EMPLOYEE tables at ServerB and ServerC, and add the column definition to all six Q subscriptions.