DB2 Z/os V8 Package Rebinds

Dawn Kastelic

DB2 Z/os V8 Package Rebinds
We will be moving to Compatibility Mode within the next few weeks with a
target for ENFM in April
We do not plan on rebinding all of our packages in Compatibility Mode.
While in ENFM mode,
if the packages have not been re-bound, when they get executed will they
need to be expanded or
converted to a version 8 format (since the catalog is now unicode?) Does
this expansion need to
take place every time the package gets executed and what type of overhead
could this incur?
If this is a concern, then we will consider re-binding a subset of our most
active packages when
we move to ENFM. Any experiences/recommendations to share?

Thank you.

Dawn Kastelic
DB2 Database Administration
Highmark Blue Cross Blue Shield
Pittsburgh, PA

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Joan Keemle

Re: DB2 Z/os V8 Package Rebinds
(in response to Dawn Kastelic)
V8 uses a new format for plans and packages. Once in V8, whether CM,
ENFM, or NFM, if the plan/package is in old V7 format, it will be
expanded or "puffed" to the new format the first time accessed. A
rebind would save this from happening at execution. It might also
alleviate surprises with access path changes, etc.

Rebinds are always generally a good idea with new releases to leverage
optimizer enhancements. For critical applications, run explains in V7
first to retain the current access path in PLAN_TABLE. That would allow
use of OPTHINTS as a workaround for the anomoly or rare case that
doesn't optimize the way you would like.

Hope this helps! Thanks, Joan

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Dawn Kastelic
Sent: Friday, January 06, 2006 12:11 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Z/os V8 Package Rebinds

We will be moving to Compatibility Mode within the next few weeks with a
target for ENFM in April We do not plan on rebinding all of our packages
in Compatibility Mode.
While in ENFM mode,
if the packages have not been re-bound, when they get executed will they
need to be expanded or converted to a version 8 format (since the
catalog is now unicode?) Does this expansion need to take place every
time the package gets executed and what type of overhead could this
incur?
If this is a concern, then we will consider re-binding a subset of our
most active packages when we move to ENFM. Any
experiences/recommendations to share?

Thank you.

Dawn Kastelic
DB2 Database Administration
Highmark Blue Cross Blue Shield
Pittsburgh, PA

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: DB2 Z/os V8 Package Rebinds
(in response to Joan Keemle)
Joan

A quick clarification please as I think I am confused....

You say "Once in V8, whether CM, ENFM, or NFM, if the plan/package is in old V7 format, it will be expanded or "puffed" to the new format the first time accessed" and later say "A rebind would save this from happening at execution. It might also alleviate surprises with access path changes" - I hope this DOESN'T mean that ALL plans and packages will be rebound by DB2 at v8. This is news to me if it does mean that!

I'm also rather surprised that this happens in CM - so v7 is aware of puffed plans??

Thanks

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Keemle Joan T
Sent: Fri 06/01/2006 19:05
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Z/os V8 Package Rebinds



V8 uses a new format for plans and packages. Once in V8, whether CM,
ENFM, or NFM, if the plan/package is in old V7 format, it will be
expanded or "puffed" to the new format the first time accessed. A
rebind would save this from happening at execution. It might also
alleviate surprises with access path changes, etc.

Rebinds are always generally a good idea with new releases to leverage
optimizer enhancements. For critical applications, run explains in V7
first to retain the current access path in PLAN_TABLE. That would allow
use of OPTHINTS as a workaround for the anomoly or rare case that
doesn't optimize the way you would like.

Hope this helps! Thanks, Joan

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Dawn Kastelic
Sent: Friday, January 06, 2006 12:11 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Z/os V8 Package Rebinds

We will be moving to Compatibility Mode within the next few weeks with a
target for ENFM in April We do not plan on rebinding all of our packages
in Compatibility Mode.
While in ENFM mode,
if the packages have not been re-bound, when they get executed will they
need to be expanded or converted to a version 8 format (since the
catalog is now unicode?) Does this expansion need to take place every
time the package gets executed and what type of overhead could this
incur?
If this is a concern, then we will consider re-binding a subset of our
most active packages when we move to ENFM. Any
experiences/recommendations to share?

Thank you.

Dawn Kastelic
DB2 Database Administration
Highmark Blue Cross Blue Shield
Pittsburgh, PA

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Bell

Re: DB2 Z/os V8 Package Rebinds
(in response to Phil Grainger)
REBIND has been specified for plans older than v2.3.

But the expanded format occurs for plans, packages and DBD's. I seem to
remember it occurs on the first load to EDM pool but isn't saved until
rebind or alter to DBD.

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Grainger, Phil
Sent: Friday, January 06, 2006 3:41 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Z/os V8 Package Rebinds

Joan

A quick clarification please as I think I am confused....

You say "Once in V8, whether CM, ENFM, or NFM, if the plan/package is in old
V7 format, it will be expanded or "puffed" to the new format the first time
accessed" and later say "A rebind would save this from happening at
execution. It might also alleviate surprises with access path changes" - I
hope this DOESN'T mean that ALL plans and packages will be rebound by DB2 at
v8. This is news to me if it does mean that!

I'm also rather surprised that this happens in CM - so v7 is aware of puffed
plans??

Thanks

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Keemle Joan T
Sent: Fri 06/01/2006 19:05
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Z/os V8 Package Rebinds



V8 uses a new format for plans and packages. Once in V8, whether CM,
ENFM, or NFM, if the plan/package is in old V7 format, it will be
expanded or "puffed" to the new format the first time accessed. A
rebind would save this from happening at execution. It might also
alleviate surprises with access path changes, etc.

