MQT not recognized by optimizer

Mark Vickers

MQT not recognized by optimizer
I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mark Vickers

Re: MQT not recognized by optimizer
(in response to Mark Vickers)
Forgot to add that I did a refresh on the MQT before runstats.


-----Original Message-----
From: Mark E Vickers
Sent: Thursday, January 07, 2010 10:18 AM
To: [login to unmask email]
Cc: Mark E Vickers
Subject: MQT not recognized by optimizer

I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.

David Simpson

Re: MQT not recognized by optimizer
(in response to Mark Vickers)
This is buried in the doc somewhere....

MQTs are only for dynamic SQL. Bound package will not use it.

______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]
For more information about Themis, visit www.themisinc.com

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mark E Vickers
Sent: Thursday, January 07, 2010 10:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer

Forgot to add that I did a refresh on the MQT before runstats.


-----Original Message-----
From: Mark E Vickers
Sent: Thursday, January 07, 2010 10:18 AM
To: [login to unmask email]
Cc: Mark E Vickers
Subject: MQT not recognized by optimizer

I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.

Edward Long

Re: MQT not recognized by optimizer
(in response to David Simpson)
Interesting.
So, if a statically bound program prepared and then executed a statement against an MQT, would DB2 should see the MQT ?
First guess is yes but I also had the Pats over Houston!

Edward Long




________________________________
From: David Simpson <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, January 7, 2010 12:28:34 PM
Subject: Re: [DB2-L] MQT not recognized by optimizer

This is buried in the doc somewhere....

MQTs are only for dynamic SQL. Bound package will not use it.

______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]
For more information about Themis, visit www.themisinc.com

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mark E Vickers
Sent: Thursday, January 07, 2010 10:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer

Forgot to add that I did a refresh on the MQT before runstats.


-----Original Message-----
From: Mark E Vickers
Sent: Thursday, January 07, 2010 10:18 AM
To: [login to unmask email]
Cc: Mark E Vickers
Subject: MQT not recognized by optimizer

I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Simpson

Re: MQT not recognized by optimizer
(in response to Edward Long)
Someone else emailed me off list for this clarification... my original
post was a bit terse.



MQTs may be directly referenced by any SQL static or dynamic. Automatic
query rewrite is only for dynamic. The zParms mentioned my Mark control
the defaults for special registers CURRENT REFRESH AGE and CURRENT
MAINTAINED TABLE TYPES FOR OPTIMIZATION, but we don't have any
equivalent bind options to control this behavior for static SQL.






________________________________________________________________________
______
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email]

For more information about Themis, visit www.themisinc.com
< http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: Thursday, January 07, 2010 12:02 PM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



Interesting.
So, if a statically bound program prepared and then executed a statement
against an MQT, would DB2 should see the MQT ?
First guess is yes but I also had the Pats over Houston!



Edward Long





________________________________

From: David Simpson <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, January 7, 2010 12:28:34 PM
Subject: Re: [DB2-L] MQT not recognized by optimizer

This is buried in the doc somewhere....

MQTs are only for dynamic SQL. Bound package will not use it.

________________________________________________________________________
______
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email]
For more information about Themis, visit www.themisinc.com

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mark E
Vickers
Sent: Thursday, January 07, 2010 10:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer

Forgot to add that I did a refresh on the MQT before runstats.


-----Original Message-----
From: Mark E Vickers
Sent: Thursday, January 07, 2010 10:18 AM
To: [login to unmask email]
Cc: Mark E Vickers
Subject: MQT not recognized by optimizer

I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the intended recipient(s) is strictly
prohibited. If you are not the intended recipient, please contact the
sender by reply email and delete all copies of this message.



________________________________

IDUG - The Worldwide DB2 User Community!
< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: MQT not recognized by optimizer
(in response to David Simpson)
Indeed yes



An MQT also behaves like a regular DB2 table if you reference it directly



Phil Grainger

