Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DB2 How can I delte a column?

Posted on 2009-07-01
3
Medium Priority
?
454 Views
Last Modified: 2012-05-07
Hi,

how can I delete a column in a table of a database?

I tried with the following sql-statement but it did not work:

alter table tablename delete column columnname;
alter table tablename drop column columnname;

Many thanks in advance!

Have a nice day!
isaf
0
Comment
Question by:inversojvo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 24753903
Hi inversijvo,

It depends a lot on what flavor of DB2 (Z/OS, AS/400, LUW) that you're running and the version.

Before version 9, DB2 didn't support it.  You could "recover" some of the space used by the column by setting it to NULL (if the column allowed NULL) for all rows, but you couldn't delete it.  The only other real choices were to recreate the table without the column, or just live with it.

If your version of DB2 supports the functionality, you should be able to do this:

  ALTER TABLE mytable DROP COLUMN somecolumn;
  REORG TABLE mytable;

The REORG is optional, but if the column is large, is indexed, or is part of a foreign key relationship you should strongly consider it.  :)


Good Luck,
Kent
0
 

Author Closing Comment

by:inversojvo
ID: 31598707
Many thanks also for the info that DB2 did not suppert it before version 9. Unfortunately I have DB2 version 7.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24755269
Hi inversijvo,

Version 7 is pretty old and well out of the support window.

You should consider updating DB2 to a newer version.


Kent
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

597 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