Giving Automatic Maintenance a Fair Try

You may also like...

6 Responses

  1. Isaac Munoz says:

    Thanks Ember for your article.

    Particularly we’ve been using auto_stats using the 24×7 online maint window for more than 2 years in all our production databases (+100) and it has never caused any interruptions or affectation to applications nor end-users (and we have some pretty oltp and mix-workload intensive databases). Auto_stats is throttled which means even a manual user runstats runs with higher priority than auto_stats. There is a devworks article which confirms my previous sentence by saying:

    “Throttling in automatic statistics collection is different than RUNSTATS throttling. Automatic statistics collection uses a fixed impact rate of 7 percent no matter how much UTIL_IMPACT_LIM (by default 10) is set. This way ensures automatic statistics collection does not have a significant impact on your workload even if it runs during production hours.”

    As for auto_reorg, all our SAP prod and non-prod db’s have used it +1 year (24×7 maint window too). It’s part of the SAP recommended settings and they have studied carefully the reorg policy so they have designed a policy for every DB2 version (they even discovered a bug during their DB2 fixpack testing process and got covered through the policy they designed). So we are using the same policy also in our non-SAP databases without any issues.

    Hope that helps

  2. Luke Numrych says:

    Ember – thanks for your view on automatic maintenance features. I had been planning to ask you about it during your visit in Milwaukee, but never got around to doing that.
    It seems that many application vendors recommend using the autonomic features of DB2; however, I feel that this advice is mostly aimed at small operators who do not necessarily have a DBA on staff and is of the “better something than nothing” type of advice. Therefore, I was somewhat surprised that enabling autonomic features was mentioned by Anthony Reina in his “Move Upgrade with DB2 V10.5” presentation at the same WDUG meeting at which you have presented – that presentation almost suggested that enabling these features was an integral part of upgrade to 10.5…

    As far as my own practice – I tried it, and I do not use it. I find that I prefer knowing and controlling what runs and when as much as I can.

  3. Carlos says:

    Any tips on setting up TSM for backups with automatic maintenance. I’ve yet to get the XML file properly formatted.

  4. Prudhviraj says:

    Do we need to create TOOLS DB before enabling Auto Maintenance on database? How do we enable Auto Maintenance in windows server, I have tried with DATA Studio, but for the first it ran successfully and after it failed, I was using Windows 2012 standard, One more issue is web console is supported in this version. Can you help in configuring this. Please

    • Ember Crooks says:

      I have never used the TOOLS DB, personally, for better or worse. Even when using auto maintenance. I also hate guis, even on Windows, where I prefer to use a powerShell command line. I therefore don’t have the experience with Data Studio to help with this. I would assume you could use the same command line steps for configuring it on Windows that you would on Linux and UNIX. I’ve seen several issues with automatic maintenance keeping up as much as I think it should, so don’t use it much, though I do leave both auto runstans and real-time stats enabled in BLU databases.

  1. March 24, 2015

    […] Giving Automatic Maintenance a Fair Try […]

Leave a Reply

Your email address will not be published. Required fields are marked *