When does a stored procedure use an MQT

[peter]

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 Rielau]

Re: When does a stored procedure use an MQT

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

[Ian]

Re: When does a stored procedure use an MQT

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

[peter]

Re: When does a stored procedure use an MQT

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

[Serge Rielau]

Re: When does a stored procedure use an MQT

Just to clear out an apparent misunderstandin g:
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

[peter]

Re: When does a stored procedure use an MQT

On Jun 28, 11:18 pm, Serge Rielau <srie...@ca.ibm .comwrote:

Just to clear out an apparent misunderstandin g:
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.

[Serge Rielau]

Re: When does a stored procedure use an MQT

peter wrote:

On Jun 28, 11:18 pm, Serge Rielau <srie...@ca.ibm .comwrote:

>Just to clear out an apparent misunderstandin g:
>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

[peter]

Re: When does a stored procedure use an MQT

On Jun 30, 9:08 am, Serge Rielau <srie...@ca.ibm .comwrote:

peter wrote:

On Jun 28, 11:18 pm, Serge Rielau <srie...@ca.ibm .comwrote:

Just to clear out an apparent misunderstandin g:
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.

[Serge Rielau]

Re: When does a stored procedure use an MQT

>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

[peter]

Re: When does a stored procedure use an MQT

On Jun 30, 9:37 pm, Serge Rielau <srie...@ca.ibm .comwrote:

 >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.

[Serge Rielau]

Re: When does a stored procedure use an MQT

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.

Er... That's a DB2 for zOS term?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[peter]

Re: When does a stored procedure use an MQT

On Jul 2, 1:09 pm, Serge Rielau <srie...@ca.ibm .comwrote:

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.

>
Er... That's a DB2 for zOS term?
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Yep, but its basically the same object (content of bind file for LUW
I think) just stored in a different way in the system catalogues
between the environments. Never bothered to decode it for LUW version
as the contol centre decodes it. I have assumed the control centre
has correctly decoded it which I suppose is a big assumption.