Re: refresh mqt question -- MIDRANGE-L

Dynamic SQL is when you build an SQL statement in a variable and then run it.

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

**NOTE: you should never name your variables SQLxxx. Names beginning
with SQL are reserved for IBM's use.

The point is that wSQLSTMT could be anything, it's dynamic. The
statement isn't known till runtime. Perhaps a more clear example.
if myvar = *ON;
SET wSQLSTMT = 'refresh table mylib/MQT0';
elseif someVar = *ON;
SET wSQLSTMT = 'drop table mylib/MQT0';
else;
SET wSQLSTMT = BuildSomeOtherStatement();
endif;
EXECUTE IMMEDIATE wSQLSTMT ;

In static SQL, the statement is known at compile time:
if myvar = *ON;
exec SQL refresh table mylib/MQT0;
elseif someVar = *ON;
exec SQL drop table mylib/MQT0;
else;
//nothing to do here
endif;

If you are using PREPARE + EXECUTE or PREPARE + OPEN (cursor) or
EXECUTE IMMEDIATE, then you are using Dynamic SQL.

HTH,
Charles

On Fri, Jul 31, 2009 at 11:29 AM, Yan Zhang<yzhtech@xxxxxxxxx> wrote:

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.

--
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.