CODEWITHSUNDEEP
db2
kongxx
kongxx

Reputation: 113

DB2 cannot create MQT table

I am trying to create DB2 MQT table in my DB2 v10.5, but I got the following error message:

An unexpected token "CREATE TABLE T_MQT AS ( SELECT ID, COL1, C" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.16.53 SQL Code: -104, SQL State: 42601

Here is my create table statement:

CREATE TABLE T (
   ID                VARCHAR(128)  NOT NULL,
   COL1              VARCHAR(128),
   COL2              VARCHAR(128),
   COL3              VARCHAR(128),
   COL4              VARCHAR(128),
   COL5              VARCHAR(128),
   PRIMARY KEY (ID)
);
CREATE TABLE T_MQT AS (
    SELECT ID, COL1, COL2, COL3 FROM T
)
DATA INITIALLY DEFERRED 
REFRESH IMMEDIATE 
SET INTEGRITY FOR T_MQT IMMEDIATE CHECKED NOT INCREMENTAL;

Upvotes: 0

Views: 730

Answers (2)

kongxx
kongxx

Reputation: 113

I found the reason is the base table is "ORGANIZE BY COLUMN". So I changed the sql to 

    CREATE TABLE T (
       ID                INTEGER  NOT NULL,
       COL1              VARCHAR(128),
       COL2              VARCHAR(128),
       COL3              VARCHAR(128),
       COL4              VARCHAR(128),
       COL5              VARCHAR(128),
       PRIMARY KEY (ID)
    ) ORGANIZE BY ROW;
    CREATE TABLE T_MQT ( ID, COL1, COL2, COL3 )
    AS ( select ID, COL1, COL2, COL3  from T )
    DATA INITIALLY DEFERRED REFRESH IMMEDIATE MAINTAINED BY SYSTEM;
    SET INTEGRITY FOR T_MQT IMMEDIATE CHECKED FULL ACCESS;

Upvotes: 0

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11052

You're missing a semicolon between the CREATE TABLE T_MQT ... statement and the SET INTEGRITY statement.

Upvotes: 0

Related Questions