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.
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