RE: [DB2EUG] ALTER TABLE QUESTION

[Hallin, Tomas (HQP)]

You're out of luck, it can't be done with an ALTER statement.  As you observed, the only changes to existing columns that can be done using ALTER TABLE is increasing the size of a VARCHAR column.   What I would do is:   Create a table with the new definition, and another name. Insert data from the old table, casting the datatype appropriately for your smallint to decimal change. Rename the original table to whatever_old, or something, so it's still there in case something hasn't worked as you intended. Rename the newly created and populated table to the desired name.   Then of course, there's the issues of things (packages, triggers, views) that may have depended on this table.  They would have been invalidated and must be recreated.   /T   Tomas Hallin Database Administration Robert Half International Pleasanton, CA -----Original Message----- From: Sunil Inchal [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2003 11:38 AM To: [EMAIL PROTECTED] Subject: [DB2EUG] ALTER TABLE QUESTION Hi group,   How do I alter column type and drop a column in DB2 using ALTER TABLE or any other command? I read in the documents that only columns with CHAR type could be altered using ALTER TABLE <tabname> ALTER COLUMN <column> ....   I want to change a SMALLINT datatype to DECIMAL(16,2). Also I need to drop an existing column. In Informix IDS it is a very easy to perform the above using ALTER command. But I don't know how to do it in DB2. Finally, what I did was backed up data using EXPORT utility, dropped the table and recreated with changed schema and reloaded the data using LOAD utility. Are there any other ways to do it?   Thanks and regards   Sunil