Grainger Database Solutions Ltd

_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: 07 January 2010 18:02
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



Interesting.
So, if a statically bound program prepared and then executed a statement
against an MQT, would DB2 should see the MQT ?
First guess is yes but I also had the Pats over Houston!



Edward Long





_____

From: David Simpson <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, January 7, 2010 12:28:34 PM
Subject: Re: [DB2-L] MQT not recognized by optimizer

This is buried in the doc somewhere....

MQTs are only for dynamic SQL. Bound package will not use it.

____________________________________________________________________________
__
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email]
For more information about Themis, visit www.themisinc.com

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mark E Vickers
Sent: Thursday, January 07, 2010 10:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer

Forgot to add that I did a refresh on the MQT before runstats.


-----Original Message-----
From: Mark E Vickers
Sent: Thursday, January 07, 2010 10:18 AM
To: [login to unmask email]
Cc: Mark E Vickers
Subject: MQT not recognized by optimizer

I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the intended recipient(s) is strictly
prohibited. If you are not the intended recipient, please contact the sender
by reply email and delete all copies of this message.


_____

< http://www.idug.org/db2-north-america-conference/index.html > IDUG - The
Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mark Vickers

Re: MQT not recognized by optimizer
(in response to Phil Grainger)
Well after David's response, I tried to prepare a Text SQL in OCS and it did not pick up the MQT.
I tried rebinding the program with REOPT(ALWAYS) and still no MQT.
It baffles me why they would put this in just for Dynamic - another SAS agreement !?!

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: Thursday, January 07, 2010 12:02 PM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer

Interesting.
So, if a statically bound program prepared and then executed a statement against an MQT, would DB2 should see the MQT ?
First guess is yes but I also had the Pats over Houston!

Edward Long


________________________________
From: David Simpson <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, January 7, 2010 12:28:34 PM
Subject: Re: [DB2-L] MQT not recognized by optimizer

This is buried in the doc somewhere....

MQTs are only for dynamic SQL. Bound package will not use it.

______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]<mailto:[login to unmask email]>
For more information about Themis, visit www.themisinc.com < http://www.themisinc.com >

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email]<mailto:[login to unmask email]>] On Behalf Of Mark E Vickers
Sent: Thursday, January 07, 2010 10:23 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: Re: [DB2-L] MQT not recognized by optimizer

Forgot to add that I did a refresh on the MQT before runstats.


-----Original Message-----
From: Mark E Vickers
Sent: Thursday, January 07, 2010 10:18 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Cc: Mark E Vickers
Subject: MQT not recognized by optimizer

I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.

________________________________

[ http://www.idug.org/images/M_images/idug%20na3.jpg ] < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >
This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Simpson

Re: MQT not recognized by optimizer
(in response to Mark Vickers)
An interesting experiment might be to explain a query to the MQT
directly using OSC and compare the cost estimates to your query that
will not re-write. If optimizer perceives the direct table access to be
less costly it will go directly to the tables.



Make sure in the OSC "Query Context" window that you have CURRENT
REFRESH AGE set to ANY and the other one set to ALL.




________________________________________________________________________
______
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email]

For more information about Themis, visit www.themisinc.com
< http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mark E
Vickers
Sent: Thursday, January 07, 2010 12:53 PM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



Well after David's response, I tried to prepare a Text SQL in OCS and it
did not pick up the MQT.

I tried rebinding the program with REOPT(ALWAYS) and still no MQT.

It baffles me why they would put this in just for Dynamic - another SAS
agreement !?!



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: Thursday, January 07, 2010 12:02 PM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



Interesting.
So, if a statically bound program prepared and then executed a statement
against an MQT, would DB2 should see the MQT ?
First guess is yes but I also had the Pats over Houston!



Edward Long





________________________________

From: David Simpson <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, January 7, 2010 12:28:34 PM
Subject: Re: [DB2-L] MQT not recognized by optimizer

This is buried in the doc somewhere....

MQTs are only for dynamic SQL. Bound package will not use it.

________________________________________________________________________
______
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email]
For more information about Themis, visit www.themisinc.com

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mark E
Vickers
Sent: Thursday, January 07, 2010 10:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer

Forgot to add that I did a refresh on the MQT before runstats.


-----Original Message-----
From: Mark E Vickers
Sent: Thursday, January 07, 2010 10:18 AM
To: [login to unmask email]
Cc: Mark E Vickers
Subject: MQT not recognized by optimizer

I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the intended recipient(s) is strictly
prohibited. If you are not the intended recipient, please contact the
sender by reply email and delete all copies of this message.



________________________________

IDUG - The Worldwide DB2 User
Community! < http://www.idug.org/images/M_images/idug%20na3.jpg >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >

This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the intended recipient(s) is strictly
prohibited. If you are not the intended recipient, please contact the
sender by reply email and delete all copies of this message.



________________________________

IDUG - The Worldwide DB2 User Community!
< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

DB2 DBA Jeffrey Benner

Re: MQT not recognized by optimizer
(in response to David Simpson)
How the optimizer uses your summary table depends heavily on the CURRENT
REFRESH AGE special register. If that register is set to the default value
it will not use your MQT because you created it REFRESH DEFERRED. The
optimizer will only use your summary table if CURRENT REFRESH AGE is set to
ANY (or all 9's). Or you could change your summary table to REFRESH
IMMEDIATE.

I think that both static and dynamic SQL will use a summary table if it
satisfies all that the optimizer is looking for. Greg Nash here explicitly
states that static SQL can benefit:
http://www.devx.com/getHelpOn/10MinuteSolution/16580/1954

The info center has more to say about what the optimizer wants to see before
it will use a summary table.

On Thu, Jan 7, 2010 at 10:18, Mark Vickers <[login to unmask email]>wrote:

> I created my first MQT and cannot get the optimizer to pick it up ?
>
> Original Query:
> SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
> H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
> STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
> STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
> WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
> RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR
>
> My MQT:
> CREATE TABLE WW44G.MQTRQH01 (
> STR_RLP_MBR_ID
> , RQ_TYP_CD
> , DRW_TOT
> , BIL_BCK_AMT
> )
> AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
> , SUM(DRW_QTY * AD_VAL)
> FROM WW44G.WCSSTRQH
> GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
> DATA INITIALLY DEFERRED REFRESH DEFERRED
> MAINTAINED BY SYSTEM
> ENABLE QUERY OPTIMIZATION
> CCSID EBCDIC
> ;
>
> I ran runstats on the MQT table.
> I have REFSHAGE = ANY and MAINTYPE = ALL on the SS
>
> Rebind does not pick up the MQT.
>
> Any ideas where I went wrong please ?
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
> meetings,
> Webcasts, Conferences- what is going on next?
> RUG leaders- get your events on the calendar today!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
> the home of IDUG's DB2-L
>
>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Simpson

Re: MQT not recognized by optimizer
(in response to DB2 DBA Jeffrey Benner)
Hi Jeff,



This may be a difference between DB2 for z/OS and LUW...



The Performance Monitoring and Tuning guide for DB2 on z/OS says:



"DB2 supports automatic query rewrite only for read-only, dynamic
queries. DB2 cannot automatically rewrite statically bound queries."




________________________________________________________________________
______
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email]

For more information about Themis, visit www.themisinc.com
< http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jeffrey
Benner, DB2 DBA
Sent: Thursday, January 07, 2010 1:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



