Hints in LUW

Edward Long

Hints in LUW
A hearty handshake rides on the answer so accuracy is important.
We are doing an database performance improvement project and are building our toolkit.
The other 3 DBMS's involved (SQL/SERVER, DB2/Z, and Oracle) all support Hints in some form or another.
I've now been told twice that LUW has not gotten the hint yet.
Is this true? Is there an equivalent?
In a similar vein, what is your experience with Performance Expert -or whatever it is now called - when pointed at LUW/EE?


Edward Long

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Philip Gunning

Re: Hints in LUW
(in response to Edward Long)
Ed, informational constraints came out in DB2 UDB V8.1 in 2003...so for long
time...they are like check constraints or RI but not enforced but can be

Used for query access..I wrote about them then.. Also stat views has been
available for some time..HTH Phil



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: Tuesday, November 23, 2010 2:26 PM
To: [login to unmask email]
Subject: [DB2-L] Hints in LUW




A hearty handshake rides on the answer so accuracy is important.
We are doing an database performance improvement project and are building
our toolkit.
The other 3 DBMS's involved (SQL/SERVER, DB2/Z, and Oracle) all support
Hints in some form or another.
I've now been told twice that LUW has not gotten the hint yet.
Is this true? Is there an equivalent?
In a similar vein, what is your experience with Performance Expert -or
whatever it is now called - when pointed at LUW/EE?


Edward Long



_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG
difference!

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.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Ian Bjorhovde

Re: Hints in LUW
(in response to Philip Gunning)
I would also add that DB2 LUW supports optimization profiles, which are
basically hints that are stored in the database (in SYSTOOLS.OPT_PROFILE) –
as opposed to coding hints directly into your SQL statements.

This feature has been around since at least DB2 9.1, but I believe it was
unofficially included in later fixpacks for 8.2.


Ian Bjorhovde


On Tue, Nov 23, 2010 at 12:36 PM, Phil Gunning <[login to unmask email]> wrote:

> Ed, informational constraints came out in DB2 UDB V8.1 in 2003…..so for
> long time………they are like check constraints or RI but not enforced but can
> be
>
> Used for query access….I wrote about them then…. Also stat views has been
> available for some time….HTH Phil
>
>
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Ed Long
> *Sent:* Tuesday, November 23, 2010 2:26 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] Hints in LUW
>
>
>
> A hearty handshake rides on the answer so accuracy is important.
> We are doing an database performance improvement project and are building
> our toolkit.
> The other 3 DBMS's involved (SQL/SERVER, DB2/Z, and Oracle) all support
> Hints in some form or another.
> I've now been told twice that LUW has not gotten the hint yet.
> Is this true? Is there an equivalent?
> In a similar vein, what is your experience with Performance Expert -or
> whatever it is now called - when pointed at LUW/EE?
>
>
> Edward Long
>
>
> ------------------------------
>
> [image: Independent, not-for-profit, User Run - the IDUG difference! ] < 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 >
>
> ------------------------------
>
> [image: Independent, not-for-profit, User Run - the IDUG difference! ] < 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 >
>

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Martin Hubel

Re: Hints in LUW
(in response to Ian Bjorhovde)
DB2 LUW supports hints through optimization profiles. They are coded in XML, and can be enabled via a SET CURRENT OPTIMIZATION PROFILE statement.

As with DB2 on z/OS, I never encourage their use, nor in a classroom do I spend time teaching people how to use them. If they really need them, they should be advanced enough to figure it out for themselves.

As for Performance Expert, I would encourage people to look at what you can do for free using the administrative views in DB2 9 and forward. If you need performance tools with greater and useful functionality, consider DBI or other third party offerings.

hth--Martin

>> A hearty handshake rides on the answer so accuracy is important.
>> We are doing an database performance improvement project and are building
>> our toolkit.
>> The other 3 DBMS's involved (SQL/SERVER, DB2/Z, and Oracle) all support
>> Hints in some form or another.
>> I've now been told twice that LUW has not gotten the hint yet.
>> Is this true? Is there an equivalent?
>> In a similar vein, what is your experience with Performance Expert -or
>> whatever it is now called - when pointed at LUW/EE?


>> Edward Long






====================
Martin Hubel
MHC Inc.
[login to unmask email]
+1 905-764-7498
+1 416-670-7498 Mobile
Skype: db2hubel
Yahoo IM: db2hubel

Charter Member - IBM Gold Consultant Program
IBM Information Champion

DB2 Certifications include:
DB2 LUW 9.7 Advanced DBA
DB2 z/OS 10 DBA
DB2 9.7 Solutions Developer
====================


The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

Edward Long

Re: Hints in LUW
(in response to Martin Hubel)
Thank you to everyone who responded on this.
This project appears to be trending towards the buffet scenario where we provide a range of options, and costs, and someone picks.
I've looked at the admin views you refer to; perhaps because I spent too much time with DB2PM and Omegamon, I expected more I guess.
Before we can look at the third party tools we will have to demonstrate that PE and the admin tools don't do the job.

