473,883 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to alter a column from NULL to NOT NULL?

4 New Member
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 93680
craigbert
4 New Member
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,516 Recognized Expert Moderator MVP
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
craigbert
4 New Member
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,516 Recognized Expert Moderator MVP
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 New Member
alter table <table_name> alter column <column_name> drop not null
Apr 27 '11 #6
craigbert
4 New Member
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 New Member
Did you ever determine how to do this? I am trying to do the same thing using db2 zos v9.
Aug 24 '11 #8
Arshayub31
1 New Member
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 New Member
Hi,
in DB2 version 12 this doesn't work:

---------+---------+---------+---------+---------+---------+---------+-----
ALTER TABLE DBRF001T.RFT229 1_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 *************** ************
Jul 22 '21 #10

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

Similar topics

1
8275
by: luc h | last post by:
Hello, Easy one for the SQL experts. I have a simple table. For the example let's say it looks like this: CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL) Now I want to alter this table and make the column column_a a float instead of an INT. How do I do that? I cannot DROP and ADD the column I would lose some
2
21364
by: me | last post by:
I would like to add an Identity to an existing column in a table using a stored procedure then add records to the table and then remove the identity after the records have been added or something similar. here is a rough idea of what the stored procedure should do. (I do not know the syntax to accomplish this can anyone help or explain this? Thanks much, CBL
1
3307
by: Danny | last post by:
I'm trying to simply change a column definition from Null to Not Null. It's a multi million row table. I've already checked to make sure there are no nulls for any rows and a default has been created for the column. My log is set to autogrow and as the alter column colname char(6) Not Null runs the log begins to grow. If I use no check BOL say the optimizer won't consider the change. How can I change the nullability of a column that...
1
6324
by: Euler Almeida via SQLMonster.com | last post by:
Hi people, I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K. Example: table : item_nota_fiscal_forn_setor_publico field : qtd_mercadoria integer NOT NULL ALTER TABLE item_nota_fiscal_forn_setor_publico ALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULL
3
22650
by: Prince Kumar | last post by:
Is there any way I can define an Unique constraint or unique index which allows more than one null values for the same column combination in DB2? ie, If my index is defined on (col3, col4) where both columns allow nulls, I want col3 + col4 to be unique, if one or both the columns have values. If both columns have nulls, it should allow more than one such rows. ex,
4
3916
by: Jeff Kish | last post by:
Hi. I have a database I need to supply something (I'm assuming a t-sql script.. maybe something else is better) to update customer tables with. The operations include mostly changing varchar lengths, though a couple of columns were renamed. I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer to generate the scripts.
3
5286
by: sparks | last post by:
several months ago I was trying to set the field size of text fields in a table and Lyle Fairfield was nice enought to post a way using alter column. it was surprising since it was ALTER COLUMN f5 Text(250) WHERE in the world can I find the a listing of the different things that can be done with this?
2
15799
by: siujean | last post by:
As captioned question, i would like to how to alter the column from null to not null? Thanks
2
12060
by: Jeff Kish | last post by:
Hi. I've read up on this, and have something that works, but I was wondering if there is anything I'm overlooking with this. Situation is: I have a bunch of tables.. I need to modify table2 as part of an upgrade of a database schema.
0
9791
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11137
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10742
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9571
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7122
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5797
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5990
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4609
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4215
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.