Prev: Question: restore from backup in relative path...
Next: the option CPU_PARALLELISM of LOAD
From: peter on 27 Jun 2008 02:36
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?
From: Serge Rielau on 27 Jun 2008 07:48
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
From: Ian on 27 Jun 2008 16:57
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)?


From: peter on 28 Jun 2008 02:09
On Jun 28, 6:57 am, Ian <ianb...(a)mobileaudio.com> wrote:
> 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.
From: Serge Rielau on 28 Jun 2008 09:18
Just to clear out an apparent misunderstanding:
MERGE is just a regular SQL statement like INSERT/UPDATE/DELETE
It's only special w.r.t. its semantics. I.e. the set based workflow for
the MATCHED clauses

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
 |  Next  |  Last
Pages: 1 2 3
Prev: Question: restore from backup in relative path...
Next: the option CPU_PARALLELISM of LOAD