Sunday, June 8, 2014

Changing the data type of a column in replicated table in DB2 Q Replication

db2inst1@sairam:~$ db2 describe table task

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
TASK_ID                         SYSIBM    INTEGER                      4     0 No    
TASK_INFO                       SYSIBM    VARCHAR                     10     0 Yes   
SALARY                          SYSIBM    VARCHAR                     10     0 Yes   

  3 record(s) selected.

We want to change SALARY column datatype from varchar(10) to char(20) 

Steps to change the data type:

1) Stop the subscription by passing capstop signal
2) stop the data capture changes to none on both databases
3) alter the column with the new data type on both sides
4) start the data capture changes to yes on both databaes
5) Start the subscription by passing capstart signal

1) db2inst1@sairam:~$ db2 connect to testb

   Database Connection Information

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

db2inst1@sairam:~$ db2 "insert into ASNTB.IBMQREP_SIGNAL (SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN) values (CURRENT TIMESTAMP,'CMD','CAPSTOP','TASK0002')"
DB20000I  The SQL command completed successfully.

2) db2inst1@sairam:~$ db2 alter table task data capture none      - ( on both databases )
DB20000I  The SQL command completed successfully.

3) db2inst1@sairam:~$ db2 "alter table task alter column task_info set data type char(20)"     - ( on both databases )
DB20000I  The SQL command completed successfully.

4) db2inst1@sairam:~$ db2 alter table task data capture changes
DB20000I  The SQL command completed successfully.

5) db2inst1@sairam:~$ db2 "insert into ASNTA.IBMQREP_SIGNAL (SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN) values (CURRENT TIMESTAMP,'CMD','CAPSTART','TASK0001')"   - ( on one side )
DB20000I  The SQL command completed successfully.

No comments:

Post a Comment