Make a column nullable in DB2 when Data Capture is enabled

Question

I'm using db2 version 9.7* and it seems impossible to make a NOT NULL column nullable in any straightforward way.

Unfortunately the solution of using a more developer friendly database is not available. Basically, in MySQL speak, I want to do something like this (where MY_COLUMN used to be VARCHAR(200) NOT NULL) :

ALTER TABLE MY_TABLE MODIFY COLUMN MY_COLUMN VARCHAR(200);

score 30 · Accepted Answer · answered Aug 02 '11 at 14:08

30

My final query ended up something like this:

ALTER TABLE MY_TABLE DATA CAPTURE NONE;
ALTER TABLE MY_TABLE ALTER MY_COLUMN DROP NOT NULL;
ALTER TABLE MY_TABLE DATA CAPTURE CHANGES;

answered Aug 02 '11 at 14:08

lukewm

21,433
6
26
28

2

For others stumbling on this answer: this is due to [SQL0270N Function not supported (Reason code = "100")](http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00270n.html?lang=en) "Drop column, and alter nullability is not allowed on any table with data capture on." -- that is why the data capture has to be changed. – David Lantos Jul 15 '14 at 10:00

score 12 · Answer 2 · answered Aug 02 '11 at 13:43

12

the documentation says that the ALTER TABLE has the possibility to DROP NOT NULL

answered Aug 02 '11 at 13:43

djna

54,992
14
74
117

4

Correct. The problem I was facing was that data capture was on, although I didn't realise that and leapt, probably unfairly, to the conclusion that DB2 was being overcomplicated for no good reason. – lukewm Aug 02 '11 at 14:08
1

Very helpful. :-) I had the same problem on a DB2 environment and could solve it after reading your answer with `ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME DROP NOT NULL` – Kaadzia Dec 09 '13 at 10:27

score 5 · Answer 3 · answered Mar 03 '16 at 06:47

ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME DROP NOT NULL;

works in DB2 9.7 and above. How ever you might want to perform a table reorg (in 9.7 db2 puts the table in reorg pending state, the same in 10.5 FP 5 too):

call sysproc.admin_cmd('reorg table TABLE_NAME');

Make a column nullable in DB2 when Data Capture is enabled

3 Answers3