In a similar vein, I've been looking very closely at Real Application Testing from Oracle. Is anyone aware of something similar for LUW? If RAT works as advertised it may be a real winner. Just think, no Loadrunner scripts but repeatable tests. Too good to be true perhaps but I can dream.

Edward Long

--- On Tue, 11/23/10, Martin Hubel <[login to unmask email]> wrote:

From: Martin Hubel <[login to unmask email]>
Subject: Re: [DB2-L] Hints in LUW
To: [login to unmask email]
Date: Tuesday, November 23, 2010, 5:08 PM





DB2 LUW supports hints through optimization profiles. They are coded in XML, and can be enabled via a SET CURRENT OPTIMIZATION PROFILE statement.
As with DB2 on z/OS, I never encourage their use, nor in a classroom do I spend time teaching people how to use them. If they really need them, they should be advanced enough to figure it out for themselves.
As for Performance Expert, I would encourage people to look at what you can do for free using the administrative views in DB2 9 and forward. If you need performance tools with greater and useful functionality, consider DBI or other third party offerings.
hth--Martin



>> A hearty handshake rides on the answer so accuracy is important.

>> We are doing an database performance improvement project and are building

>> our toolkit.

>> The other 3 DBMS's involved (SQL/SERVER, DB2/Z, and Oracle) all support

>> Hints in some form or another.

>> I've now been told twice that LUW has not gotten the hint yet.

>> Is this true? Is there an equivalent?

>> In a similar vein, what is your experience with Performance Expert -or

>> whatever it is now called - when pointed at LUW/EE?





>> Edward Long













====================

Martin Hubel

MHC Inc.

[login to unmask email]

+1 905-764-7498

+1 416-670-7498 Mobile

Skype: db2hubel

Yahoo IM: db2hubel



Charter Member - IBM Gold Consultant Program

IBM Information Champion



DB2 Certifications include:

DB2 LUW 9.7 Advanced DBA

DB2 z/OS 10 DBA

DB2 9.7 Solutions Developer

====================




The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Philip Gunning

Re: Hints in LUW
(in response to Edward Long)
HADR in 9.7 has ROS -- read only on the standby, and true shared architecture with DB2 purescale. PG
Sent via BlackBerry by AT&T

-----Original Message-----
From: Ed Long <[login to unmask email]>
Sender: IDUG DB2-L <[login to unmask email]>
Date: Tue, 23 Nov 2010 15:26:41
To: <[login to unmask email]>
Reply-To: IDUG DB2-L <[login to unmask email]>
Subject: Re: [DB2-L] Hints in LUW

Thank you to everyone who responded on this.
This project appears to be trending towards the buffet scenario where we provide a range of options, and costs, and someone picks.
I've looked at the admin views you refer to; perhaps because I spent too much time with DB2PM and Omegamon, I expected more I guess.
Before we can look at the third party tools we will have to demonstrate that PE and the admin tools don't do the job.

In a similar vein, I've been looking very closely at Real Application Testing from Oracle. Is anyone aware of something similar for LUW? If RAT works as advertised it may be a real winner. Just think, no Loadrunner scripts but repeatable tests. Too good to be true perhaps but I can dream.

Edward Long

--- On Tue, 11/23/10, Martin Hubel <[login to unmask email]> wrote:

From: Martin Hubel <[login to unmask email]>
Subject: Re: [DB2-L] Hints in LUW
To: [login to unmask email]
Date: Tuesday, November 23, 2010, 5:08 PM





DB2 LUW supports hints through optimization profiles. They are coded in XML, and can be enabled via a SET CURRENT OPTIMIZATION PROFILE statement.
As with DB2 on z/OS, I never encourage their use, nor in a classroom do I spend time teaching people how to use them. If they really need them, they should be advanced enough to figure it out for themselves.
As for Performance Expert, I would encourage people to look at what you can do for free using the administrative views in DB2 9 and forward. If you need performance tools with greater and useful functionality, consider DBI or other third party offerings.
hth--Martin



>> A hearty handshake rides on the answer so accuracy is important.

>> We are doing an database performance improvement project and are building

>> our toolkit.

>> The other 3 DBMS's involved (SQL/SERVER, DB2/Z, and Oracle) all support

>> Hints in some form or another.

>> I've now been told twice that LUW has not gotten the hint yet.

>> Is this true? Is there an equivalent?

>> In a similar vein, what is your experience with Performance Expert -or

>> whatever it is now called - when pointed at LUW/EE?





>> Edward Long













====================

Martin Hubel

MHC Inc.

[login to unmask email]

+1 905-764-7498

+1 416-670-7498 Mobile

Skype: db2hubel

Yahoo IM: db2hubel



Charter Member - IBM Gold Consultant Program

IBM Information Champion



DB2 Certifications include:

DB2 LUW 9.7 Advanced DBA

DB2 z/OS 10 DBA

DB2 9.7 Solutions Developer

====================