How the optimizer uses your summary table depends heavily on the CURRENT
REFRESH AGE special register. If that register is set to the default
value it will not use your MQT because you created it REFRESH DEFERRED.
The optimizer will only use your summary table if CURRENT REFRESH AGE is
set to ANY (or all 9's). Or you could change your summary table to
REFRESH IMMEDIATE.

I think that both static and dynamic SQL will use a summary table if it
satisfies all that the optimizer is looking for. Greg Nash here
explicitly states that static SQL can benefit:
http://www.devx.com/getHelpOn/10MinuteSolution/16580/1954

The info center has more to say about what the optimizer wants to see
before it will use a summary table.

On Thu, Jan 7, 2010 at 10:18, Mark Vickers <[login to unmask email]>
wrote:

I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L



________________________________

IDUG - The Worldwide DB2 User Community!
< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Joe Geller

Re: MQT not recognized by optimizer
(in response to David Simpson)
I believe that your problem is that your query references the columns STA_DT
and END_DT, but the MQT definition does not. Since the MQT does not have
those columns, there query cannot use it.

Joe


On Thu, 7 Jan 2010 16:18:29 +0000, Mark Vickers <[login to unmask email]>
wrote:

>I created my first MQT and cannot get the optimizer to pick it up ?
>
>Original Query:
>SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
>H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
>STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
>STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
>WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
>RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR
>
>My MQT:
>CREATE TABLE WW44G.MQTRQH01 (
> STR_RLP_MBR_ID
> , RQ_TYP_CD
> , DRW_TOT
> , BIL_BCK_AMT
> )
> AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
> , SUM(DRW_QTY * AD_VAL)
> FROM WW44G.WCSSTRQH
> GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
> DATA INITIALLY DEFERRED REFRESH DEFERRED
> MAINTAINED BY SYSTEM
> ENABLE QUERY OPTIMIZATION
> CCSID EBCDIC
> ;
>
>I ran runstats on the MQT table.
>I have REFSHAGE = ANY and MAINTYPE = ALL on the SS
>
>Rebind does not pick up the MQT.
>
>Any ideas where I went wrong please ?
>
>_____________________________________________________________________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
>_____________________________________________________________________
>
>http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
meetings,
>Webcasts, Conferences- what is going on next?
>RUG leaders- get your events on the calendar today!
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

DB2 DBA Jeffrey Benner

Re: MQT not recognized by optimizer
(in response to Joe Geller)
Thanks David, I had not picked up on the original context that this was a
z/OS question. Pardon my prejudice.


Jeffrey Benner
eBenner.com Consulting
http://www.ebenner.com
+001 312 520 0090
http://www.linkedin.com/in/ebenner



On Thu, Jan 7, 2010 at 14:02, David Simpson <[login to unmask email]> wrote:

> Hi Jeff,
>
>
>
> This may be a difference between DB2 for z/OS and LUW…
>
>
>
> The Performance Monitoring and Tuning guide for DB2 on z/OS says:
>
>
>
> “DB2 supports automatic query rewrite only for read-only, dynamic queries.
> DB2 cannot automatically rewrite statically bound queries.”
>
>
>
> [image: cid:[login to unmask email]*
> ______________________________________________________________________________
> *
> *David Simpson | Senior Technical Advisor | Themis Education*
> 123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
> [login to unmask email]
>
> For more information about Themis, visit www.themisinc.com
>
>
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Jeffrey
> Benner, DB2 DBA
> *Sent:* Thursday, January 07, 2010 1:21 PM
>
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] MQT not recognized by optimizer
>
>
>
> How the optimizer uses your summary table depends heavily on the CURRENT
> REFRESH AGE special register. If that register is set to the default value
> it will not use your MQT because you created it REFRESH DEFERRED. The
> optimizer will only use your summary table if CURRENT REFRESH AGE is set to
> ANY (or all 9's). Or you could change your summary table to REFRESH
> IMMEDIATE.
>
>
> I think that both static and dynamic SQL will use a summary table if it
> satisfies all that the optimizer is looking for. Greg Nash here explicitly
> states that static SQL can benefit:
> http://www.devx.com/getHelpOn/10MinuteSolution/16580/1954
>
> The info center has more to say about what the optimizer wants to see
> before it will use a summary table.
>
> On Thu, Jan 7, 2010 at 10:18, Mark Vickers <[login to unmask email]>
> wrote:
>
> I created my first MQT and cannot get the optimizer to pick it up ?
>
> Original Query:
> SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
> H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
> STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
> STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
> WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
> RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR
>
> My MQT:
> CREATE TABLE WW44G.MQTRQH01 (
> STR_RLP_MBR_ID
> , RQ_TYP_CD
> , DRW_TOT
> , BIL_BCK_AMT
> )
> AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
> , SUM(DRW_QTY * AD_VAL)
> FROM WW44G.WCSSTRQH
> GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
> DATA INITIALLY DEFERRED REFRESH DEFERRED
> MAINTAINED BY SYSTEM
> ENABLE QUERY OPTIMIZATION
> CCSID EBCDIC
> ;
>
> I ran runstats on the MQT table.
> I have REFSHAGE = ANY and MAINTYPE = ALL on the SS
>
> Rebind does not pick up the MQT.
>
> Any ideas where I went wrong please ?
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
> meetings,
> Webcasts, Conferences- what is going on next?
> RUG leaders- get your events on the calendar today!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
> the home of IDUG's DB2-L
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mark Vickers

Re: MQT not recognized by optimizer
(in response to DB2 DBA Jeffrey Benner)
Thanks everyone,
My bad for not saying [z/OS]

I added the STA_DT & END_DT to the MQT and still no luck.

I am going to go with Phil's comment (An MQT also behaves like a regular DB2 table if you reference it directly)
and play with that and if I can see that updates are reflected in the summary, I will use that, Thanks.

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jeffrey Benner, DB2 DBA
Sent: Tuesday, January 12, 2010 6:31 AM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer

Thanks David, I had not picked up on the original context that this was a z/OS question. Pardon my prejudice.


Jeffrey Benner
eBenner.com Consulting
http://www.ebenner.com
+001 312 520 0090
http://www.linkedin.com/in/ebenner


On Thu, Jan 7, 2010 at 14:02, David Simpson <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Hi Jeff,

This may be a difference between DB2 for z/OS and LUW...

The Performance Monitoring and Tuning guide for DB2 on z/OS says:

"DB2 supports automatic query rewrite only for read-only, dynamic queries. DB2 cannot automatically rewrite statically bound queries."

Error! Filename not specified.______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]<mailto:[login to unmask email]>
For more information about Themis, visit www.themisinc.com < http://www.themisinc.com/ >

From: IDUG DB2-L [mailto:[login to unmask email]<mailto:[login to unmask email]>] On Behalf Of Jeffrey Benner, DB2 DBA
Sent: Thursday, January 07, 2010 1:21 PM

To: [login to unmask email]<mailto:[login to unmask email]>
Subject: Re: [DB2-L] MQT not recognized by optimizer

How the optimizer uses your summary table depends heavily on the CURRENT REFRESH AGE special register. If that register is set to the default value it will not use your MQT because you created it REFRESH DEFERRED. The optimizer will only use your summary table if CURRENT REFRESH AGE is set to ANY (or all 9's). Or you could change your summary table to REFRESH IMMEDIATE.


I think that both static and dynamic SQL will use a summary table if it satisfies all that the optimizer is looking for. Greg Nash here explicitly states that static SQL can benefit: http://www.devx.com/getHelpOn/10MinuteSolution/16580/1954

The info center has more to say about what the optimizer wants to see before it will use a summary table.
On Thu, Jan 7, 2010 at 10:18, Mark Vickers <[login to unmask email]<mailto:[login to unmask email]>> wrote:
I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

________________________________

Error! Filename not specified. < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

Error! Filename not specified. < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >
This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: MQT not recognized by optimizer
(in response to Mark Vickers)
If you are going to go with my suggestion, I have to admit being concerned
by your comment "and if I can see that updates are reflected in the summary"



The data in the MQT will only be as current as the last time you issued a
REFRESH TABLE statement



An MQT is NOT automatically kept "in synch" with the table it is defined
upon



Phil Grainger

Grainger Database Solutions Ltd

_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mark E Vickers
Sent: 12 January 2010 14:11
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



Thanks everyone,

My bad for not saying [z/OS]



I added the STA_DT & END_DT to the MQT and still no luck.



I am going to go with Phil's comment (An MQT also behaves like a regular DB2
table if you reference it directly)

and play with that and if I can see that updates are reflected in the
summary, I will use that, Thanks.



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jeffrey Benner,
DB2 DBA
Sent: Tuesday, January 12, 2010 6:31 AM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



Thanks David, I had not picked up on the original context that this was a
z/OS question. Pardon my prejudice.


Jeffrey Benner
eBenner.com Consulting
http://www.ebenner.com
+001 312 520 0090
http://www.linkedin.com/in/ebenner



On Thu, Jan 7, 2010 at 14:02, David Simpson <[login to unmask email]> wrote:

Hi Jeff,



This may be a difference between DB2 for z/OS and LUW.



The Performance Monitoring and Tuning guide for DB2 on z/OS says:



"DB2 supports automatic query rewrite only for read-only, dynamic queries.
DB2 cannot automatically rewrite statically bound queries."



Error! Filename not
specified.__________________________________________________________________
____________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email]

