存储过程何时使用MQT [英] When does a stored procedure use an MQT
问题描述
我试图让一个SQL存储过程隐式使用用户维护的MQT
，这引起了关于何时使用或不使用它们的问题。
理论上你会期望存储过程在创建静态SQL时绑定的时间为
。这引发了关于如何停止它或使用MQT启动它的问题，因为绑定上没有
选项。反弹时会发生什么？如果
计划失效并且db2自动重新计划该计划，会发生什么。什么
是reopt的影响？我假设它是动态sql，它是连接的
状态。合并命令似乎没有创建
静态SQL因此它是否处于连接状态？
反馈主机将是非常感谢。
我应该补充一点，我们已经将一些使用MQT的SQL隐式地使用了
到存储过程中但是运行时的存储过程并没有t $ / $
似乎正在使用MQT。它建议存储过程不允许使用MQT
。但这是否适用于存储的
过程中的动态SQL？
I am trying to get a SQL stored procedure to use user maintained MQT
implicitly which raises questions on when they are used or not used.
In theory you would expect the stored procedure to pick up the MQT at
the time it is bound on the creation of the static SQL. This raises
the question on how you stop it or start it using a MQT as there is no
option on the bind. What happens when it is rebound? What happens if
the plan is made invalid and db2 automatically rebinds the plan. What
is the impact of reopt? I assume as it is dynamic sql it is the
state of the connection. The merge command doesn''t appear to create
static SQL therefore does it come under the state of the connect?
Feed back on the mater would be much appreciated.
I should add that we have put some SQL that uses an MQT implicitly
into the stored procedure yet the stored procedure when run doesn''t
appear to be using the MQT. It suggests stored procedure don''t allow
the use of MQT. But does this hold for dynamic SQL within a stored
procedure?
推荐答案
好问题。我在后台询问。
现在，我们确实有一个漫长的周末。所以请问我，如果我在下周末之前没有发布
答案。
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
Good questions. I''m inquiring with backstage.
Now, we do have a long weekend coming up. So ping me if I haven''t posted
an answer by end of next week.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
peter写道：
peter wrote:
我试图让一个SQL存储过程使用用户维护的MQT
隐式提出问题当它们被使用或不被使用时。
从理论上讲，你可以期望存储过程在创建静态时绑定MQT的时间为
SQL。这引发了关于如何停止它或使用MQT启动它的问题，因为绑定上没有
选项。
反弹时会发生什么？如果
计划失效并且db2自动重新计划该计划，会发生什么。什么
是reopt的影响？我假设它是动态sql，它是连接的
状态。
I am trying to get a SQL stored procedure to use user maintained MQT
implicitly which raises questions on when they are used or not used.
In theory you would expect the stored procedure to pick up the MQT at
the time it is bound on the creation of the static SQL. This raises
the question on how you stop it or start it using a MQT as there is no
option on the bind.
What happens when it is rebound? What happens if
the plan is made invalid and db2 automatically rebinds the plan. What
is the impact of reopt? I assume as it is dynamic sql it is the
state of the connection.
我怀疑存储过程中的静态SQL永远不会符合优惠者选择MQT的
。在编译时，优化器
依赖于CURRENT REFRESH AGE特殊寄存器（或DFT_REFRESH_AGE
数据库cfg参数）来确定是否可以使用MQT，因此它
将计划绑定到MQT没有任何意义，当用户可能不希望在运行时使用它时
。
动态SQL但是，在存储过程中，应该（理论上）能够利用MQT来获得
，前提是用户设置的当前REFRESH
AGE是合适的。 br />
使用REOPT ALWAYS *可能*允许静态SQL利用MQT;但
这将取决于用户调用
存储过程的CURRENT REFRESH AGE。仅供参考，您可以使用DB2_SQLROUTINE_PREPOPTS注册表
变量或SYSPROC.SET_ROUTINE_OPTS（）存储过程来控制SQL
存储过程的绑定选项。
显然，Serge可能会从后台获得更好（明确）的回答，但希望我不会太离谱。
I suspect that static SQL in a stored procedure would never be eligible
for the optimizer to select an MQT. At compile time, the optimizer
depends on the CURRENT REFRESH AGE special register (or DFT_REFRESH_AGE
database cfg parameter) to determine whether MQTs can be used, so it
makes no sense to bind a plan to an MQT when a user might not want to
use it at run time.
Dynamic SQL in stored procedures, though, should (in theory) be able to
leverage an MQT, provided that the user''s setting for CURRENT REFRESH
AGE is appropriate.
Using REOPT ALWAYS *might* allow static SQL to leverage an MQT; but
again this will depend on the CURRENT REFRESH AGE for the user calling
the stored procedure. FYI, you can control the bind options for SQL
stored procedures using either the DB2_SQLROUTINE_PREPOPTS registry
variable or the SYSPROC.SET_ROUTINE_OPTS() stored procedure.
Obviously Serge will probably get a better (definitive) answer from
backstage, but hopefully I''m not too far off.
合并命令似乎没有创建
静态SQL因此它是否处于连接状态？
The merge command doesn''t appear to create
static SQL therefore does it come under the state of the connect?
不确定这意味着什么（即MERGE与它有什么关系）？
Not sure what this means (i.e. what does MERGE have to do with it)?
On Jun 28,6：57 * am，Ian< ianb ... @ mobileaudio.comwrote：
On Jun 28, 6:57*am, Ian <ianb...@mobileaudio.comwrote:
peter写道：
peter wrote:
我试图让一个SQL存储过程隐含地使用用户维护的MQT
，这引起了关于何时使用或不使用它们的问题。
理论上你可以期望存储过程在创建静态SQL时绑定在
上的MQT。 *这引发了关于如何停止它或使用MQT启动它的问题，因为绑定上没有
选项。 *
I am trying to get a SQL stored procedure to use user maintained MQT
implicitly which raises questions on when they are used or not used.
In theory you would expect the stored procedure to pick up the MQT at
the time it is bound on the creation of the static SQL. *This raises
the question on how you stop it or start it using a MQT as there is no
option on the bind. *
*>
*反弹时会发生什么？ *如果
*计划失效并且db2自动重新计划该计划，会发生什么。 *什么
*是reopt的影响？ *我假设它是动态sql，它是连接的
*状态。
我怀疑存储过程中的静态SQL永远不会符合条件
，供优化器选择MQT。 *在编译时，优化器
取决于CURRENT REFRESH AGE特殊寄存器（或DFT_REFRESH_AGE
数据库cfg参数）来确定是否可以使用MQT，所以它是
动态但是，存储过程中的SQL应该（理论上）能够利用MQT来获得
，前提是用户对CURRENT REFRESH
AGE的设置是合适的。
使用REOPT ALWAYS *可能*允许静态SQL利用MQT;但
这将取决于用户调用
存储过程的CURRENT REFRESH AGE。 *仅供参考，您可以使用DB2_SQLROUTINE_PREPOPTS注册表
变量或SYSPROC.SET_ROUTINE_OPTS（）存储过程来控制SQL
存储过程的绑定选项。
很明显，Serge可能会从后台获得更好的（明确的）答案，但希望我不会太离谱。
*合并命令似乎没有创建
*>
*What happens when it is rebound? *What happens if
*the plan is made invalid and db2 automatically rebinds the plan. *What
*is the impact of reopt? * I assume as it is dynamic sql it is the
*state of the connection.
I suspect that static SQL in a stored procedure would never be eligible
for the optimizer to select an MQT. *At compile time, the optimizer
depends on the CURRENT REFRESH AGE special register (or DFT_REFRESH_AGE
database cfg parameter) to determine whether MQTs can be used, so it
makes no sense to bind a plan to an MQT when a user might not want to
use it at run time.
Dynamic SQL in stored procedures, though, should (in theory) be able to
leverage an MQT, provided that the user''s setting for CURRENT REFRESH
AGE is appropriate.
Using REOPT ALWAYS *might* allow static SQL to leverage an MQT; but
again this will depend on the CURRENT REFRESH AGE for the user calling
the stored procedure. *FYI, you can control the bind options for SQL
stored procedures using either the DB2_SQLROUTINE_PREPOPTS registry
variable or the SYSPROC.SET_ROUTINE_OPTS() stored procedure.
Obviously Serge will probably get a better (definitive) answer from
backstage, but hopefully I''m not too far off.
*The merge command doesn''t appear to create
静态SQL因此它是否处于连接状态？
static SQL therefore does it come under the state of the connect?
不确定这意味着什么（即MERGE与它有什么关系）？
Not sure what this means (i.e. what does MERGE have to do with it)?
感谢Ian的反馈意见。我最初有同样的想法
然而我意识到这样的限制会排除在DB2中提供的许多新功能，例如：如果是联合源，则缓存
，它是通过MQT实现的。关于使用MQT的用户，
反之亦然。您可能希望静态SQL使用存储的
过程，因为它会显着提升性能。
注意我们的环境同时使用了MQT和联合访问
系统和用户都维护，所以我们知道如何控制MQT的使用。我们现在遇到了需要存储
程序来使用MQT的情况，而且这个
方面的文档很少。
关于用户设置连接状态，我建议
anwer不清楚，因为存储过程在服务器上运行，并且
与DB2建立自己的连接。更多的是使用java或C
存储过程。我知道存储过程可以在代码中设置状态
，然后提出问题是
返回的状态。答案应该是否定的。
已知有关控制SQL存储过程的绑定选项但
这些仅适用于创建时，并且没有控制
MQTs。这就是我的问题所在。
关于合并的问题是基于它不会产生静态的事实
SQL（可以'''在包中看到它）因此它将完全运行与其他SQL（当然是静态的）不同的
。它实际上有点
更宽，因为我们注意到插入/更新/删除似乎没有使用MQT，这对我们希望非常有用使用缓存的
昵称来提高性能。
我希望这可以澄清一点问题并且可能有助于理解为什么
我相信使用MQT存储过程是非常有益的，并且需要适当的机制来控制它们的使用。由于我们有存储过程来填充我们的
MQT，因此我们希望确保这些不会尝试使用MQT
他们正在填充。
Thanks Ian for your feedback. I had the same thoughts initially
however I realised such a restriction would the rule out many of the
new features being delivered in DB2, e.g. caching if federated sources
which is implemented via an MQT. In regarding to a user using an MQT,
the reverse is also true. You may want static SQL to use a stored
procedure as it is a significant performance boost.
Note our environment has both MQTs and federated access in use for
both system and user maintained so we are aware of how to control the
use of MQTs. We have now hit situations where we want stored
procedures to use MQTs and there is little documentation on this
aspect.
With regard to the user setting the connection state, I would suggest
the anwer is not clear as the stored procedure runs at the server and
establishes its own connection with DB2. More so with java or C
stored procedures. I know that the stored procedure can set the state
within code, which then raises the question is the state maintained on
return. The answer should be no.
Known about controlling bind options for SQL stored procedures but
these only apply at creation and there is no option for controlling
MQTs. That is the point of my orginally question.
My question on merge is based on the fact it doesn''t produce static
SQL (can''t see it in the package) therefore it would run completely
different to the other SQL (static of course). It actually goes a bit
wider as we have noticed that insert/update/delete doesn''t seem to
make use of MQTs which would be very useful as we wish to use cached
nicknames to improve performance.
I hope this clarifies matter a little more and may help understand why
I believe it is very benefical to have stored procedures using MQTs
and the need for appropriate mechanisms to control their use. It gets
a little more complex as we have stored procedures to populate our
MQTs and these we want to ensure these do not attempt to use the MQT
they are populating.
这篇关于存储过程何时使用MQT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
