By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,993 Members | 1,898 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,993 IT Pros & Developers. It's quick & easy.

How to alter a column from NULL to NOT NULL?

P: 4
Hello All,

I am having problems finding the correct syntax for changing a column from NULL to NOT NULL.

We are using DB2 on z/OS.

I have tried:

ALTER TABLE TEST_CB ALTER COLUMN TEST_CHAR NOT NULL;
ALTER TABLE TEST_CB ALTER COLUMN TEST_CHAR SET NOT NULL;

And a couple others, but no luck.

Thanks,

Craigbert
Jan 11 '11 #1
Share this Question
Share on Google+
8 Replies


P: 4
Does the silence mean that there is not a way to do this w/o dropping and recreating the table?
Jan 13 '11 #2

Rabbit
Expert Mod 10K+
P: 12,373
What do you mean by change a column from null to not null? The only thing that would make sense is if you're trying to put a validation on the column in which they can not leave the column blank. In which case, you're trying to add a check constraint. If that is the case, you need to look up the syntax for that. If that is not the case, I have no idea what you're trying to do.
Jan 13 '11 #3

P: 4
Rabbit,

Thanks for the reply.

Your initial guess is correct.

When I look at the IBM DB2 v9 manual online this is the syntax I see:
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE bar ALTER COLUMN C1 SET NOT NULL
But when I use that syntax I get the following error:
Expand|Select|Wrap|Line Numbers
  1. Category    Timestamp    Duration    Message    Line    Position
  2. Error    1/13/2011 10:49:33 AM    0:00:00.171    <link> - DB2 Database Error: ERROR [42601] [IBM][DB2] SQL0104N  An unexpected token "NOT" was found following "".  Expected tokens may include:  "CYCLE, NOCYCLE, ORDER, NOCACHE, NOORDER, NOMINVALUE, NOMAXVALUE".  SQLSTATE=42601
  3.     3    0
  4.  
What am I doing wrong????

Thanks,

Craigbert
Jan 13 '11 #4

Rabbit
Expert Mod 10K+
P: 12,373
Here's what I was able to find in the documentation.
NOT NULL
Prevents the column from containing null values. The default-clause must also be specified (SQLSTATE 42601).

NULL
Specifies NULL as the default for the column. If NOT NULL was specified, DEFAULT NULL must not be specified within the same column definition.
Or, there's the option of
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE tableName
  2. ADD CONSTRAINT constraintName (fieldName IS NOT NULL)
Or you could alter the column data type with a not null after the data type.
Jan 13 '11 #5

P: 2
alter table <table_name> alter column <column_name> drop not null
Apr 27 '11 #6

P: 4
JUKe & Rabbit,

Here is what I have tried w/o any luck:
ALTER TABLE DEPT ALTER COLUMN LD2_IN DROP NOT NULL;
returns the following error:
ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "DROP" was found following "". Expected tokens may include: "RESTART". SQLSTATE=42601

ALTER TABLE DEPT ADD CONSTRAINT CK01A(LD2_IN IS NULL);
returns the following error:

Lookup Error
ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "(" was found following "". Expected tokens may include: "FOREIGN CHECK UNIQUE PRIMARY". SQLSTATE=42601

ALTER TABLE DEPT ALTER COLUMN LD2_IN SET DATA TYPE CHAR(1);
This statement executes, but it does not do anything to the NULL option.

ALTER TABLE DEPT ALTER COLUMN LD2_IN SET DATA TYPE CHAR(1) IS NULL;
produces the following error:

Lookup Error
ERROR [42601] [IBM][DB2] SQL0199N The use of the reserved word "IS" following "" is not valid. Expected tokens may include: "FOR ADD ALTER APPEND VALIDPROC AUDIT DROP DATA VOLATILE NOT". SQLSTATE=42601

ALTER TABLE DEPT ALTER COLUMN LD2_IN SET DATA TYPE CHAR(1) NULL;
produces the following error:

Lookup Error
ERROR [42601] [IBM][DB2] SQL0199N The use of the reserved word "NULL" following "" is not valid. Expected tokens may include: "FOR ADD ALTER APPEND VALIDPROC AUDIT DROP DATA VOLATILE NOT". SQLSTATE=42601

Any other suggestions?
Apr 28 '11 #7

P: 1
Did you ever determine how to do this? I am trying to do the same thing using db2 zos v9.
Aug 24 '11 #8

P: 1
too late to reply- this is what i tried and it worked -
Expand|Select|Wrap|Line Numbers
  1. alter table table_name alter column column_name set not null
Dec 2 '16 #9

Post your reply

Sign in to post your reply or Sign up for a free account.