File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes question about deleting a table column Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "question about deleting a table column" Watch "question about deleting a table column" New topic
Author

question about deleting a table column

rick collette
Ranch Hand

Joined: Mar 22, 2002
Posts: 208
Hi, guys:

I have DB2/NT 7.2.3 on my Win2000. I am trying to delete a column from a
table like this:

ALTER table mytable drop column mycolumn decimal(15)

it seems I cannot do it.

Is this because of my version or OS? Os there any way around this?

thanks a lot
[ January 15, 2005: Message edited by: rick collette ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30752
    
156

Rick,
It is definitely not due to an OS version. I don't recall whether db2 7 supports dropping a column.

In any event, I notice is that there is an error in syntax. You only include the type when adding a column. It should be:


Also, check if mycolumn part of any keys or indexes? You typically can't drop columns in this case.

If it still doesn't work, post the error message from db2.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
rick collette
Ranch Hand

Joined: Mar 22, 2002
Posts: 208
Thanks Jeanne:

I got the following error when I ran the following command:

db2 => ALTER TABLE mytable DROP COLUMN mycolumn DECIMAL(10)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "DROP" was found following "R TABLE
mytable". Expected tokens may include: "ADD". SQLSTATE=42601

I can add column but cannot drop a column. I am wondering if DB2 has this
built-in functionality?

thanks







Originally posted by Jeanne Boyarsky:
Rick,
It is definitely not due to an OS version. I don't recall whether db2 7 supports dropping a column.

In any event, I notice is that there is an error in syntax. You only include the type when adding a column. It should be:


Also, check if mycolumn part of any keys or indexes? You typically can't drop columns in this case.

If it still doesn't work, post the error message from db2.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30752
    
156

Rick,
According to google, db2 7 doesn't support dropping columns.

A technique for when you can't drop a column is:
1) Create table with new schema with new table name
2) Copy data to new table
3) Drop old table
4) Create table with new schema with original tablename
5) Copy data to original table
6) Drop copy of table
rick collette
Ranch Hand

Joined: Mar 22, 2002
Posts: 208
Thanks, Jeanne:

Yes, I aslo thought about dropping/creating tables,
the trouble is that this table has several tables referencing
it. Maybe thats the only way to solve this issue.



Originally posted by Jeanne Boyarsky:
Rick,
According to google, db2 7 doesn't support dropping columns.

A technique for when you can't drop a column is:
1) Create table with new schema with new table name
2) Copy data to new table
3) Drop old table
4) Create table with new schema with original tablename
5) Copy data to original table
6) Drop copy of table
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: question about deleting a table column