Hi,
Can you pl tell me the syntax to rename a column. I have tried using - ALTER TABLE COMPANY CHANGE "ELITE ID" ELITE_ID VARCHAR (255);-
this but it didn't work.
Also you can tell how to drop a column froma table. i used - ALTER TABLE COMPANY drop "ELITE ID" ;- this .
Thanks

try:

ALTER TABLE tablename RENAME COLUMN oldname TO newname

You care to tell us which version of DB2 you are using and on which platform? Then we can provide you a link to the manuals where the syntax is described and explained.

If you are using DB2 7.1 or DB2 8.1 thenb there is no query to rename column
only you have to drop table

but if you are using DB2 9.1 then you can rename column

I am using DB 8. Is there any way to drop that column.

Hi,
in v8 you can:
1. export table ... select only columns you need
2. drop table
3. import table
4. create any foreign keys, triggers, etc if exist

You can also use ALTOBJ stored procedure to do this automatically. But it is not so easy to use it...

Hope this helps,
Grofaty

You can also use ALTOBJ stored procedure to do this automatically. But it is not so easy to use it...

Hope this helps,
Grofaty[/QUOTE]


can you please tell step by step process to alter column through ALTOBJ stored procedure

Open the DB2 Control Center and use the procedure from there. The GUI makes it much easier to provide the correct parameters for the procedure invocation.

this is the syntax (see info center for details)
CALL SYSPROC.ALTOBJ( 'APPLY_STOP_ON_ERROR',
'CREATE TABLE T1 (CL1 VARCHAR(5), CL2 BIGINT)', -1, ?)
replace the create table stmnt with the correct stmnt

Hi,
the command is pretty simple, but if something goes wrong it can be pain in the a... to recover to previous state. I had a problem of DB2 dropping some object (foreign keys if I remember correctly) and I need to restore database to test computer to see what was the original sintax.

Bottom line is ALTOBJ can solve simple problems, but complex problems can lead to more manual work as it would without this procedure.

So I always prefer export/import functionality...
Hope this helps,
Grofaty

Read Serge's description of ALTOBJ in this link

http://www.thescripts.com/forum/thread514528.html

Cheers
Sathyaram