473,703 Members | 3,670 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

When does a stored procedure use an 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. 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?
Jun 27 '08 #1
11 3430
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
Jun 27 '08 #2
Ian
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)?
Jun 27 '08 #3
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.
Jun 28 '08 #4
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
Jun 28 '08 #5
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.
Jun 29 '08 #6
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
Jun 29 '08 #7
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.
Jun 30 '08 #8
>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
Jun 30 '08 #9
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.
Jul 1 '08 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1657
by: 8leggeddj | last post by:
Hello, I am having a problem when using access xp as a frontend for sql server 2000. I have been trying to update a number of stored procedures (Just simple adding fields etc) which results in access crashing with event ID 1000 and 1001. Does anyone have any ideas as to what could be the problem? Thanks in advance..
4
4965
by: Robin Tucker | last post by:
Hi, I'm trying to determine with my program whether or not a given database supports a given feature set. To do this I'm querying for certain stored procedures in the sysobjects table and if they are present, making the assumption the database will support the given feature. The problem is I can't find a certain stored procedure in the sysobjects table, even though I know it exists and can see other similar procedures using: select...
3
4239
by: Koen | last post by:
Hi, first of all, the machine setup server 1: - UDB2 7.2.5; COUNTRY=1, location = US, IBM1250 codepage - Locale: US English; Regional Settings: English; Keyboard: Dutch; decimal separator: . server 2: - UDB2 7.2.5 Client Tools - Websphere 4.0.5
3
2284
by: dbuchanan | last post by:
Hello, (Windows forms - SQL Server) I fill my datagrid with a stored procedure that includes relationships to lookup tables so that users can see the values of the combobox selections rather than the key value that are stored in the table. It works well if the comboboxes are selected when the row is created.
1
1826
by: Ted | last post by:
In MS SQL I used the following to create a stored procedure. USE AdventureWorks; GO IF OBJECT_ID ( 'HumanResources.usp_My_Search', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.usp_My_Search; GO CREATE PROCEDURE HumanResources.usp_My_Search @searchstring varchar(40) AS
3
6042
by: Goog79 | last post by:
Hi everyone, first time here, so I'm sorry if this has been covered already ages ago. :( I am trying to learn T-SQL and Stored Procedures and bought the book on these topics by Djan Sunderic, Publisher McGraw Hill/Osborne. I'm already stuck on my first Stored Procedure and getting error messages that I cannot understand. I've already tried Google and Microsoft online to no avail. I do have the .NET Framework on my system and use
3
2928
by: =?Utf-8?B?YWJheWVy?= | last post by:
In short: I have an application using the CommittableTransaction class that works fine when run locally, but does not escalate to MSDTC when deployed to a Windows 2003 R2 server. More Specifically: I have a web service that saves data to a database. This service uses a CommittableTransaction to decide when to Commit or Abort the underlying
0
1947
by: mersis | last post by:
I have a very very annoying problem. I want to create a stored procedure, that creates a table and does various things with it. Before creating a stored procedure I check if the table is there. If it is, I first drop it, and then recreate it. However when I try to create such stored procedure DB2 complains if the table already exists. The stupid thing does not realise that I do the check in the code. Is there anyway to ignore such errors from...
0
1727
by: jk9427 | last post by:
Hello all, I have a weird situation here. We are on an ISeries AS400. We have created an SQL table and can access it just fine when the COBOL program is called directly. The problem exists when the process is invoked with a stored procedure. The stored procedure calls a CL and then the COBOL program, but the SQL statement fails during this process. Am I missing something, why does it work when the COBOL progam is invoked without using...
0
8674
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9262
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9125
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9018
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6595
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4690
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3127
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2463
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2070
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.