DB2 – How to Alter Table from CLOB to VARCHAR

db2

I have a Table which some nut head had created. The data in it is not more than varchar(100) but he has created the column as CLOB.

Need to change it to Varchar (100). The below query does not seem to work.

ALTER TABLE test alter Column VARCHAR_CLOB set data type VARCHAR(100);

it gives the following error

ALTER TABLE "BASE.test" specified attributes for column "VARCHAR_CLOB" that are not compatible with the existing column.. SQLCODE=-190, SQLSTATE=42837, DRIVER=4.18.60

Best Answer

You cannot change a LOB type to a non LOB type and vice versa (I assume LUW here):

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html

[...] SET DATA TYPE altered-data-type Start of changeSpecifies the new data type of the column. The new data type must be castable from the existing data type of the column (SQLSTATE 42837). A LOB column cannot be altered to a different data type (SQLSTATE 42837). A non-LOB column cannot be altered to a LOB data type (SQLSTATE 42837).End of change.

One way is to unload the data with export:

db2 "export to t.ixf of ixf select ..., cast(varchar_clob as varchar(100)), ... from t"

then drop all foreign key pointing to this table, drop the table, create the new table, load the data back to the table and finally add the foreign keys that where dropped.

An alternative approach is to drop the foreign keys, rename the table, create the new table and use load from cursor. When done drop the renamed table

Related Question