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.

Before you begin

The Q subscription that the columns are being added to must be in A (active) state.
Linux, UNIX, 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.

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).
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.
Federated targets: To add columns to an existing Q subscription, you can use the ADDCOL signal but you must drop the Q subscription and recreate it after you alter the target table because you cannot add columns to a nickname.
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.

When you insert the signal at the Q Capture server, the column is automatically added to the target table if you did not already add it. If you want to add multiple columns to a Q subscription, you insert one signal for each new column. You can add multiple columns in a single transaction. The Q Capture program can be stopped when you insert the signals and it will read them from the log when it restarts.

If you let the replication programs automatically add new columns to the target table it helps ensure that they match the columns at the source. Columns are added to the target table with the same data type, null characteristic, and default value as the matching columns in the source table. You can specify a different name for the target column if you use the ALTER ADD COLUMN command in the ASNCLP command-line program or an ADDCOL signal.

Procedure

To add columns to a unidirectional Q subscription, use one of the following methods:

Method Description

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;before_column_name; target_column_name', 'P' );` `schema` Identifies the Q Capture program that is processing the Q subscription that you are adding a column to. `subname;column_name;before_column_name;target_column_name` The name of the Q subscription that you want to add the column to 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. Follow these examples: Add column in source table to Q subscription and to target table `QSUB1;COL10` Add column and before image of the column (for CCD target tables) `QSUB1;COL10;XCOL10` Add column without before image but with different target column name `QSUB1;COL10;;TRGCOL10` (Use the double semicolon (;;) to indicate that you are omitting the before-image column.)

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;before_column_name;
        target_column_name',
     'P' );

schema

Identifies the Q Capture program that is processing the Q subscription that you are adding a column to.

subname;column_name;before_column_name;target_column_name

The name of the Q subscription that you want to add the column to 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. Follow these examples:

Add column in source table to Q subscription and to target table: QSUB1;COL10
Add column and before image of the column (for CCD target tables): QSUB1;COL10;XCOL10
Add column without before image but with different target column name: QSUB1;COL10;;TRGCOL10 (Use the double semicolon (;;) to indicate that you are omitting the before-image column.)

After processing the signal, the Q Capture program begins capturing changes to the new column when it reads log data that includes the column. Changes to the column that are committed after the commit of the ADDCOL signal insert will be replicated to the new column in the target table. Rows that existed in the target table before the new column is added will have a NULL or default value for the new column.