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

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


[...] 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

  • Can't Unload. Data size in this table is too huge. Though that was my first Plan of action. If i create a coumn TEMP_VARCHAR, update it with VARCHAR_CLOB. Then drop the VARCHAR_CLOB colum and rename the column name. Is that a feasible option ? – rrrohanb Jan 6 '16 at 12:42
  • Yes that would also be possible, but when you say too huge what do you mean, why is that a problem? If there is a massive amount of rows your update might cause problems with the transaction logs. You might have to increase the logspace and/or do the update in chunks. – Lennart Jan 6 '16 at 12:51
  • The file size would be approxiately +10 GB. Wouldn't that be a problem? I can do the update during a downtime and change the log size temporarily, revert it back once done. – rrrohanb Jan 6 '16 at 12:55
  • I believe you can change the LOB columns to be non-logging if you do not care about loss of data. That should help as LOBs otherwise have huge impact on logging. – Chris Aldrich Jan 7 '16 at 21:20
  • You might want to read about turning logging off temporarily. www-01.ibm.com/support/docview.wss?uid=swg21215818. Just make sure you backup and set a SAVEPOINT – GeekyDaddy Jan 12 '16 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.