Access path and plan_table comparison - V7 to V7..

Bob Kota

Access path and plan_table comparison - V7 to V7..
Folks,

We are at DB2 V7 currently and are in the process of executing the pre-req step of converting to a single CCSID. Since we will be rebinding all plans and packages, we plan on doing the following:

* Save the contents of the current plan_tables to be compared with the contents of the plan_tables after the "mass" rebinds.
* Compare explain output before and after the "mass" rebinds

Questions:

* We found a COBOL program at the IDUG Code Place link that compares plan_table contents between V7 and V8. Has anyone used this code and how much work is involved to customize it to fit individual needs?
* Does anyone currently use 3rd party tools to do this on a regular basis and what are the costs?
* Has anyone leased or used freeware code during the single CCSID conversion piece?
* HLS Technologies has some freeware listed, is it any good? Are there any other vendors out there that might have what we need.

We need to achieve this pretty quickly so your prompt and accurate responses will be much appreciated.

Thanks.

Bob Kota
Information Technology - Database Services
CHQ 46/NE/010/01
Office: (312)-394-7523
Pager: (877)-396-9345






-----------------------------------------
**************************************************
This e-mail and any of its attachments may contain Exelon
Corporation proprietary information, which is privileged,
confidential, or subject to copyright belonging to the Exelon
Corporation family of Companies.
This e-mail is intended solely for the use of the individual or
entity to which it is addressed. If you are not the intended
recipient of this e-mail, you are hereby notified that any
dissemination, distribution, copying, or action taken in relation
to the contents of and attachments to this e-mail is strictly
prohibited and may be unlawful. If you have received this e-mail
in error, please notify the sender immediately and permanently
delete the original and any copy of this e-mail and any printout.
Thank You.
**************************************************

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

Phil Grainger

Re: Access path and plan_table comparison - V7 to V7..
(in response to Bob Kota)
Hi Bob

Something to factor in - when you migrate to V8 you will (probably) be wanting to rebind everything again, so please don't consider this a "one off" requirement.....

Like many other vendors, our Plan Analyzer product will allow not only comparisons of changes to access paths and/or sql statements but will also allow you to filter the "what's changed" reports to only see access paths that have a changed cost estimate outside user defined thresholds

After all, you are really not interested in "what's changed" you are interested in "what's got (a lot) worse"!!

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of [login to unmask email]
Sent: Thu 10/01/2008 13:20
To: [login to unmask email]
Subject: [DB2-L] Access path and plan_table comparison - V7 to V7..



Folks,

We are at DB2 V7 currently and are in the process of executing the pre-req step of converting to a single CCSID. Since we will be rebinding all plans and packages, we plan on doing the following:

* Save the contents of the current plan_tables to be compared with the contents of the plan_tables after the "mass" rebinds.
* Compare explain output before and after the "mass" rebinds


Questions:

* We found a COBOL program at the IDUG Code Place link that compares plan_table contents between V7 and V8. Has anyone used this code and how much work is involved to customize it to fit individual needs?
* Does anyone currently use 3rd party tools to do this on a regular basis and what are the costs?
* Has anyone leased or used freeware code during the single CCSID conversion piece?
* HLS Technologies has some freeware listed, is it any good? Are there any other vendors out there that might have what we need.


We need to achieve this pretty quickly so your prompt and accurate responses will be much appreciated.

Thanks.

Bob Kota
Information Technology - Database Services
CHQ 46/NE/010/01
Office: (312)-394-7523
Pager: (877)-396-9345



************************************************** This e-mail and any of its attachments may contain Exelon Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Exelon Corporation family of Companies. This e-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this e-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this e-mail is strictly prohibited and may be unlawful. If you have received this e-mail in error, please notify the sender immediately and permanently delete the original and any copy of this e-mail and any printout. Thank You. **************************************************


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org <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 < http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

Phil Grainger

Re: Access path and plan_table comparison - V7 to V7..
(in response to Phil Grainger)
Hi Bob

Something to factor in - when you migrate to V8 you will (probably) be
wanting to rebind everything again, so please don't consider this a "one
off" requirement.....

Like many other vendors, our Plan Analyzer product will allow not only
comparisons of changes to access paths and/or sql statements but will
also allow you to filter the "what's changed" reports to only see access
paths that have a changed cost estimate outside user defined thresholds

