MQT cannot be used for queries with parameter markers?

[uwcssa@gmail.com]

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="EXPLA IN ALL EXPLSNAP ALL REOPT 3"
db2 bind "@db2ubind. lst" blocking all sqlerror continue messages
bind.msg grant public REOPT ALWAYS
db2 bind "@db2cli.ls t" blocking all sqlerror continue messages cli.msg
grant public REOPT ALWAYS

[ChrisC]

Re: MQT cannot be used for queries with parameter markers?

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