Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: MQT based on Table with CLOB

    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?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, you could add a generated VARCHAR column to the base table and reference it in the MQT.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2013
    Posts
    4
    Is there anyway I can do this without changing the original table?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •