Not able to create Materialized query on AS400

Question

I get an error message when trying to create materialized query in as400 I use winSql for the connection. The syntax seems valid. Could you please point out what am I doing wrong?

This is the statement I am trying to execute

CREATE TABLE AAA.TEST_MQ AS
(
     SELECT test.*

    FROM
        AAA.TABLE_NAME test

)  REFRESH DEFERRED

This is the error message:

Error: SQL0104 - Token <END-OF-STATEMENT> was not valid. Valid tokens: IMMEDIATE <IDENTIFIER>. (State:37000, Native Code: FFFFFF98)

I Tried creating an immediate one as well.

Answer 1

Try this:

CREATE TABLE AAA.TEST_MQ AS (
      SELECT test.*
      FROM
      AAA.TABLE_NAME test  )
    DATA INITIALLY DEFERRED 
    REFRESH DEFERRED 
    MAINTAINED BY USER 
    ENABLE QUERY OPTIMIZATION 
;

I use JaySQL Lite and it works.