Solved

DB2 How can I delte a column?

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Guide to DB2 SQL pl programming 3 216
Leveraging AD Groups in DB2 1 84
db2 != check 14 54
Getting missing rows 16 66
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

827 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