472,358 Members | 1,938 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,358 software developers and data experts.

How can I drop a Column from a table?

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
5 50977
"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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Richard Galli | last post by:
I want viewers to compare state laws on a single subject. Imagine a three-column table with a drop-down box on the top. A viewer selects a state from the list, and that state's text fills the...
1
by: Marion Jakob | last post by:
hi i'm using mssql server 2000 i want to remove a not null column constraint from the column answertext in table answertext. i tried the following line ALTER TABLE AnswerText ALTER COLUMN...
2
by: James Knowlton | last post by:
Hello, I'm just returning to MS SQL Server after two years of dealing with Sybase ASE. I need to drop a column, using the alter table command. I keep getting an error indicating that a...
2
by: kmnotes04 | last post by:
Is it possible to link one drop-down box to another? For example, if a name is chosen from a drop-down list, can another drop-down list then automatically display the person's office as a result of...
1
by: Igor Kryltsov | last post by:
Hi, I have table: # \d category; category_id | integer | not null default nextval('public.category_category_id_seq'::text) category_name | character varying(100) | not...
1
by: Thomas Chille | last post by:
Hello! I have a Problem. A table with a ForeignKeyRef exits. The Name of the field with this FK-Constraint is 'id_employee' because it is referencing the field 'id_employee' in...
5
by: Romulo NF | last post by:
Greetings, Im back here to show the new version of the drag & drop table columns (original script ). Ive found some issues with the old script, specially when trying to use 2 tables with...
1
by: Gladiator | last post by:
Hi Team , Can some one tell me why we dont we have a DROP column or rename a column command in DB2 ? I Have a very Huge table and need to rename a column . The only option i have to do to rename...
4
by: Joseph | last post by:
Can i drop a cloumn from a table in DB2 for Z/OS?
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
1
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. header("Location:".$urlback); Is this the right layout the...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it so the python app could use a http request to get...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.