?
Solved

Convert NULL COLUMNS TO NOT NULL in IBM DB2 using ALTER TABLE Command

Posted on 2007-07-27
5
Medium Priority
?
9,183 Views
Last Modified: 2008-09-14
Hi Experts,

I need to make a NULL Column to NOT NULL Column in db2. Iam using IBM DB2 version IBM DB2 v8.1.7.445.
I have writted query

ALTER TABLE ORDER_QUEUE_HISTORY ALTER COLUMN QUEUETIME_START SET NOT NULL

but its not working. Its giving the following error

ERROR [42601] [IBM][DB2/NT] SQL0104N  An unexpected token "NOT" was found following "QUEUETIME_START SET".  Expected tokens may include:  "DEFAULT".  SQLSTATE=42601

Please help,
Rave
0
Comment
3 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 672 total points
ID: 19581530
Hi Rave,

Sorry to say, but you can't do that.  It would be nice, but that feature isn't supported by DB2.

You'll have to rebuild the table with the column defined as you want it.


Kent
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 664 total points
ID: 19581750
if you can't drop and recreate the table, the are ways to work around it
they won't be best interms of performance but you can create a trigger to replace all incoming null values with some actual value as a temporary workaround
0
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 664 total points
ID: 19582426

That's weird. It works beautifully in DB2 for iSeries.

alter table deleteme
alter column name set not null
;
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question