How can I drop a Column from a table?

[Markus]

I tried this:

ALTER TABLE Dokumente
DROP COLUMN docPrioID

but I get this errormessage:
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: COLUMN;ABLE
Dokumente
DROP;CONSTRAINT

docPrioID was a foreign key to another table but I dropped them (the FK
and the table)
--> Get name with SELECT constname FROM syscat.referenc es and DROP it
from Dokumente

Now syscat.referenc es doesn't show constraints for this table.

Does anybody know how I can DROP the column docPrioID?

kind regards

Markus

[Mark A]

Re: How can I drop a Column from a table?

"Markus" <TheRealHawk@Fr eenet.de> wrote in message
news:1136898222 .931364.152790@ z14g2000cwz.goo glegroups.com.. .[color=blue]
>I tried this:
>
> ALTER TABLE Dokumente
> DROP COLUMN docPrioID
>
> but I get this errormessage:
> DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: COLUMN;ABLE
> Dokumente
> DROP;CONSTRAINT
>
> docPrioID was a foreign key to another table but I dropped them (the FK
> and the table)
> --> Get name with SELECT constname FROM syscat.referenc es and DROP it
> from Dokumente
>
> Now syscat.referenc es doesn't show constraints for this table.
>
> Does anybody know how I can DROP the column docPrioID?
>
> kind regards
>
> Markus
>[/color]

There is no native SQL command to drop a column from a table in DB2. Please
consult the SQL Reference Vol 2 for more information on alter table.

You can use the Control Center to drop a column, because it will generate
commands to export the data, drop the table, recreate the table without the
dropped column, and re-load the data.

[Markus]

Re: How can I drop a Column from a table?

Unfortunately I access the DB with a third-party tool and have no
access to the server so I want to do a workaround since somebody can
drop this column.

For this reason I dropped the FK and the tables as mentioned before and
now I want to set a default-value for this column.

Can you explain, what I have to do (datatype of the column is integer).

I tried
ALTER TABLE dokumente ALTER COLUMN docPrioID SET DATA TYPE INTEGER WITH
DEFAULT -1
but it seems, that this statement doesn't work. :-)

kind regards

Markus

[Markus]

Re: How can I drop a Column from a table?

One second after my posting I thougt: What happens if I try ALTER
TABLE dokumente ALTER COLUMN docPrioID SET WITH DEFAULT -1?

--> I tried it and it works. :-)

Markus

[Serge Rielau]

Re: How can I drop a Column from a table?

Markus wrote:[color=blue]
> One second after my posting I thougt: What happens if I try ALTER
> TABLE dokumente ALTER COLUMN docPrioID SET WITH DEFAULT -1?
>
> --> I tried it and it works. :-)[/color]
Praise to the inventor of the syntax diagram! :-)

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Artur]

Re: How can I drop a Column from a table?

FYI:

offline drop column works with Beta DB2 version 9. GA is expected this
year.

ALTER TABLE Dokumente
DROP COLUMN docPrioID;
REORG TABLE Dokumente;

-- Artur Wronski