To make that change you will need to drop and re-create the table with the new definitions. The ALTER table ADD column function will only work if you either allow NULLs or ALLOW a default (NOT NULL WITH DEFAULT), but once you've chosen one or the other - you must stick with it, or re-create a new table with the new definition.

What I usually do is CREATE the new table first with a slightly different name, then INSERT with a subselect from the old table to populate the new table, then DROP the old table and rename the new to have the same name as the old.

Good luck.

Hal

-----Original Message-----
From: db2udbdba-ezmlmshield-x20878161.[Email address protected]
To: [Email address protected]
Sent: Wed, 27 Sep 2006 1:12 PM
Subject: Making a NOT NULL column to allow NULL


Hi,

I was adding columns to existing table.
As per the general convention in our installation, the column is added
with NOT NULL DEFAULT.

Now that user comes back and says that these columns are exception to
installation standards, and wants the columns to allow NULL.

How do I achieve this ?

I was trying
alter table test.jnl_ety alter column dpt_cd set data type char 3
(earlier dpt_cd is added as char(3) not null with default )
When I am running the above query, it is giving me error.

Please help me out in this.


Thanks & Regards,
Ravi.



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
________________________________________________________________________
Check out the new AOL. Most comprehensive set of free safety and security tools, free access to millions of high-quality videos from across the web, free AOL Mail and more.

DB2 & UDB email list listserv db2-l LazyDBA home page