Creating MQT's on v5r3

[kellyj00]

I am trying to create a incrementally updatable Materialized Query
Table on our as400 v5r3 per examples found on the internet. Shown
below.

create tABLE SQLLIB.INVMAST_MQT AS (SELECT * FROM SQLLIB.INVMAST) DATA
INITIALLY
DEFERRED REFRESH DEFERRED;

CREATE TABLE INVMAST_MQTS FOR SQLLIB.INVMAST_MQT
PROPOGATE IMMEDIATE;

SET INTEGRITY FOR INVMAST_MQT QUERY IMMEDIATE UNCHECKED;
SET INTEGRITY FOR INVMAST_MQT STAGING IMMEDIATE CHECKED;

I receive errors like this one below based on syntax for the Create
Table statement.
SQL0104] Token <END-OF-STATEMENTwas not valid. Valid tokens: ENABLE
DISABLE MAINTAINED.

I can create the MQT's using the iseries navigator interface, but they
are not incrementally updateable.

This is probably the most basic of errors. I have had no luck in
finding any examples from IBM on how to create an incrementally
updatable MQT, but have been able to create a basic MQT that fully
refreshes.

-Jason

[Karl Hanson]

ke******@gmail.com wrote:

I am trying to create a incrementally updatable Materialized Query
Table on our as400 v5r3 per examples found on the internet. Shown
below.

create tABLE SQLLIB.INVMAST_MQT AS (SELECT * FROM SQLLIB.INVMAST) DATA
INITIALLY
DEFERRED REFRESH DEFERRED;

CREATE TABLE INVMAST_MQTS FOR SQLLIB.INVMAST_MQT
PROPOGATE IMMEDIATE;

SET INTEGRITY FOR INVMAST_MQT QUERY IMMEDIATE UNCHECKED;
SET INTEGRITY FOR INVMAST_MQT STAGING IMMEDIATE CHECKED;

I receive errors like this one below based on syntax for the Create
Table statement.
SQL0104] Token <END-OF-STATEMENTwas not valid. Valid tokens: ENABLE
DISABLE MAINTAINED.

I can create the MQT's using the iseries navigator interface, but they
are not incrementally updateable.

This is probably the most basic of errors. I have had no luck in
finding any examples from IBM on how to create an incrementally
updatable MQT, but have been able to create a basic MQT that fully
refreshes.

Incrementally updatable MQTs are not supported by iSeries. I believe
the error is because the MAINTAINED BY USER clause is missing. See
Notes under refreshable-table-options here:

http://publib.boulder.ibm.com/infoce...zmsthctabl.htm

Also the SET INTEGRITY statement is not supported. The examples you
reference may be for DB2 LUW. V5R3 iSeries SQL info is available here
under Database->Reference :

http://publib.boulder.ibm.com/infoce...v5r3/index.jsp

--
Karl Hanson

[kellyj00]

Thank you for the reply.

If the Iseries does not support incrementally updateable views, what
else could I use? I have a query that takes around 30 minutes to
execute and I would like to update it daily.

I've thought about using a stored procedure to handle this, where a NOT
IN clause on the existing table or a MERGE statement... the issue is
the time it takes to run this query.

A table refresh takes over an hour for some reason, so a
non-incremental MQT is pretty much worthless to us.

Thank you!
Jason

[Karl Hanson]

ke******@gmail.com wrote:

Thank you for the reply.

If the Iseries does not support incrementally updateable views, what
else could I use? I have a query that takes around 30 minutes to
execute and I would like to update it daily.

I've thought about using a stored procedure to handle this, where a NOT
IN clause on the existing table or a MERGE statement... the issue is
the time it takes to run this query.

A table refresh takes over an hour for some reason, so a
non-incremental MQT is pretty much worthless to us.

You might look to see if there are ways to reduce refresh time, such as
using index advisor.
http://publib.boulder.ibm.com/infoce...q/queryopt.htm

Also user maintained MQTs can be updated other ways vs a complete
refresh using REFRESH TABLE, such as with stored procedures with queries
or triggers (depending on volumes) that insert/update rows.

--
Karl Hanson