DB2 - L

  • 1.  Db2 z/OS: RUNSTATS Profiles and SYSSTATFEEDBACK

    Posted Oct 14, 2021 11:14 AM

    We are looking into the possibility of starting to use RUNSTATS profiles in our subsystems.  We were surprised to find hundreds (in some subsystems) and thousands (in other subsystems) of RUNSTATS profiles already existing in the SYSTABLES_PROFILES catalog table, where RUNSTATS profiles are stored.  It turns out that when we went to Db2 v12, the new STATFDBK_PROFILE zparm was automatically creating and updating these profiles whenever SYSSTATFEEDBACK was being updated.

    I have a couple of questions:

    1) Are you routinely using RUNSTATS profiles in your shop?

    2) If so, how has this new behavior that comes with Db2 v12 (the default for the STATFDBK_PROFILE zparm is "YES") affected your use of RUNSTATS profiles?  Have you turned the zparm off so that you can have more control of the contents of your profiles?  Or are you good with collecting more and more statistics based off of what the feedback table is recommending?

    Thanks!



    ------------------------------
    Bill Gallagher
    Senior Systems Engineer, DBA
    ------------------------------


  • 2.  RE: Db2 z/OS: RUNSTATS Profiles and SYSSTATFEEDBACK

    Posted Oct 14, 2021 12:47 PM
    I hated it... 1000's of pointless profiles... basically you must delete all the old rubbish and start again.. quite why db2 had this switched on as default is still a major puzzle for me! What I do is "buffer" the updates- if a recommendation arrives again and again over a period of time, then, and only then, does it get added to the Runstats.

    Roy Boxwell
    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-
    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    http://www.seg.de

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich 





  • 3.  RE: Db2 z/OS: RUNSTATS Profiles and SYSSTATFEEDBACK

    Posted Oct 15, 2021 02:34 AM
    In fact here's my blog about it way back in 2017!!

    https://www.seg.de/2017-01-db2-12-technical-overview-feature/

    Scroll on down to "Autonomic Statistics with PROFILEs"
    We had various customers with over 4000 COLGROUPs due to this "default on" malarkey!


    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 4.  RE: Db2 z/OS: RUNSTATS Profiles and SYSSTATFEEDBACK

    Posted Oct 15, 2021 08:35 AM
    Love it, use it regularly in all our runstats. In fact, we had an issue several months ago where a dynamic query was performing very badly. I mean really bad. It wasn't using the "better" index but using the "not so good" index. We fought with this for an entire day. I remembered the new runstats profiles and ran runstats  on the tablespace using the USE PROFILE parameter then invalidated the cache. Like magic it started using the "better" index.

    ------------------------------
    RussellPetersCentral Technology Services
    ------------------------------