After all, you are really not interested in "what's changed" you are
interested in "what's got (a lot) worse"!!

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of [login to unmask email]
Sent: Thu 10/01/2008 13:20
To: [login to unmask email]
Subject: [DB2-L] Access path and plan_table comparison - V7 to V7..



Folks,

We are at DB2 V7 currently and are in the process of executing the
pre-req step of converting to a single CCSID. Since we will be
rebinding all plans and packages, we plan on doing the following:

* Save the contents of the current plan_tables to be compared with
the contents of the plan_tables after the "mass" rebinds.
* Compare explain output before and after the "mass" rebinds


Questions:

* We found a COBOL program at the IDUG Code Place link that
compares plan_table contents between V7 and V8. Has anyone used this
code and how much work is involved to customize it to fit individual
needs?
* Does anyone currently use 3rd party tools to do this on a
regular basis and what are the costs?
* Has anyone leased or used freeware code during the single CCSID
conversion piece?
* HLS Technologies has some freeware listed, is it any good? Are
there any other vendors out there that might have what we need.


We need to achieve this pretty quickly so your prompt and accurate
responses will be much appreciated.

Thanks.

Bob Kota
Information Technology - Database Services
CHQ 46/NE/010/01
Office: (312)-394-7523
Pager: (877)-396-9345



************************************************** This e-mail and any
of its attachments may contain Exelon Corporation proprietary
information, which is privileged, confidential, or subject to copyright
belonging to the Exelon Corporation family of Companies. This e-mail is
intended solely for the use of the individual or entity to which it is
addressed. If you are not the intended recipient of this e-mail, you are
hereby notified that any dissemination, distribution, copying, or action
taken in relation to the contents of and attachments to this e-mail is
strictly prohibited and may be unlawful. If you have received this
e-mail in error, please notify the sender immediately and permanently
delete the original and any copy of this e-mail and any printout. Thank
You. **************************************************


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< 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
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

Avram Friedman

Re: Access path and plan_table comparison - V7 to V7..
(in response to Phil Grainger)
Bob

Are you unloading and reloading your data to address your CCSID issues (Your
data actually has some of the 7 or so EBCDIC characters that are impacted)
or are you using the IBM process / usermod that allows you to ALTER the
CCSIDs on the table space?

Do you make a routine practice of examing access paths and rebinding?

Consider the following table x axis Load unload
y axis Micromanage access paths
Result High priority access path review


No Yes

No No Usually
Not

Yes Yes Yes

Now I surmise your shop does not micromanage access paths.
How do I know this? Because you asked a HOW TO question
If you micomanaged access paths there would of been no question!

If you do not micromanage access paths the big case where you want to
seriously consider a review is when columns participating in indexes (key
columns) contain the characters that are CCSID senistive.

The fact of the matter is very few shops micro manage access paths. There
was a bit of a proof of this idea a year or 2 ago when there were some very
active threads on To REBIND or Not and a very large group was clearly in
the "not" class. One of the things that makes this history important is there is
a lot of presure to say that ones actions are perfect. In the medical world for
example a person saying his complience say for avoiding salt to address high
blood presure is bad is very beliveable but saying its good is a marginal
statement. That is people are lobbied to do the so called right thing and will
not admit to doing something that is politically wrong.

In the zos DB2 V7 world where there is a lot of pressure to convert to V8
before looming end of service. There is a lot of work to do in a short time
with out manufacturing extra steps that may be inconsistant with your normal
shop practices.

Regards
Avram Friedman

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

Phil Grainger

Re: Access path and plan_table comparison - V7 to V7..
(in response to Avram Friedman)
Hi Bob

Something to factor in - when you migrate to V8 you will (probably) be
wanting to rebind everything again, so please don't consider this a "one
off" requirement.....

Like many other vendors, our Plan Analyzer product will allow not only
comparisons of changes to access paths and/or sql statements but will
also allow you to filter the "what's changed" reports to only see access
paths that have a changed cost estimate outside user defined thresholds

After all, you are really not interested in "what's changed" you are
interested in "what's got (a lot) worse"!!

Phil Grainger
CA


________________________________

From: DB2 Data Base Discussion List on behalf of [login to unmask email]
Sent: Thu 10/01/2008 13:20
To: [login to unmask email]
Subject: [DB2-L] Access path and plan_table comparison - V7 to V7..