For more information about Themis, visit www.themisinc.com
< http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jeffrey Benner,
DB2 DBA
Sent: Thursday, January 07, 2010 1:21 PM


To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



How the optimizer uses your summary table depends heavily on the CURRENT
REFRESH AGE special register. If that register is set to the default value
it will not use your MQT because you created it REFRESH DEFERRED. The
optimizer will only use your summary table if CURRENT REFRESH AGE is set to
ANY (or all 9's). Or you could change your summary table to REFRESH
IMMEDIATE.



I think that both static and dynamic SQL will use a summary table if it
satisfies all that the optimizer is looking for. Greg Nash here explicitly
states that static SQL can benefit:
http://www.devx.com/getHelpOn/10MinuteSolution/16580/1954

The info center has more to say about what the optimizer wants to see before
it will use a summary table.

On Thu, Jan 7, 2010 at 10:18, Mark Vickers <[login to unmask email]>
wrote:

I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.htmlis your DB2 Events calendar! RUG
meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-Lis
the home of IDUG's DB2-L



_____

Error! Filename not specified.

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >



_____

Error! Filename not specified.

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >



_____

< http://www.idug.org > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >

This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the intended recipient(s) is strictly
prohibited. If you are not the intended recipient, please contact the sender
by reply email and delete all copies of this message.


_____

< http://www.idug.org > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Isaac Yassin

Re: MQT not recognized by optimizer
(in response to Phil Grainger)
Hi,



Or use maintained by user with triggers to keep it fully in synch.



Isaac Yassin

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 12, 2010 5:43 PM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



If you are going to go with my suggestion, I have to admit being concerned by your comment “and if I can see that updates are reflected in the summary”



The data in the MQT will only be as current as the last time you issued a REFRESH TABLE statement



An MQT is NOT automatically kept “in synch” with the table it is defined upon



Phil Grainger

Grainger Database Solutions Ltd

_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mark E Vickers
Sent: 12 January 2010 14:11
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



Thanks everyone,

My bad for not saying [z/OS]



I added the STA_DT & END_DT to the MQT and still no luck.



I am going to go with Phil’s comment (An MQT also behaves like a regular DB2 table if you reference it directly)

and play with that and if I can see that updates are reflected in the summary, I will use that, Thanks.



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jeffrey Benner, DB2 DBA
Sent: Tuesday, January 12, 2010 6:31 AM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



Thanks David, I had not picked up on the original context that this was a z/OS question. Pardon my prejudice.


Jeffrey Benner
eBenner.com Consulting
http://www.ebenner.com
+001 312 520 0090
http://www.linkedin.com/in/ebenner

On Thu, Jan 7, 2010 at 14:02, David Simpson <[login to unmask email]> wrote:

Hi Jeff,



This may be a difference between DB2 for z/OS and LUW…



The Performance Monitoring and Tuning guide for DB2 on z/OS says:



“DB2 supports automatic query rewrite only for read-only, dynamic queries. DB2 cannot automatically rewrite statically bound queries.”



Error! Filename not specified.______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]

