Solved

DB2 How can I delte a column?

Posted on 2009-07-01
3
450 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 45

Accepted Solution

by:
Kent Olsen earned 500 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 45

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 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