468,770 Members | 2,396 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,770 developers. It's quick & easy.

How to alter a column from NULL to NOT NULL?

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
9 80028
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
12,515 Expert Mod 8TB
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
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
12,515 Expert Mod 8TB
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
JUKe
2
alter table <table_name> alter column <column_name> drop not null
Apr 27 '11 #6
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
gwilp
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
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
XF00906
1 Bit
Hi,
in DB2 version 12 this doesn't work:

---------+---------+---------+---------+---------+---------+---------+-----
ALTER TABLE DBRF001T.RFT2291_IMAGE_ZUSATZ
ALTER COLUMN OB
SET NOT NULL;
---------+---------+---------+---------+---------+---------+---------+-----
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD NOT. TOKEN WITH
DEFAULT DATA INLINE CACHE MAXVALUE MINVALUE NOCACHE WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 157 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000009D' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-----
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+-----
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 3
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 20
******************************** Bottom of Data ***************************
1 Week Ago #10

Post your reply

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

Similar topics

1 post views Thread by luc h | last post: by
2 posts views Thread by me | last post: by
1 post views Thread by Danny | last post: by
3 posts views Thread by sparks | last post: by
2 posts views Thread by Jeff Kish | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.