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);

Rowland

Answer 1

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;

Rowland

For others stumbling on this answer: this is due to SQL0270N Function not supported (Reason code = "100") "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. – Clausen

Answer 2

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

Caloric

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

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 – Feudalism

Answer 3

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');

Accra