• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

question about deleting a table column

 
rick collette
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34214
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
rick collette
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34214
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 208
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic