RE: Making a NOT NULL column to allow NULL

Welcome to the DB2 forum.

Douglas Kostelnik
Senior Database Administrator/Architect

-----Original Message-----
From: Godar Rose
[mailto:db2udbdba-ezmlmshield-x3610736.[Email address protected]
Sent: Wednesday, September 27, 2006 3:35 PM
To: LazyDBA Discussion
Subject: RE: Making a NOT NULL column to allow NULL

if you are using sql server 2000, you can make the change within
enterprise manager from the table design without dropping and recreating
table

-----Original Message-----
From: hsteiner
[mailto:db2udbdba-ezmlmshield-x41806178.[Email address protected]
Sent: Wednesday, September 27, 2006 1:35 PM
To: LazyDBA Discussion
Subject: Re: Making a NOT NULL column to allow NULL

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.

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

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

<html>
 <align=justify> 
_____________________________________________________________ 
The information transmitted is intended only for the person(s) or entity to which it is addressed and may contain confidential and or privileged material and should be treated as a confidential AAA Auto Club South communication. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.
</html>

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