Solved

DB2 How can I delte a column?

Posted on 2009-07-01
3
447 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
  • 2
3 Comments
 
LVL 45

Accepted Solution

by:
Kdo 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:Kdo
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 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