making a column optional

[Murt]

Hi

I am new to DB2 and have a fairly basic question. I have a mandatory column (NOT NULL) in my table.
How do I alter the column to be optional ?

 

[MarcLodge]

Hi Murt,
You need to unload any data so that you can restore it later, drop the table, and then create it again changing the NOT NULL to NULL. You can then reload the table from the stored data. If you are then wanting to have this field not exist, either set it to NULL or move -1 to it's null indicator.
hope this helps,
Marc

 

[Murt]

Thanks for that Marc. I was hoping there was a way to just change the column characteristic as you can in Oracle or Sybase. Oh well !

Cheers,

Murt

 

[MarcLodge]

Murt,
Don't think that this can be done, although I'll welcome somebody coming along and saying otherwise. There's an ALTER TABLE command, but I don't think that this does what you want. It will add a new column or delete an old one, but it doesn't change one in the way you want.
Marc

 

[leveyp]

Not sure about all DB2 variants but on i-series you can use
ALTER TABLE yourlib.yourtable ALTER COLUMN yourcolumn DROP NOT NULL

 

[Murt]

thanks for that. Unfortunately that doesn't work for DB2 version 7 running on linux.
Oh well !