473,391 Members | 1,489 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,391 software developers and data experts.

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 4042
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Pascal PEYRE | last post by:
hi, I have an 820 iseries on V5R1. I use DB2 connect V7.2 on a linux server. This week I must migrate the 820 iseries toward an 620 i5 on V5R3. I would to know if my DB2 connect V7.2 on linux...
1
by: Zri Man | last post by:
I have come across a bizzare behaviour with DB2/UDB 8.2 on SuSE Linux 2.41 When I have a MQT Refresh going on (complete refresh) it appears to lock the underlying base tables used to build the...
4
by: rajesh.balu | last post by:
I created a MQT defined as: select a.col1,a.col2,a.col3,b.col1.b.col2 from a , b where a.id = b.id Now, I execute this query which get rewritten to the MQT: select a.col1,b.col1, c.val from...
1
by: Sa | last post by:
DB2 V 8.1.6 and DB2 V8.2.4 I'm using a Fact Table and 7 Dimension tables in a MQT definition. create mqt1(col-list) as ( select dim1_desc, dim2_desc, .... dim6_date,
4
by: lpmsk | last post by:
Hi, we are using DB2 v8.1 on Linux64 for a DW application. We have a table with around 800 million records in it. For performance purpose we created about 2 or 3 MQT's(summary tables) on the table...
5
by: Kevin | last post by:
Using a base table, a MQT table was created. With optimization - when querying the base table with calcuation that are already completed in the MQT - I would assume the optimizer would use the MQT...
8
by: Michael.Guppenberger | last post by:
Hello everyone, I am currently trying to create a materialized query table which should be in-sync all the time. So my first attempt was to use the "REFRESH IMMEDIATE" option of the create table...
1
by: Toralf =?utf-8?q?F=C3=B6rster?= | last post by:
We tried to use MQT in our environment, but with the biggest MQT we always run into this problem: DB21034E The command was processed as an SQL statement because it was not a valid Command Line...
11
by: peter | last post by:
I am trying to get a SQL stored procedure to use user maintained MQT implicitly which raises questions on when they are used or not used. In theory you would expect the stored procedure to pick up...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.