Topic
  • No replies
javierg
javierg
1 Post

Pinned topic How can I include the data from a CLOB in my MQT?

‏2013-04-04T18:31:31Z |
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 (
ENTITY_CODE,
DESCRIPTION)

AS (

SELECT
PD.ENTITY_CODE,
cast(PD.DESCRIPTION as VARCHAR(5000))

FROM
SMS.PD PD

) DATA INITIALLY DEFERRED
REFRESH 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.