hi all
if by mistake we add wrong column in table and we have backup of last week is there any way to drop column from table in db2

Hi,
you can't drop column by command "alter table drop column...".
But you can export table to ixf format without 'droped' column and then import it back.
1. export to c:\file.ixf of ixf select col1, col2... from schema.table
2. drop table schema.table
3. import from c:\file.ixf of ixf create into schema.table

But you must be careful - dropping a table results in lost of foreign keys, triggers etc if exist on table.

Hope this helps,
Grofaty

v9 allows drop column

rahul_s80,
can you please write a sintax.
Thanks,
Grofaty

Example 15: Assume that you have a table named SALARY_DATA that is defined with the following columns:

Column Name Data Type
----------- ---------
EMP_NAME VARCHAR(50) NOT NULL
EMP_ID SMALLINT NOT NULL
EMP_POSITION VARCHAR(100) NOT NULL
SALARY DECIMAL(5,2)
PROMOTION_DATE DATE NOT NULL
Change this table to allow salaries to be stored in a DECIMAL(6,2) column, make PROMOTION_DATE an optional field that can be set to the null value, and remove the EMP_POSITION column.

ALTER TABLE SALARY_DATA
ALTER COLUMN SALARY SET DATA TYPE DECIMAL(6,2)
ALTER COLUMN PROMOTION_DATE DROP NOT NULL
DROP COLUMN EMP_POSITION

http://publib.boulder.ibm.com/infoce...w/v9/index.jsp

alter table command

Syntax is obvious in V9:

so according to you

suppsose if we have a table ISMPCS
and col1,col2,col3,col4
and we want to drop col4
then first we export the table ISMPCS in ixf format with col1,col2,col3
then drop previous table after that import new table

so what is the syntax to import table ISMPCS with col1,col2,col3

Hi,
ankur02018, first of all check what is your DB2 database version. Execute command 'db2level' in DB2 Command prompt.

If it is version 9 then you can use "alter table ISMPCS drop dolumn col4" - I have checked this out now with DB2 v9 on Linux/Intel and it works fine.

If you DB2 version is older then v9 then you should follow my tips.

You have to specify the columns that you need with export command. For example: "export to c:\file.ixf of ixf select col1, col2, col3 from ISMPCS". Did you noticed there is no col4 in select statement. File.ixf contains only col1, col2 and col3, but no more col4. When you import file.ixf you already don't have col4 created.

Hope this helps,
Grofaty

If you are not on V9, you should use the ALTER_OBJ stored procedure provided by DB2. That will help you with a lot of the issues associated with dropping a column.

stolze,
I have been using ALTER_OBJ stored procedure, but this procedure can make quite a mess if you don't 100% know what are you doing.

BTW, procedure exports data, change definitions and import data back.

Also DB2 v8 fp11 has a bug where ALTER_OBJ stored procedure does not work. I know this for sure, because this is the database level in over production database.

According to the type of the question ankur02018 has asked, I thing ALTER_OBJ will be to complicated to use.

If someone decides to study ALTER_OBJ stored procedure I recommend the following links:
http://groups.google.co.uk/group/com...3dea192?&hl=en
http://publib.boulder.ibm.com/infoce...n/r0011934.htm

Hope this helps,
Grofaty

easiest of all
use db2cc GUI , it will allow you to drop column in a table by calling alter_obj
n doing all exports,imports, rename at the back of it
just click and it will do all for you
DBA's paradise :-)

Not quite "DBA's paradise" - V9 is much closer than the ALTER_OBJ work-around.

Sorry guys..But can some expert please help me on my thread of SQL1272N ..this has halted our system and needs to start it ASAP...
Sorry once again...please help.

here is simplest way


first export in .csv file

then drop column and remake required script

run the script

and in last import .csv file into the table