Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    May 17
    2000

    RUNSTAT and REBIND frequency.

    Brendan Friel
    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
    >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
    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
    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
    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
    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
    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
    [Boscovs Dept Stores LLC]
    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 >>




    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact