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?

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 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)?

On Jun 28, 6:57*am, Ian <ianb...@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.

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

On Jun 28, 11:18*pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> 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

Hi, don´t want to get side tracked from my main issue being MQT but I
did not say it was not regular SQL, I offered the observation it doesn
´t appear to generate static SQL in package for the stored procedure.
Plus it does appear to select an MQT. The later observation applies
for update and insert statements. Haven´t tested delete. I queried
a merge command´s ability to use parallelism at a recent IDUG and the
IBM guru stated it was not incorporated into DB2 in the same way as
main stream SQL so don´t expect it to behave in a similar way. Or
words to that effect. There are other restrictions on it that don´t
exist for insert/delete/update such as the ¨with¨ syntax from memory.

peter wrote:
> On Jun 28, 11:18 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
>> 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
> Hi, don´t want to get side tracked from my main issue being MQT but I
> did not say it was not regular SQL, I offered the observation it doesn
> ´t appear to generate static SQL in package for the stored procedure.
> Plus it does appear to select an MQT. The later observation applies
> for update and insert statements. Haven´t tested delete. I queried
> a merge command´s ability to use parallelism at a recent IDUG and the
> IBM guru stated it was not incorporated into DB2 in the same way as
> main stream SQL so don´t expect it to behave in a similar way. Or
> words to that effect. There are other restrictions on it that don´t
> exist for insert/delete/update such as the ¨with¨ syntax from memory.
If the MERGE statement did not show up at static, could it be it was
referring to a DGTT?
Any statement that refers to SESSION.* is compiled on first use.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On Jun 30, 9:08*am, Serge Rielau <srie...@ca.ibm.com> wrote:
> peter wrote:
> > On Jun 28, 11:18 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> >> 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
> > Hi, don´t want to get side tracked from my main issue being MQT but I
> > did not say it was not regular SQL, *I offered the observation it doesn
> > ´t appear to generate static SQL in package for the stored procedure.
> > Plus it does appear to select an MQT. *The later observation applies
> > for update and insert statements. *Haven´t tested delete. * I queried
> > a merge command´s ability to use parallelism at a recent IDUG and the
> > IBM guru stated it was not incorporated into DB2 in the same way as
> > main stream SQL so don´t expect it to behave in a similar way. *Or
> > words to that effect. *There are other restrictions on it that don´t
> > exist for insert/delete/update such as the ¨with¨ syntax from memory..
>
> If the MERGE statement did not show up at static, could it be it was
> referring to a DGTT?
> Any statement that refers to SESSION.* is compiled on first use.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

No, the merge statements make no reference to a DGTT or a
SESSIONS.*. I have check a whole series of stored procedure with
m,erge commands. None show the merge command yet show the update and
insert statements. In some merge statements all the tables referenced
appear in other SQL and those statements appear in the package.

>None show the merge command yet show the update and
>insert statements.
Please clarify "show".
Do you mean the optimizer plan?
If so then yes, that makes sense. The DB2 compiler breaks MERGE down
into its individual components.
Typically you see an outer join between source and target, followed by a
join with a UNION ALL with UPDATE/DELETE/INSERT operations stacked on
top as requested.
This behavior is not special for MERGE however. It is normal for the
compiler to break statements down (or add to them).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On Jun 30, 9:37*pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> *>None show the merge command yet show the update and
> *>insert statements.
> Please clarify "show".
> Do you mean the optimizer plan?
> If so then yes, that makes sense. The DB2 compiler breaks MERGE down
> into its individual components.
> Typically you see an outer join between source and target, followed by a
> join with a UNION ALL with UPDATE/DELETE/INSERT operations stacked on
> top as requested.
> This behavior is not special for MERGE however. It is normal for the
> compiler to break statements down (or add to them).
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Not talking about the optimizer plan, talking about the statements
that form the DBRM which is stored in the system tables which can be
shown by the "show explainable statements" option on the package list
menu. They do not exist in the DBRM even in broken down form from
what I have seen.