I am trying to create an MQT with DB2 9.7, the MQT is using a full select from a table that has several CLOBS. Since MQT's do not support CLOBs I am trying to cast the CLOB to a varchar, however, no matter what I do I cannot convert the CLOB to a varchar and keep getting an error on the create table (MQT). The code is:
CREATE TABLE COGNOS.PD (
cast(PD.DESCRIPTION as VARCHAR(5000))
) DATA INITIALLY DEFERRED
ENABLE QUERY OPTIMIZATION
MAINTAINED BY SYSTEM
NOT LOGGED INITIALLY
IN TSMQT8K INDEX IN TSMQT8K
The error code is:
The fullselect specified for the materialized query table "COGNOS.PD" is not valid. Reason code = "3".. SQLCODE=-20058, SQLSTATE=428EC, DRIVER=4.13.111
Is there anything I can do in order to include the data from a CLOB in my MQT? Any conversion that can be done?
I do not necessarily want to do the cast, my final objective is to include the data that is in a CLOB in the MQT.