Rebinds are always generally a good idea with new releases to leverage
optimizer enhancements. For critical applications, run explains in V7
first to retain the current access path in PLAN_TABLE. That would allow
use of OPTHINTS as a workaround for the anomoly or rare case that
doesn't optimize the way you would like.

Hope this helps! Thanks, Joan

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Dawn Kastelic
Sent: Friday, January 06, 2006 12:11 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Z/os V8 Package Rebinds

We will be moving to Compatibility Mode within the next few weeks with a
target for ENFM in April We do not plan on rebinding all of our packages
in Compatibility Mode.
While in ENFM mode,
if the packages have not been re-bound, when they get executed will they
need to be expanded or converted to a version 8 format (since the
catalog is now unicode?) Does this expansion need to take place every
time the package gets executed and what type of overhead could this
incur?
If this is a concern, then we will consider re-binding a subset of our
most active packages when we move to ENFM. Any
experiences/recommendations to share?

Thank you.

Dawn Kastelic
DB2 Database Administration
Highmark Blue Cross Blue Shield
Pittsburgh, PA

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm




----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Joan Keemle

Re: DB2 Z/os V8 Package Rebinds
(in response to Mike Bell)
I'm sorry, and thanks for correcting me! They are not rebound by V8,
but are only expanded.

And yes, the expansion happens in CM as well as ENFM/NFM. V7 knows
enough about it to recognize the V8 format, and it will re-format back
to V7 format (after fallback, for instance).

Thanks Phil.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Grainger, Phil
Sent: Friday, January 06, 2006 3:41 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Z/os V8 Package Rebinds

Joan

A quick clarification please as I think I am confused....

You say "Once in V8, whether CM, ENFM, or NFM, if the plan/package is in
old V7 format, it will be expanded or "puffed" to the new format the
first time accessed" and later say "A rebind would save this from
happening at execution. It might also alleviate surprises with access
path changes" - I hope this DOESN'T mean that ALL plans and packages
will be rebound by DB2 at v8. This is news to me if it does mean that!

I'm also rather surprised that this happens in CM - so v7 is aware of
puffed plans??

Thanks

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Keemle Joan T
Sent: Fri 06/01/2006 19:05
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Z/os V8 Package Rebinds



V8 uses a new format for plans and packages. Once in V8, whether CM,
ENFM, or NFM, if the plan/package is in old V7 format, it will be
expanded or "puffed" to the new format the first time accessed. A
rebind would save this from happening at execution. It might also
alleviate surprises with access path changes, etc.

Rebinds are always generally a good idea with new releases to leverage
optimizer enhancements. For critical applications, run explains in V7
first to retain the current access path in PLAN_TABLE. That would allow
use of OPTHINTS as a workaround for the anomoly or rare case that
doesn't optimize the way you would like.

Hope this helps! Thanks, Joan

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Dawn Kastelic
Sent: Friday, January 06, 2006 12:11 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Z/os V8 Package Rebinds

We will be moving to Compatibility Mode within the next few weeks with a
target for ENFM in April We do not plan on rebinding all of our packages
in Compatibility Mode.
While in ENFM mode,
if the packages have not been re-bound, when they get executed will they
need to be expanded or converted to a version 8 format (since the
catalog is now unicode?) Does this expansion need to take place every
time the package gets executed and what type of overhead could this
incur?
If this is a concern, then we will consider re-binding a subset of our
most active packages when we move to ENFM. Any
experiences/recommendations to share?

Thank you.

Dawn Kastelic
DB2 Database Administration
Highmark Blue Cross Blue Shield
Pittsburgh, PA

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm




------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Roland Schiradin

Re: DB2 Z/os V8 Package Rebinds
(in response to Joan Keemle)
DB2 V8 production in CM mode since Nov 2004.
SAP-DB2 V8 in NFM since several months.

We did this and I also recommend to rebind all plans/packages.
One package cause an abend which was fixed with a DB2 ptf.

One of about 2000 packages got a bad access path because
of the OPTIMZE FOR ONE ROW clause. I recommend to have Visual
Explain installed as this helps to provide the data requested
by IBM support. As a workaround we use OPTHINT.

Our CICS applications run alsmost close compared to V7.
Some batch show better results but most of them consuming
about 10 to 30 percent more CPU. We still looking into
those issue

Roland



-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email] On Behalf Of Keemle Joan T
Sent: Friday, January 06, 2006 8:05 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Z/os V8 Package Rebinds


V8 uses a new format for plans and packages. Once in V8,
whether CM, ENFM, or NFM, if the plan/package is in old V7
format, it will be expanded or "puffed" to the new format the
first time accessed. A rebind would save this from happening
at execution. It might also alleviate surprises with access
path changes, etc.

Rebinds are always generally a good idea with new releases to
leverage optimizer enhancements. For critical applications,
run explains in V7 first to retain the current access path in
PLAN_TABLE. That would allow use of OPTHINTS as a workaround
for the anomoly or rare case that
doesn't optimize the way you would like.

Hope this helps! Thanks, Joan

-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email] On Behalf Of Dawn Kastelic
Sent: Friday, January 06, 2006 12:11 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Z/os V8 Package Rebinds

We will be moving to Compatibility Mode within the next few
weeks with a target for ENFM in April We do not plan on
rebinding all of our packages in Compatibility Mode. While in
ENFM mode, if the packages have not been re-bound, when they
get executed will they need to be expanded or converted to a
version 8 format (since the catalog is now unicode?) Does this
expansion need to take place every time the package gets
executed and what type of overhead could this incur? If this is
a concern, then we will consider re-binding a subset of our
most active packages when we move to ENFM. Any
experiences/recommendations to share?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm