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.