MQT question for db2 v8 on z/OS

Deepak Goyal

MQT question for db2 v8 on z/OS
Hi

Does anyone know about the MAINTYPE and REFSHAGE zparm ? We have a
configuration where MAINTYPE=USER and REFSHAGE=ANY. What does this
mean ? Will this configuration pick any MQT ( user/system) ?

Thanks

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Muthuraj

Re: MQT question for db2 v8 on z/OS
(in response to Deepak Goyal)
Maintype and Refresh age will be used by AQR to decide MQT should be
considered when deciding the accesspath. Both Zparms are related. Maintype
can have System, user,all and none. Refresh age can have 0 or any. If
refresh age is 0, no MQT will be considered. if refresh is any, then which
MQTs should be considered will be decided by Maintype Zparm. Hope value
itself is self explanatory. before all these, these values will be effective
only for the MQTs defined with Enable optimization Clause.

Regards,
Muthu

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Deepak Goyal

Re: MQT question for db2 v8 on z/OS
(in response to SUBSCRIBE DB2-L Muthuraj)
Thanks Muthu,

I have the following configuration on my dev system -

REFSHAGE = ANY
MAINTYPE = USER

So as per manual, AQR should pick up any USER MQT but not SYSTEM MQT.

I did a simple test where I defined two MQTS (SYSTEM / USER) on the same
table with enable query optimization. When I explained my sample SQL it
picked up the SYSTEM MQT and when I dropped the SYSTEM MQT, it was not
picking any MQT at all. So in brief it was not picking my USER MQT, that's
surprised me a lot.

I have run the stats on both MQTS after populating them. And both MQTs
have the same definition except the maintenance type. So I was expecting
DB2 tp pick USER MQT not SYSTEM MQT at all as per our configuration.

So what do you think could go wrong in this case ?

Thanks

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

David Simpson

Re: MQT question for db2 v8 on z/OS
(in response to Deepak Goyal)
These zParms specify the default values for special registers CURRENT REFRESH AGE and CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION (gotta love that one for being descriptive). It is the special registers for the session of the application that control the behavior of AQR (CURRENT REFRESH AGE determines if AQR is available, the other one determines which type of MQTs are eligible as described by Muthu below). The zParms control the default values for the special registers, but they may be changed at the application level.

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of SUBSCRIBE DB2-L Muthuraj
Sent: Thursday, December 11, 2008 8:00 PM
To: [login to unmask email]
Subject: Re: [DB2-L] MQT question for db2 v8 on z/OS

Maintype and Refresh age will be used by AQR to decide MQT should be
considered when deciding the accesspath. Both Zparms are related. Maintype
can have System, user,all and none. Refresh age can have 0 or any. If
refresh age is 0, no MQT will be considered. if refresh is any, then which
MQTs should be considered will be decided by Maintype Zparm. Hope value
itself is self explanatory. before all these, these values will be effective
only for the MQTs defined with Enable optimization Clause.

Regards,
Muthu

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Deepak Goyal

Re: MQT question for db2 v8 on z/OS
(in response to David Simpson)
Something I am not able to understand ...

We have a configuration where we have MAINTYPE = USER is defined on
zparm. But when I queried the value for CURRENT MAINTAINED TABLE TYPES
FOR OPTIMIZATION it returned me SYSTEM. I am not overwriting this value
anywhere in my SQL. Then why it returned me SYSTEM ??

Any clue ??

Thanks

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Muthuraj

Re: MQT question for db2 v8 on z/OS
(in response to Deepak Goyal)
May be ZPRAMS would have been changed dynamically.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms