Adding a new column to replicated table in DB2 Q Replication

This tutorial will help you understand on how to add a new column to table which is already replicated, This tutorial applies for Q Replication with bidirectional setup ( active - active solution ) that i was using in my earlier posts. Description of table is below to which we shall add one new column, name it as ADDRESS VARCHAR(10)

db2inst1@murali:~$ db2 describe table dba

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            SYSIBM    VARCHAR                     10     0 Yes
ID                              SYSIBM    INTEGER                      4     0 No  

  2 record(s) selected.


Basic steps are like this

1) Check the subscription of the table on source and target and see if it active before we add a new column
2) Add the column with normal alter table add column command to the source table
3) Pass the signal to replication so it can understand that we have added a new column to table
4) Check on the target side if the column is added or not

1)

db2inst1@murali:~$ db2 connect to testa

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTA

db2inst1@murali:~$ db2 "select subname,state from asnta.ibmqrep_subs where source_name='DBA'"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0001                                                                                                                              A  

  1 record(s) selected.

db2inst1@murali:~$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

db2inst1@murali:~$ db2 "select subname,state from asntb.ibmqrep_subs where source_name='DBA'"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0002                                                                                                                              A  

  1 record(s) selected.


2)

db2inst1@murali:~$ db2 "alter table dba add column address varchar(10)"
DB20000I  The SQL command completed successfully.

3)

db2inst1@murali:~$ db2 "insert into ASNTA.IBMQREP_SIGNAL(SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN,SIGNAL_STATE) values ( CURRENT TIMESTAMP,'CMD','ADDCOL','DBA0001;ADDRESS','P' )"
DB20000I  The SQL command completed successfully.

4)

db2inst1@murali:~$ db2 connect to testb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

db2inst1@murali:~$ db2 describe table dba

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            SYSIBM    VARCHAR                     10     0 Yes
ID                              SYSIBM    INTEGER                      4     0 No  
ADDRESS                         SYSIBM    VARCHAR                     10     0 Yes

  3 record(s) selected.

All the commands used in this post are highlighted in ITALIC