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.references and DROP it
from Dokumente

Now syscat.references doesn't show constraints for this table.

Does anybody know how I can DROP the column docPrioID?

kind regards

Markus

[Mark A]

"Markus" <Th*********@Freenet.de> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

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.references and DROP it
from Dokumente

Now syscat.references doesn't show constraints for this table.

Does anybody know how I can DROP the column docPrioID?

kind regards

Markus

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]

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]

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]

Markus wrote:

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. :-)

Praise to the inventor of the syntax diagram! :-)

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

[Artur]

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