How to alter a column from NULL to NOT NULL?

[craigbert]

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

[craigbert]

Does the silence mean that there is not a way to do this w/o dropping and recreating the table?

[Rabbit]

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.

[craigbert]

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:

ALTER TABLE bar ALTER COLUMN C1 SET NOT NULL

But when I use that syntax I get the following error:

Category    Timestamp    Duration    Message    Line    Position
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    0
 

What am I doing wrong????

Thanks,

Craigbert

[Rabbit]

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

ALTER TABLE tableName
ADD CONSTRAINT constraintName (fieldName IS NOT NULL)

Or you could alter the column data type with a not null after the data type.

[JUKe]

alter table <table_name> alter column <column_name> drop not null

[craigbert]

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?

[gwilp]

Did you ever determine how to do this? I am trying to do the same thing using db2 zos v9.

[Arshayub31]

too late to reply- this is what i tried and it worked -

alter table table_name alter column column_name set not null

[XF00906]

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 ***************************