IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

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: 2508
Location: NY,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: 2508
Location: NY,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

Moderator


Joined: 07 Sep 2006
Posts: 1566
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

 

Search our Forum:

Similar Topics
Topic Forum Replies
This topic is locked: you cannot edit posts or make replies. Splice JCL into one record for DD sta... SYNCSORT 2
No new posts Can we Insert duplicates in Primary U... DB2 0
No new posts Converting Hex to PD Special Condition SYNCSORT 3
No new posts COnvert a column with mix of hex ,cha... DB2 5
No new posts VSAM Alter Command - Cylinder Extent ... JCL & VSAM 3

Back to Top