Solved

DB2 How can I delte a column?

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

861 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

Need Help in Real-Time?

Connect with top rated Experts

30 Experts available now in Live!

Get 1:1 Help Now