SEO

#1 (**permalink**) 02-27-2008, 11:30 AM

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-STATEMENT> was 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

#2 (**permalink**) 02-27-2008, 11:30 AM

kellyj00@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-STATEMENT> was 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

#3 (**permalink**) 02-27-2008, 11:30 AM

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

#4 (**permalink**) 02-27-2008, 11:30 AM

kellyj00@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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Creating MQT's on v5r3