Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Converting NULL column into NOT NULL using ALTER statement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Sat Aug 06, 2016 3:45 pm    Post subject: Converting NULL column into NOT NULL using ALTER statement
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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Sun Aug 07, 2016 4:15 am    Post subject:
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: 186
Location: chennai

PostPosted: Sun Aug 07, 2016 1:43 pm    Post subject: Reply to: Converting NULL column into NOT NULL using ALTER statement
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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Mon Aug 08, 2016 8:27 pm    Post subject:
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: 1529
Location: Andromeda Galaxy

PostPosted: Mon Aug 08, 2016 9:20 pm    Post subject:
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: 186
Location: chennai

PostPosted: Tue Aug 09, 2016 3:16 pm    Post subject: Reply to: Converting NULL column into NOT NULL using ALTER statement
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us