On Jun 28, 6:57*am, Ian <ianb...@mobileaudio.comwrote:
peter wrote:
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.
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
static SQL therefore does it come under the state of the connect?
Not sure what this means (i.e. what does MERGE have to do with it)?
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.