By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,682 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,682 IT Pros & Developers. It's quick & easy.

How can I drop a Column from a table?

P: n/a
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

Jan 10 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/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.
Jan 10 '06 #2

P: n/a
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

Jan 10 '06 #3

P: n/a
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

Jan 10 '06 #4

P: n/a
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
Jan 10 '06 #5

P: n/a
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

Jan 10 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.