× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. July 2009

Re: refresh mqt question

Thanks for your comments, Birgitta!
From my understanding (pls point out if I am wrong),

1) The purpose of building MQT is that, it is a summary table that are built
based on one ore more than one source table.
a) when running a statement that access the source tables, e.g., select
..... from srctbl1, srctbl2 ... where..., SQE will check whether it can use
the MQTs that are built from srctbl1 and/or srctbl2 to find the best access
plan.
b) If not considering a), MQT is a summary table, e.g. select ... from
MQT0 where...

2) The reason why I don't use VIEW is: it does not help to speed up the
quering running at all. But MQTs do help with it.

3) I still have confusion with dynamic SQL and static SQL. I thought I was
using static sql. I wonder if you could show me how to write static SQL code
for the problem I stated in my previous post?


thanks,
yan

On Thu, Jul 30, 2009 at 1:19 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>wrote:

Would it possible that I set an option to get exclusive access to the MQT?
You may try to allocate the MQT before refreshing it, with the CL command
ALCOBJ and deallocate it after.

But be aware, if the MQT is accessed via SQL, the ODPs stay open (until
job's end).

In either way I'd not use a MQT but a view. An MQT is a physical file that
duplicates original data and cannot be actualized automatically (even
though
it is panned).
Refreshing an MQT is much more time consuming than accessing a view,
because
a CLRPFM must be executed, the query must be executed and the data must be
physically written to the MQT. A view is a unkeyed logical file can be used
wherever you use a physical file, for example in Query/400 or with ODBC or
for download into excel.

... why are you using dynamic SQL when you can use static SQL?
Dynamic SQL may be more flexible than static SQL, but has a performance
overhead at runtime, that means the String must be checked and converted
into an executable SQL statement and the access plans cannot be stored in
the program object. (Excuting dynamic SQL with the CQE means building
access
plans by scratch, with SQE an access plan that can be used may be stored
within the plan cache)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Yan Zhang
Gesendet: Wednesday, 29. July 2009 17:44
An: Midrange Systems Technical Discussion
Betreff: Re: refresh mqt question

Hi Birgitta,

Would it possible that I set an option to get exclusive access to the MQT?

Right now, I have to replace the code

SET SQLSTMT = 'refresh table mylib/MQT0';
EXECUTE IMMEDIATE SQLSTMT ;

with following codes:

SET SQLSTMT = 'delete from MQT0';
EXECUTE IMMEDIATE SQLSTMT ;

SET SQLSTMT = 'insert into MQT0
(DIST,COTID,ONOFFID,CHNID,"DATE",QTY,CES,BES,NETPRIC,ROWS_PER_GROUP)
SELECT F1.DIST , COTID , ONOFFID , CHNID , DATE , SUM ( QTY ) AS QTY ,
SUM ( CES ) AS CES, SUM ( BES ) AS BES , SUM ( NETPRIC ) AS NETPRIC , COUNT
( * ) AS ROWS_PER_GROUP FROM
FCTSALES F1 , DIMACCTS A1 WHERE A1.DIST_ACCT = F1.DIST_ACCT
GROUP BY F1.DIST , COTID , ONOFFID , CHNID , DATE';
EXECUTE IMMEDIATE SQLSTMT ;


Seems the 2nd method works while other application is reading the MQT. But
I
still prefer to use the simple "refresh table" method.


thanks,
yan


On Wed, Jul 29, 2009 at 11:17 AM, Birgitta Hauser
<Hauser@xxxxxxxxxxxxxxx>wrote:

For refreshing MQTs you need exclusive access to the MQT, because a
CLRPFM
must be performed.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Yan Zhang
Gesendet: Wednesday, 29. July 2009 16:29
An: Midrange Systems Technical Discussion
Betreff: refresh mqt question

This question is about to refresh a materialized query table.

When executing statement
" refresh table mylib/mqt0; ", if some application is reading mqt0 at
the
same time, will the refresh statement run successfully?


My colleague found he could not refresh the mqt table successfully in
above
situation.



thanks,
yan
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.