469,628 Members | 1,044 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,628 developers. It's quick & easy.

Creating MQT's on v5r3

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

Jan 19 '07 #1
3 3895
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
Jan 19 '07 #2
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

Jan 19 '07 #3
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
Jan 19 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Pascal PEYRE | last post: by
1 post views Thread by Zri Man | last post: by
4 posts views Thread by rajesh.balu | last post: by
1 post views Thread by Sa | last post: by
4 posts views Thread by lpmsk | last post: by
5 posts views Thread by Kevin | last post: by
8 posts views Thread by Michael.Guppenberger | last post: by
1 post views Thread by Toralf =?utf-8?q?F=C3=B6rster?= | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.