AW: Create View

I don't that a MQT will be a good solution:

The automatic update for a MQT is not yet realized.
That means it must be manually refreshed.
Because the refresh performs a CLRPFM and a lot of writes into the MQT
(physical table). This will more expensive than using a view or a UTF.

From: The creation and use of materialized query tables 
      within IBM DB2 UDB for iSeries by Michael Caine
"DB2 UDB for iSeries does not automatically keep materialized query tables
synchronized with the base tables. When the base tables are changing due to
insert, update or delete activity, there can be a difference between the
contents of the MQT(s) and the contents of the base tables. This difference
represents the data latency.

It is the responsibility of the user or programmer to refresh or maintain
the MQT data. If user queries are accessing MQTs and the MQT(s) are not
maintained as the base table(s) change, the query results will increasingly
diverge away from the results obtained from querying the base tables
directly."

For more information about MQT read:
The creation and use of materialized query tables within IBM DB2 UDB for
iSeries by Michael Caine
http://www-1.ibm.com/servers/enable/site/education/abstracts/438a_abs.html

Birgitta

Mit freundlichen Grüßen
 
i.A. Birgitta Hauser
 
LUNZER + PARTNER GMBH
Unternehmensberatung
Carl-Zeiss-Straße 1
63755 Alzenau
 
Tel:         + 49 6023 951-255
Fax:        + 49 6023 951-111
Internet.  www.lp-gmbh.com
              www.rpg-schulung.de
 

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Wilt, Charles
Gesendet: Dienstag, 2. August 2005 15:06
An: Midrange Systems Technical Discussion
Betreff: RE: Create View

Note that if you're at v5r3, you can materialize the view so that the run
time considerations don't apply.  The view is then kept upto date just like
a MAINT(*IMMED) logical.


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Vernon Hamberg
> Sent: Tuesday, August 02, 2005 8:47 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: Create View
> 
> 
> A further consideration - you mentioned the performance issue 
> in reply to 
> the post about UDTFs - but views are also run at the time you 
> use them - 
> the SQL SELECT that is used to define the view is run dynamically, so 
> performance is an issue there, as well. Whereas the LF access path is 
> maintained and can even include an index.
> 
> Maybe?
> 
> Vern
>