Db2 Tools for z/OS

Expand all | Collapse all

Runstat profiles/ Rebinds question

  • 1.  Runstat profiles/ Rebinds question

    Posted Wed January 27, 2021 12:12 PM
    • I would like to hear from others who have already implemented the Cleanup of runstats,  using runstat profiles, and using autonomic statistics.  What function level did you  start using these  Autonomic Statistics ?
    • Function level 507 does some additional clean up, is it best to wait for that function level for Autonomic Statistics?.
    • Access paths could possibly change when using the profiles, I  would like to hear when and how you determine if rebinds are needed.
    • In Catalog Table, SYSIBM.SYSTABLES_PROFILES  ; there are 2 columns  PROFILE_UPDATE , PROFILE_USED?

                  Could these 2 column drive when to do rebinds? Will the rebinds be a part of Autonomic Statistics?


    All insights would be appreciated. I hope I am posting in the correct place.

    Regards,
    Carol

     

     



    ------------------------------
    carol goldberg
    ------------------------------


  • 2.  RE: Runstat profiles/ Rebinds question

    Posted Thu January 28, 2021 12:02 PM
    Edited by Paul Bradley Thu January 28, 2021 05:14 PM
    Hi Carol,

    Thank you for your questions.  However, these questions have to do with Db2 for zOS itself, rather than Db2 Tools.  Can you please post them in the Db2 for zOS community, at https://ibm.biz/BdfXYc (= https://community.ibm.com/community/user/hybriddatamanagement/communities/community-home/digestviewer?communitykey=621c2a2a-01f9-4b57-992f-36ed7432e3bb&tab=digestviewer).

    Kind regards,
    Irv Mandelbaum
    Db2 Tools Support

    Irv, Pat Bossman just posted a response.  Because it is also utilities-related, it's fine in either community. 

    ------------------------------
    Irv Mandelbaum
    ------------------------------



  • 3.  RE: Runstat profiles/ Rebinds question

    Posted Thu January 28, 2021 09:28 PM

    I appreciate your insights and will definitely post it in the Z/os DB2 community.

     

    Thanks, Carol

     

     

    Carol Goldberg

    Texas Comptroller of Public Accounts

    Software Development

    512-475-0612

    Carol.Goldberg@CPA.Texas.gov

    111 E 17th Street

    Austin, Texas 78774

     

     






  • 4.  RE: Runstat profiles/ Rebinds question

    Posted Thu January 28, 2021 09:36 PM

    I am also excited that these groups are available for questions.

     

    Many years ago Red books where the best gifts in the world. I would say communities is another step forward.

     

    Best regards, Carol

     

     

    Carol Goldberg

    Texas Comptroller of Public Accounts

    Software Development

    512-475-0612

    Carol.Goldberg@CPA.Texas.gov

    111 E 17th Street

    Austin, Texas 78774

     

     






  • 5.  RE: Runstat profiles/ Rebinds question

    Posted Thu January 28, 2021 12:02 PM

    Hi Carol,
    FYI - Terry Purcell and Michal Bialecki have a 1 hour webcast on this.  

    https://event.on24.com/eventRegistration/EventLobbyServlet?target=reg20.jsp&partnerref=Surekha&eventid=1896902&sessionid=1&key=ADCA038292471864B1621F4988BA3AF3&regTag=&V2=false&sourcepage=register

    I was a member of the optimizer development team when we implemented this.

    Function Level 507 makes it easier to clean up old statistics.  Prior to this, if you wanted to clean up, you would either need to delete the statistics yourself, or run a RUNSTATS collecting 0 frequencies/histograms.

    Regarding rebinds.
    - For dynamic SQL, no rebind is required.  On next full prepare, new statistics will be used.
    - For static SQL, the new statistics will be used at next rebind.

    The purpose of optimizer generated statistics profiles is to generate the statistics required to improve optimizers ability to estimate the cost of a query.  It would make sense to plan a REBIND after the new statistics policy has been implemented and statistics collected.

    We would also strongly encourage using plan management extended.  While collection of additional statistics solves many query performance problems, there is always a change of regression.  So performing the REBIND with PLAN MANAGEMENT EXTENDED, if there are any regressions, you can perform REBIND SWITCH to restore the prior execution runtime structures.

    Some customers take REBIND a step further - and perform REBIND with EXPLAIN ONLY, APCOMPARE, and then they'd analyze any access path changes.
    If there are a lot of them, this could be a significant effort.

    I will also note, this is one scenario where comparing the before/after cost estimate is not very useful.  The collection of additional statistics frequently results in a more accurate, and often a higher cost estimate for all access paths.  So it is entirely possible for the old access path to have a cost estimate of 10, the new access path to have a cost estimate of 100.  What's really happened is - the old access path estimate of 10 was low and is now higher than 100 (that is why it lost).  So in this excercise, you can look at the cost estimate, but just understand this possible dynamic.

    Rebinds are not part of automated statistics collection.  I would recommend identifying packages dependent on the tables with RUNSTATS changes.
    I would also plan to collect the runstats on related application objects in close proximity, and perform the rebinds after the set is done.

    Giving the optimizer great information on 1 out of 5 tables involved in a join is not a great state to be in.  So you want to collect on all 5, then do the rebind.

    Hopefully I've answered your questions.

    Best regards,
    Patrick Bossman



    ------------------------------
    Patrick Bossman
    ------------------------------



  • 6.  RE: Runstat profiles/ Rebinds question

    Posted Fri January 29, 2021 11:14 AM
    Hi Carol,

    I don't know which tools you have available, but with IBM Db2 Automation Tool you can set up a profile with
    • Objects: A, B, C, ...
    • Exception: SYSSTATFEEDBACK - RUNSTATS is recommended
    • Utilities: RUNSTATS USE PROFILE followed by REBIND (with extended plan management) of the packages that reference objects for which a RUNSTATS was run
    This would allow you to run a fully automated process.

    ------------------------------
    Jørn Thyssen
    ------------------------------



  • 7.  RE: Runstat profiles/ Rebinds question

    Posted Fri January 29, 2021 12:18 PM

    We have the BMC AMI tool set. We are not sure if they are supporting Runstats with profiles. We still need to verify this.

     

    One of other concerns is that using the BMC Change Manager tool , when reorgs are added to the workid , Sometimes REORGS FOR Indexes only will be generated. This may be  causing our stale or conflicting feed back reasons, over  a period of time.

    We have not proven that out yet, its only a suspicion.

     

    Carol

     

     

    Carol Goldberg

    Texas Comptroller of Public Accounts

    Software Development

    512-475-0612

    Carol.Goldberg@CPA.Texas.gov

    111 E 17th Street

    Austin, Texas 78774

     

     






  • 8.  RE: Runstat profiles/ Rebinds question

    Posted Mon February 01, 2021 05:55 AM
    Hi Carol,

    I can't help you with the BMC tools, but I know that sometimes the vendor tools also allow you to run the IBM utility instead of the vendor utility, which might be a solution if the vendor doesn't support runstats profiles.

    If a change is for an index only it might be sufficient to do a REORG INDEX or REBUILD INDEX whereas other changes will require a REORG of the table space. It might just be a setting. For example, IBM Db2 Object Comparison Tool have a setting that specifies if you never want to run a REORG as part of a change (with the assumption that your nightly object maintenance will schedule any required REORGs); if you want to resolve hard REORG or REBUILD pending; or you also want to run REORGs for tables in advisory REORG pending.

    ------------------------------
    Jørn Thyssen
    ------------------------------