The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Philip Gunning

Re: Hints in LUW
(in response to Philip Gunning)
Ed, recent improvements in PE look promising, sat in on a few presentations
at IOD. I would also check out Speedgain from ITgain, makes good use of
db2pd. Admin views and convenience views are ok but not a replacement for
knowledge nor do they tell the whole story and for the most part don't
identify the problem. If you are very experienced then only using the DB2
provided monitoring facilities (snapshot, admin routines and views, db2top)
then is adequate. PG



Phil Gunning

IBM Information Champion

www.gts1consulting.com

Database Industry Analyst

DB2 Consulting, Books on DB2, Outsourcing, Education, Remote Support

Direct +1.610.451.5801

Fax 636.216.8192

IBM Advanced Business Partner

IBM Authorized Reseller





From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: Tuesday, November 23, 2010 6:27 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Hints in LUW




Thank you to everyone who responded on this.
This project appears to be trending towards the buffet scenario where we
provide a range of options, and costs, and someone picks.
I've looked at the admin views you refer to; perhaps because I spent too
much time with DB2PM and Omegamon, I expected more I guess.
Before we can look at the third party tools we will have to demonstrate that
PE and the admin tools don't do the job.

In a similar vein, I've been looking very closely at Real Application
Testing from Oracle. Is anyone aware of something similar for LUW? If RAT
works as advertised it may be a real winner. Just think, no Loadrunner
scripts but repeatable tests. Too good to be true perhaps but I can dream.

Edward Long

--- On Tue, 11/23/10, Martin Hubel <[login to unmask email]> wrote:


From: Martin Hubel <[login to unmask email]>
Subject: Re: [DB2-L] Hints in LUW
To: [login to unmask email]
Date: Tuesday, November 23, 2010, 5:08 PM

DB2 LUW supports hints through optimization profiles. They are coded in XML,
and can be enabled via a SET CURRENT OPTIMIZATION PROFILE statement.

As with DB2 on z/OS, I never encourage their use, nor in a classroom do I
spend time teaching people how to use them. If they really need them, they
should be advanced enough to figure it out for themselves.

As for Performance Expert, I would encourage people to look at what you can
do for free using the administrative views in DB2 9 and forward. If you need
performance tools with greater and useful functionality, consider DBI or
other third party offerings.

hth--Martin

>> A hearty handshake rides on the answer so accuracy is important.
>> We are doing an database performance improvement project and are building
>> our toolkit.
>> The other 3 DBMS's involved (SQL/SERVER, DB2/Z, and Oracle) all support
>> Hints in some form or another.
>> I've now been told twice that LUW has not gotten the hint yet.
>> Is this true? Is there an equivalent?
>> In a similar vein, what is your experience with Performance Expert -or
>> whatever it is now called - when pointed at LUW/EE?


>> Edward Long






====================
Martin Hubel
MHC Inc.
[login to unmask email]
+1 905-764-7498
+1 416-670-7498 Mobile
Skype: db2hubel
Yahoo IM: db2hubel

Charter Member - IBM Gold Consultant Program
IBM Information Champion

DB2 Certifications include:
DB2 LUW 9.7 Advanced DBA
DB2 z/OS 10 DBA
DB2 9.7 Solutions Developer
====================

_____

< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG
difference!

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 > Independent, not-for-profit, User Run - the IDUG
difference!

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.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Patrick Bossman

Re: Hints in LUW
(in response to Philip Gunning)
I could be wrong, but when Phil said stats views here - I think he was recommending that you consider using statistics views to provide the optimizer more accurate selectivity information so the optimizer chooses a more efficient access path by itself rather than using optimization hints.

The discussion thread talked about views later, but administrative views not stats views.

Best regards,
Pat Bossman

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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

Edward Long

Re: Hints in LUW
(in response to Patrick Bossman)
Thanks Patrick.
What I took away from the discussion was that LUW does not have Hints per se but does offer several functions that might simulate the key purpose of a hint, namely directly influence the optimizer's choice of access path.

Edward Long

--- On Wed, 11/24/10, Patrick Bossman <[login to unmask email]> wrote:


From: Patrick Bossman <[login to unmask email]>
Subject: Re: [DB2-L] Hints in LUW
To: [login to unmask email]
Date: Wednesday, November 24, 2010, 11:20 AM


I could be wrong, but when Phil said stats views here - I think he was recommending that you consider using statistics views to provide the optimizer more accurate selectivity information so the optimizer chooses a more efficient access path by itself rather than using optimization hints.

The discussion thread talked about views later, but administrative views not stats views.

Best regards,
Pat Bossman

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *  http://IDUG.ORG/NA *
*   If you are going to attend only one conference this year, this is it!   *
**    DB2 certification -> no additional charge
**    Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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

Patrick Bossman

Re: Hints in LUW
(in response to Edward Long)
Hi Ed,
Take a look here.
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/t0024532.htm

There are more links at the bottom of the page.

Happy Thanksgiving!
Pat Bossman

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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