For more information about Themis, visit www.themisinc.com < http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jeffrey Benner, DB2 DBA
Sent: Thursday, January 07, 2010 1:21 PM


To: [login to unmask email]
Subject: Re: [DB2-L] MQT not recognized by optimizer



How the optimizer uses your summary table depends heavily on the CURRENT REFRESH AGE special register. If that register is set to the default value it will not use your MQT because you created it REFRESH DEFERRED. The optimizer will only use your summary table if CURRENT REFRESH AGE is set to ANY (or all 9's). Or you could change your summary table to REFRESH IMMEDIATE.



I think that both static and dynamic SQL will use a summary table if it satisfies all that the optimizer is looking for. Greg Nash here explicitly states that static SQL can benefit: http://www.devx.com/getHelpOn/10MinuteSolution/16580/1954

The info center has more to say about what the optimizer wants to see before it will use a summary table.

On Thu, Jan 7, 2010 at 10:18, Mark Vickers <[login to unmask email]> wrote:

I created my first MQT and cannot get the optimizer to pick it up ?

Original Query:
SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , :
H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR
STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND
STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM
WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND
RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR

My MQT:
CREATE TABLE WW44G.MQTRQH01 (
STR_RLP_MBR_ID
, RQ_TYP_CD
, DRW_TOT
, BIL_BCK_AMT
)
AS (SELECT STR_RLP_MBR_ID, RQ_TYP_CD, SUM(DRW_QTY)
, SUM(DRW_QTY * AD_VAL)
FROM WW44G.WCSSTRQH
GROUP BY STR_RLP_MBR_ID, RQ_TYP_CD)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
CCSID EBCDIC
;

I ran runstats on the MQT table.
I have REFSHAGE = ANY and MAINTYPE = ALL on the SS

Rebind does not pick up the MQT.

Any ideas where I went wrong please ?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.htmlis your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-Lis the home of IDUG's DB2-L



_____

Error! Filename not specified.

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



_____

Error! Filename not specified.

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



_____

< http://www.idug.org > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please < http://www.idug.org/register > register here.

This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.



_____

< http://www.idug.org > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please < http://www.idug.org/register > register here.



_____

< http://www.idug.org > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please < http://www.idug.org/register > register here.


_____

I am using the Free version of SPAMfighter < http://www.spamfighter.com/len > .
We are a community of 6 million users fighting spam.
SPAMfighter has removed 4661 of my spam emails to date.
The Professional version does not have this message.


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
Attachments

  • import1 (20.1k)

Jorg Lueke

Re: MQT not recognized by optimizer
(in response to Isaac Yassin)
Have you tried CURRENT REFRESH AGE = 'ANY' when you check the
query?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

DB2 DBA Jeffrey Benner

Re: MQT not recognized by optimizer
(in response to Jorg Lueke)
I had mentioned that as well and am not sure I ever got a response.

On Wed, Jan 13, 2010 at 10:03, Jorg Lueke <[login to unmask email]> wrote:

> Have you tried CURRENT REFRESH AGE = 'ANY' when you check the
> query?
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
> meetings,
> Webcasts, Conferences- what is going on next?
> RUG leaders- get your events on the calendar today!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
> the home of IDUG's DB2-L
>
>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Jorg Lueke

Re: MQT not recognized by optimizer
(in response to DB2 DBA Jeffrey Benner)
Then it's clearly a great question :-)

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L