DB2 : Alter table: CLOB to VARCHAR
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