question about deleting a table column

[rick collette]

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]

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:

ALTER table mytable drop column mycolumn

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]

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:

ALTER table mytable drop column mycolumn

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]

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]

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