By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,995 Members | 1,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,995 IT Pros & Developers. It's quick & easy.

MQT cannot be used for queries with parameter markers?

P: n/a
I am runnig v95.

I have a query "select * from A, B where B.b<2000"

I create a MQT as "select * from A,B where B.b<2000" (identitcal to
the query), and the optimizer will use this MQT to answer this query.

However, if I submit the query using paramter marker "select * from
A, B where B.b<?", and later fill in this paramter using value
"2000", the optimizer will never use this MQT to answer the query.

Is there a way to let db2 use this MQT still?

thanks a lot!

p.s. I already tried the follwing, but doesn't work for me.

db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL EXPLSNAP ALL REOPT 3"
db2 bind "@db2ubind.lst" blocking all sqlerror continue messages
bind.msg grant public REOPT ALWAYS
db2 bind "@db2cli.lst" blocking all sqlerror continue messages cli.msg
grant public REOPT ALWAYS

Sep 21 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I don't think the optimizer can use the MQT in this case. Think about
what would happen if you used the value 3000 as the parameter - then
if it used the MQT, it would return the wrong data.

With REOPT ALWAYS, I suppose it could figure out when it could use the
MQT and when it couldn't, but it may be that the easier code path of
just invalidating the MQT with parameters that match the MQT criteria
was easier.

-Chris
Sep 22 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.