Folks,

We are at DB2 V7 currently and are in the process of executing the
pre-req step of converting to a single CCSID. Since we will be
rebinding all plans and packages, we plan on doing the following:

* Save the contents of the current plan_tables to be compared with
the contents of the plan_tables after the "mass" rebinds.
* Compare explain output before and after the "mass" rebinds


Questions:

* We found a COBOL program at the IDUG Code Place link that
compares plan_table contents between V7 and V8. Has anyone used this
code and how much work is involved to customize it to fit individual
needs?
* Does anyone currently use 3rd party tools to do this on a
regular basis and what are the costs?
* Has anyone leased or used freeware code during the single CCSID
conversion piece?
* HLS Technologies has some freeware listed, is it any good? Are
there any other vendors out there that might have what we need.


We need to achieve this pretty quickly so your prompt and accurate
responses will be much appreciated.

Thanks.

Bob Kota
Information Technology - Database Services
CHQ 46/NE/010/01
Office: (312)-394-7523
Pager: (877)-396-9345



************************************************** This e-mail and any
of its attachments may contain Exelon Corporation proprietary
information, which is privileged, confidential, or subject to copyright
belonging to the Exelon Corporation family of Companies. This e-mail is
intended solely for the use of the individual or entity to which it is
addressed. If you are not the intended recipient of this e-mail, you are
hereby notified that any dissemination, distribution, copying, or action
taken in relation to the contents of and attachments to this e-mail is
strictly prohibited and may be unlawful. If you have received this
e-mail in error, please notify the sender immediately and permanently
delete the original and any copy of this e-mail and any printout. Thank
You. **************************************************


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< 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
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

Gary Bronziet

Re: Access path and plan_table comparison - V7 to V7..
(in response to Phil Grainger)
Bear in mind that comparing access paths is not as simple as it sounds. - The databases in the different environments (e.g. V7 and V8) may be set up with different owners and this has to be dealt with. - You need to know whether an access path change is expected or not, and if not whether it is for the “better” or for “worse” and by how much. - To assess whether the change is better or worse you can compare the cost estimates but you have to allow for the fact that V7 costs may be different to V8 costs even when the access path is the same. - You have to ensure statistics are identical in both systems. - If your environment includes Dynamic SQL, then you need to include Dynamic SQL in the analysis. - To allow you to focus on the most significant access path changes, (i.e. What’s got the “most worse” and has the most affect on you) it is necessary to take into account the execution frequency of the relevant SQL. EZ-DB2 from Cogito is one product that deals with all of this in simple fashion. CA, IBM, HLS and possibly others also have offerings. Best Regards Gary Bronziet EZ-DB2 Product Manager [login to unmask email] | Ph 020 8501 0176 | Fax 020 8501 1211 | Cell 07802 444 278 Cogito Limited Performance Software for IBM DB2® and CA IDMS™ www.cogito.co.uk
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org 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

Gary Bronziet

Re: Access path and plan_table comparison - V7 to V7..
(in response to Gary Bronziet)
Bear in mind that comparing access paths is not as simple as it sounds.

• The databases in the different environments (e.g. V7 and V8) may be set up
with different owners and this has to be dealt with.

• You need to know whether an access path change is expected or not, and if
not whether it is for the “better” or for “worse” and by how much.

• To assess whether the change is better or worse you can compare the cost
estimates but you have to allow for the fact that V7 costs may be different to
V8 costs even when the access path is the same.

• You have to ensure statistics are identical in both systems.

• If your environment includes Dynamic SQL, then you need to include
Dynamic SQL in the analysis.

• To allow you to focus on the most significant access path changes, (i.e.
What’s got the “most worse” and has the most affect on you) it is necessary
to take into account the execution frequency of the relevant SQL.

EZ-DB2 from Cogito is one product that deals with all of this in simple fashion.
CA, IBM, HLS and possibly others also have offerings.

Best Regards

Gary Bronziet
EZ-DB2 Product Manager
[login to unmask email] | Ph 020 8501 0176 | Fax 020 8501 1211 | Cell
07802 444 278
Cogito Limited Performance Software for IBM DB2® and CA IDMS™
www.cogito.co.uk

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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