AW: refresh mqt question

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

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.