RUNSTAT and REBIND frequency.

Brendan Friel

RUNSTAT and REBIND frequency.
I've got a client with a slew of COBOL programs with embedded SQL.
The application is fairly stable so that hardly any of the programs have
been rebound for years.
However, REORGs and RUNSTATS are run frequently, even though there isn't a
huge amount of activity against the tables.

Question one : Is it 100% true that the RUNSTATS won't have ANY effect until
REBIND ? Or is DB2 smart enough to peek at the RUNSTATS information on the
fly even for static SQL ?

Question two: We've updated the release of DB2 (to V5) at least once since
most of these programs were rebound. Going to V6 this year. Is there a
benefit to mass rebinding when a new release of DB2 goes in ?

Question three: What is the consensus (or range of opinions) on frequency
and methodology of REORG/RUNSTATS/REBIND ?

TIA
Brendan

----------------------------------------------------------------
The information transmitted is intended only for the person or entity to which
it is addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. If you received this in error, please
contact the sender and delete the material from any computer.



Claudio Meluzzi Mendes

Re: RUNSTAT and REBIND frequency.
(in response to Brendan Friel)
>Question three: What is the consensus (or range of opinions) on frequency
>and methodology of REORG/RUNSTATS/REBIND ?


We are a small shop - 500 tables, the biggest tables have an average of
6 million lines. There are about 700 COBOL programs/packages. We run
RUNSTATS and BIND (REPLACE) on Sundays for all static plans and packages.
REORGs are executed monthly only for tablespaces which need it or,
eventually, when a table is changed by an ALTER TABLE statement.

Claudio
*************************************
Claudio Meluzzi Mendes
BEG - Banco do Estado de Goias
Goiania - GO - Brasil
*************************************



Vijay Subramanyam

Re: RUNSTAT and REBIND frequency.
(in response to Claudio Meluzzi Mendes)
Hi Brendan,

Answer to Question one:
We have to REBIND after RUNSTATS otherwise DB2 cannot pick up the latest
statistic information from the catalog.
REBIND is done so that it picks up the latest statistics from the DB2
catalog.

VIJAY

-----Original Message-----
From: Brendan Friel [SMTP:[login to unmask email]
Sent: Wednesday, May 17, 2000 7:22 PM
To: [login to unmask email]
Subject: RUNSTAT and REBIND frequency.

I've got a client with a slew of COBOL programs with embedded SQL.
The application is fairly stable so that hardly any of the programs have
been rebound for years.
However, REORGs and RUNSTATS are run frequently, even though there isn't a
huge amount of activity against the tables.

Question one : Is it 100% true that the RUNSTATS won't have ANY effect
until
REBIND ? Or is DB2 smart enough to peek at the RUNSTATS information on the
fly even for static SQL ?

Question two: We've updated the release of DB2 (to V5) at least once since
most of these programs were rebound. Going to V6 this year. Is there a
benefit to mass rebinding when a new release of DB2 goes in ?

Question three: What is the consensus (or range of opinions) on frequency
and methodology of REORG/RUNSTATS/REBIND ?

TIA
Brendan

----------------------------------------------------------------
The information transmitted is intended only for the person or entity to
which
it is addressed and may contain confidential and/or privileged material.
Any
review, retransmission, dissemination or other use of, or taking of any
action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. If you received this in error, please
contact the sender and delete the material from any computer.








Michael Ebert

Re: RUNSTAT and REBIND frequency.
(in response to Vijay Subramanyam)
In our installation, I have been trying hard since one year now to have the
weekly REBINDs removed from the main application. The reason is as follows: many
of the batch programs used by this application contain rather ugly joins between
3 or 4 tables (they're really several SELECTs merged into one, based on the
value of host variables, so there's constructs like "... OR (:hv1='constant' AND
... " in the WHERE clauses). REORG is run every two weeks, RUNSTATS weekly. It
turned out that in certain cases, DB2 flip-flopped weekly between two different
access paths, one ok and one very bad (based on the somewhat disorganised data).
On the other hand, after removing the REBIND (from just one market - the
application and the tables etc. are cloned for 40 different markets), and always
using just the "after-REORG" access path, the performance was just about the
same at every run. Of course this assumes that your tables are more or less
stable.

With every REBIND there is the danger of DB2 messing up your access path, even
with the maximum amount of statistics.

Remember the old engineers adage, "if it work, don't mess with it".

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




From: Brendan Friel <[login to unmask email]> on 17/05/2000 13:51 GMT

Please respond to DB2 Data Base Discussion List <[login to unmask email]>





|--------->
| |
|--------->
>--------------------------------------------------------------------------->
| |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|
|--------->
|To: |
|--------->
>--------------------------------------------------------------------------->
|[login to unmask email] |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
| |
>-------------------------------------------|
|--------->
|cc: |
|--------->
>--------------------------------------------------------------------------->
| (bcc: Michael Ebert/MUC/AMADEUS) |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
| |
>-------------------------------------------|
|--------->
| |
|--------->
>--------------------------------------------------------------------------->
| |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|
|--------->
|Subject: |
|--------->
>--------------------------------------------------------------------------->
|RUNSTAT and REBIND frequency. |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|







I've got a client with a slew of COBOL programs with embedded SQL.
The application is fairly stable so that hardly any of the programs have
been rebound for years.
However, REORGs and RUNSTATS are run frequently, even though there isn't a
huge amount of activity against the tables.

Question one : Is it 100% true that the RUNSTATS won't have ANY effect until
REBIND ? Or is DB2 smart enough to peek at the RUNSTATS information on the
fly even for static SQL ?

Question two: We've updated the release of DB2 (to V5) at least once since
most of these programs were rebound. Going to V6 this year. Is there a
benefit to mass rebinding when a new release of DB2 goes in ?

Question three: What is the consensus (or range of opinions) on frequency
and methodology of REORG/RUNSTATS/REBIND ?

TIA
Brendan

----------------------------------------------------------------
The information transmitted is intended only for the person or entity to which
it is addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. If you received this in error, please
contact the sender and delete the material from any computer.








Fertaki Gina

Re: RUNSTAT and REBIND frequency.
(in response to Michael Ebert)
We have also experienced problems with weekly RUNSTATS and REBIND in the
case where we have flip-flop tables. One of them is emptied every day before
batch so that online transactions can go on adding movements while batch
programs work with the other table (two identical tables for the same data
flip-flopping on a daily basis for continuous operation).
If RUNSTATS finds a table empty, then during rebind DB2 reverts the SQL
access to these tables to Tablespace scan and then during batch process
(when the specific table is full with data) we have experienced very poor
performance because of packages not using the indexes they were supposed to
use.

I would like to ask a question about the recommended approach for massive
binds:
We have some programs that use partioned tablespaces and for which, during
the bind process, we specify DEGREE(ANY) while for the other programs we use
the default DEGREE(1).
During massive bind operations there is a REXX program that retrieves
packages and issues a bind operation for each one with the same default
values.
Can we change the default DEGREE(1) to DEGREE(ANY) for all programs even
though they don't access partitioning tables or will it affect their
performance?
If not, we will have to keep track of the programs binded with DEGREE(ANY)
and issue a seperate bind for them which implies some problems as
maintenance of this list e.t.c.
Thank you

Gina Fertaki-DBA
EFG Eurobank
Athens, Greece



---Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, May 18, 2000 11:52 AM
To: [login to unmask email]
Subject: Re: RUNSTAT and REBIND frequency.


In our installation, I have been trying hard since one year now to have the
weekly REBINDs removed from the main application. The reason is as follows:
many
of the batch programs used by this application contain rather ugly joins
between
3 or 4 tables (they're really several SELECTs merged into one, based on the
value of host variables, so there's constructs like "... OR (:hv1='constant'
AND
... " in the WHERE clauses). REORG is run every two weeks, RUNSTATS weekly.
It
turned out that in certain cases, DB2 flip-flopped weekly between two
different
access paths, one ok and one very bad (based on the somewhat disorganised
data).
On the other hand, after removing the REBIND (from just one market - the
application and the tables etc. are cloned for 40 different markets), and
always
using just the "after-REORG" access path, the performance was just about the
same at every run. Of course this assumes that your tables are more or less
stable.

With every REBIND there is the danger of DB2 messing up your access path,
even
with the maximum amount of statistics.

Remember the old engineers adage, "if it work, don't mess with it".

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




From: Brendan Friel <[login to unmask email]> on 17/05/2000 13:51 GMT

Please respond to DB2 Data Base Discussion List <[login to unmask email]>





|--------->
| |
|--------->

>---------------------------------------------------------------------------
>
|
|

>---------------------------------------------------------------------------
>
>-------------------------------------------|
| |
>-------------------------------------------|
|--------->
|To: |
|--------->

>---------------------------------------------------------------------------
>
|[login to unmask email]
|

>---------------------------------------------------------------------------
>
>-------------------------------------------|
| |
| |
>-------------------------------------------|
|--------->
|cc: |
|--------->

>---------------------------------------------------------------------------
>
| (bcc: Michael Ebert/MUC/AMADEUS)
|

>---------------------------------------------------------------------------
>
>-------------------------------------------|
| |
| |
>-------------------------------------------|
|--------->
| |
|--------->

>---------------------------------------------------------------------------
>
|
|

>---------------------------------------------------------------------------
>
>-------------------------------------------|
| |
>-------------------------------------------|
|--------->
|Subject: |
|--------->

>---------------------------------------------------------------------------
>
|RUNSTAT and REBIND frequency.
|

>---------------------------------------------------------------------------
>
>-------------------------------------------|
| |
>-------------------------------------------|







I've got a client with a slew of COBOL programs with embedded SQL.
The application is fairly stable so that hardly any of the programs have
been rebound for years.
However, REORGs and RUNSTATS are run frequently, even though there isn't a
huge amount of activity against the tables.

Question one : Is it 100% true that the RUNSTATS won't have ANY effect until
REBIND ? Or is DB2 smart enough to peek at the RUNSTATS information on the
fly even for static SQL ?

Question two: We've updated the release of DB2 (to V5) at least once since
most of these programs were rebound. Going to V6 this year. Is there a
benefit to mass rebinding when a new release of DB2 goes in ?

Question three: What is the consensus (or range of opinions) on frequency
and methodology of REORG/RUNSTATS/REBIND ?

TIA
Brendan

----------------------------------------------------------------
The information transmitted is intended only for the person or entity to
which
it is addressed and may contain confidential and/or privileged material.
Any
review, retransmission, dissemination or other use of, or taking of any
action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. If you received this in error, please
contact the sender and delete the material from any computer.













RICK (SWBT) DAVIS

Re: RUNSTAT and REBIND frequency.
(in response to Fertaki Gina)
Brendan, please see embedded comments below indicated by *****

-----Original Message-----
From: Brendan Friel [mailto:[login to unmask email]
Sent: Wednesday, May 17, 2000 8:52 AM
To: [login to unmask email]
Subject: RUNSTAT and REBIND frequency.


I've got a client with a slew of COBOL programs with embedded SQL.
The application is fairly stable so that hardly any of the programs have
been rebound for years.
However, REORGs and RUNSTATS are run frequently, even though there isn't a
huge amount of activity against the tables.

Question one : Is it 100% true that the RUNSTATS won't have ANY effect until
REBIND ?

***** Yes, however that reBIND might have occurred, except see REOPTVARS
below

Or is DB2 smart enough to peek at the RUNSTATS information on the fly even
for static SQL ?

***** No peeking allowed unless REOPTVARS(YES) is specified

Question two: We've updated the release of DB2 (to V5) at least once since
most of these programs were rebound. Going to V6 this year. Is there a
benefit to mass rebinding when a new release of DB2 goes in ?

***** Consider reBINDing only those PLANs or PACKAGEs that might benefit
from the upgrade.
*****

Question three: What is the consensus (or range of opinions) on frequency
and methodology of REORG/RUNSTATS/REBIND ?

***** There's a school of thought that once a PLAN or PACKAGE is bound in
production that EXPLAINed well, unless something changes, leave it alone.
Also, REORGs should be event driven, based on RUNSTATS info -- not blindly
scheduled. Consider setting up RUNSTATS for table/indexspaces based on a
weekly, monthly, or not needed basis. There are products (BMC and PLATINUM)
that can automate finding those tables/indexpaces needing REORG, produce the
required JCL, and even run the job if you wish.
Finally, don't forget to schedule a REORG for tablespaces that have been
ALTERed.
*****
TIA
Brendan
----------------------------------------------------------------
The information transmitted is intended only for the person or entity to
which
it is addressed and may contain confidential and/or privileged material.
Any
review, retransmission, dissemination or other use of, or taking of any
action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. If you received this in error, please
contact the sender and delete the material from any computer.

HTH,
Rick Davis

"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."



RICK (SWBT) DAVIS

Re: RUNSTAT and REBIND frequency.
(in response to RICK (SWBT) DAVIS)
Gina,
Use of DEGREE(ANY), you will discover, only benefits SQL with a very
large result set. The reason being that it takes DB2 quite some time to set
it up parallelism. You should first compare "wall time" between using
DEGREE(ANY) and DEGREE(1) before choosing it. Search the DB2-L archives with
DEGREE(ANY) to find several good comments on this issue.

HTH,
Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are
confidential, and are intended solely for the use of the individual or
entity to whom this e-mail is addressed. If you are not one of the named
recipient(s) or otherwise have reason to believe that you have received this
message in error, please notify the sender at 314-235-6854 and delete this
message immediately from your computer. Any other use, retention,
dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."

-----Original Message-----
From: Fertaki Gina [mailto:[login to unmask email]
Sent: Thursday, May 18, 2000 5:45 AM
To: [login to unmask email]
Subject: Re: RUNSTAT and REBIND frequency.


We have also experienced problems with weekly RUNSTATS and REBIND in the
case where we have flip-flop tables. One of them is emptied every day before
batch so that online transactions can go on adding movements while batch
programs work with the other table (two identical tables for the same data
flip-flopping on a daily basis for continuous operation).
If RUNSTATS finds a table empty, then during rebind DB2 reverts the SQL
access to these tables to Tablespace scan and then during batch process
(when the specific table is full with data) we have experienced very poor
performance because of packages not using the indexes they were supposed to
use.

I would like to ask a question about the recommended approach for massive
binds:
We have some programs that use partioned tablespaces and for which, during
the bind process, we specify DEGREE(ANY) while for the other programs we use
the default DEGREE(1).
During massive bind operations there is a REXX program that retrieves
packages and issues a bind operation for each one with the same default
values.
Can we change the default DEGREE(1) to DEGREE(ANY) for all programs even
though they don't access partitioning tables or will it affect their
performance?
If not, we will have to keep track of the programs binded with DEGREE(ANY)
and issue a seperate bind for them which implies some problems as
maintenance of this list e.t.c.
Thank you

Gina Fertaki-DBA
EFG Eurobank
Athens, Greece



---Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, May 18, 2000 11:52 AM
To: [login to unmask email]
Subject: Re: RUNSTAT and REBIND frequency.


In our installation, I have been trying hard since one year now to have the
weekly REBINDs removed from the main application. The reason is as follows:
many
of the batch programs used by this application contain rather ugly joins
between
3 or 4 tables (they're really several SELECTs merged into one, based on the
value of host variables, so there's constructs like "... OR (:hv1='constant'
AND
... " in the WHERE clauses). REORG is run every two weeks, RUNSTATS weekly.
It
turned out that in certain cases, DB2 flip-flopped weekly between two
different
access paths, one ok and one very bad (based on the somewhat disorganised
data).
On the other hand, after removing the REBIND (from just one market - the
application and the tables etc. are cloned for 40 different markets), and
always
using just the "after-REORG" access path, the performance was just about the
same at every run. Of course this assumes that your tables are more or less
stable.

With every REBIND there is the danger of DB2 messing up your access path,
even
with the maximum amount of statistics.

Remember the old engineers adage, "if it work, don't mess with it".

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




From: Brendan Friel <[login to unmask email]> on 17/05/2000 13:51 GMT

Please respond to DB2 Data Base Discussion List <[login to unmask email]>





|--------->
| |
|--------->

>---------------------------------------------------------------------------
>
|
|

>---------------------------------------------------------------------------
>
>-------------------------------------------|
| |
>-------------------------------------------|
|--------->
|To: |
|--------->

>---------------------------------------------------------------------------
>
|[login to unmask email]
|

>---------------------------------------------------------------------------
>
>-------------------------------------------|
| |
| |
>-------------------------------------------|
|--------->
|cc: |
|--------->

>---------------------------------------------------------------------------
>
| (bcc: Michael Ebert/MUC/AMADEUS)
|

>---------------------------------------------------------------------------
>
>-------------------------------------------|
| |
| |
>-------------------------------------------|
|--------->
| |
|--------->

>---------------------------------------------------------------------------
>
|
|

>---------------------------------------------------------------------------
>
>-------------------------------------------|
| |
>-------------------------------------------|
|--------->
|Subject: |
|--------->

>---------------------------------------------------------------------------
>
|RUNSTAT and REBIND frequency.
|

>---------------------------------------------------------------------------
>
>-------------------------------------------|
| |
>-------------------------------------------|







I've got a client with a slew of COBOL programs with embedded SQL.
The application is fairly stable so that hardly any of the programs have
been rebound for years.
However, REORGs and RUNSTATS are run frequently, even though there isn't a
huge amount of activity against the tables.

Question one : Is it 100% true that the RUNSTATS won't have ANY effect until
REBIND ? Or is DB2 smart enough to peek at the RUNSTATS information on the
fly even for static SQL ?

Question two: We've updated the release of DB2 (to V5) at least once since
most of these programs were rebound. Going to V6 this year. Is there a
benefit to mass rebinding when a new release of DB2 goes in ?

Question three: What is the consensus (or range of opinions) on frequency
and methodology of REORG/RUNSTATS/REBIND ?

TIA
Brendan

----------------------------------------------------------------
The information transmitted is intended only for the person or entity to
which
it is addressed and may contain confidential and/or privileged material.
Any
review, retransmission, dissemination or other use of, or taking of any
action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. If you received this in error, please
contact the sender and delete the material from any computer.


















Robert Lawrence

Re: RUNSTAT and REBIND frequency.
(in response to RICK (SWBT) DAVIS)
Gina,
Are you "BIND"ing or "REBIND"ing the packages. If you "REBIND"ing the
packages the following should maintain your previous parameters with no
changes

REBIND PACKAGE(colid.program)

HTH
Bob Lawrence
DBA
Boscov's Dept Stores

-----Original Message-----
From: Fertaki Gina [SMTP:[login to unmask email]
Sent: Thursday, May 18, 2000 6:45 AM
To: [login to unmask email]
Subject: Re: RUNSTAT and REBIND frequency.

<< File: ATT00010.txt; charset = windows-1253 >>