IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Converting NULL column into NOT NULL using ALTER statement


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Sat Aug 06, 2016 3:45 pm
Reply with quote

Hi all,

Is there any provision to convert a NULL column into a NOT NULL column which is having data in it?

I tried many options, but went in vain.

ALTER TABLE table1 ALTER COLUMN t1 SET DATA TYPE CHAR(6) NOT NULL;

ALTER TABLE table1 ALTER COLUMN t1 SET NOT NULL;


All are went in vain.
Any help or a clue is appreciated.

Thanks in advance
Raghu
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sun Aug 07, 2016 4:15 am
Reply with quote

You must specify WITH DEFAULT and try, if you are not aDBA then why bother yourself instead send an email to the them. I that don't work then try either of this.
1. Drop the column, and then re-add it using Alter, which should then let me add it as NOT NULL, and reorg.
2. Unload the table, drop/create the table, load the table.
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Sun Aug 07, 2016 1:43 pm
Reply with quote

Thanks Rohit for your replay.

I tried alter the column WITH DEFAULT, NOT NULL WITH DEFAULT. In both cases it gives error saying "NULL" is invalid.
I am using DB2 V8.1.

This version is not allowing to drop a column. As you said I can drop the table and create another table with "NOT NULL" column.

Anyway thanks for your comments on my query.

Regards
Raghu icon_biggrin.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Aug 08, 2016 8:27 pm
Reply with quote

DB2 V8.1 probably doesn't support this feature (ask your DBA) and you would possibly have to be on DB2 V9 or up versions. Also try this if you want constraint to be NOT NULL.
Code:
ADD CONSTRAINT
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Mon Aug 08, 2016 9:20 pm
Reply with quote

Did you think of reformatting the rows which has NULL
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Tue Aug 09, 2016 3:16 pm
Reply with quote

@Rohit

Thanks for your hind.
I did it.

Quote:
CREATE UNIQUE INDEX ALTNDEX ON ALTER_EMP(EMPNO);
ALTER TABLE ALTER_EMP ADD PRIMARY KEY(EMPNO);


The table was created using LIKE verb. So no contraints were created.

Now the table is not accepting any duplicate rows as well as not accepting NULL values in the indexed column.

Thanks agin for your hint.

Regards
Raghuanthan
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How to load to DB2 with column level ... DB2 6
No new posts RC query -Time column CA Products 3
No new posts DROP & ALTER PARTITION-PBR DB2 0
No new posts Null values are considered in Total c... DFSORT/ICETOOL 6
No new posts first column truncated in search result IBM Tools 13
Search our Forums:

Back to Top