I’m a control freak. I think that control freaks tend to make good DBAs as long as they don’t take it too far. My position for years has been that I would rather control my runstats, reorgs, and backups directly than trust DB2’s automatic facilities. But I also try to keep an open mind. That means that every so often I have to give the new stuff a chance. This blog entry is about me giving automatic maintenance a try. I am NOT recommending it yet, but here’s how I approached it and what I saw.
Environment
The environment I’m working with here is a brand new 10.5 (fixpack 5) database. It uses column-organization for most tables, but has a small subset of tables that must be row-organized. I still refuse to trust my backups to automation – I want them to run at a standard time each day. But for runstats and reorgs, I’m using this article to both look into what to do with the row-organized and the column-organized tables and how to set up controls around what times things happen. The environment I’m working on happens to use HADR.
Database Configuration Parameter Settings
Here’s the configuration I’m starting with for the Automatic maintenance parameters:
Automatic maintenance (AUTO_MAINT) = ON Automatic database backup (AUTO_DB_BACKUP) = OFF Automatic table maintenance (AUTO_TBL_MAINT) = ON Automatic runstats (AUTO_RUNSTATS) = ON Real-time statistics (AUTO_STMT_STATS) = ON Statistical views (AUTO_STATS_VIEWS) = OFF Automatic sampling (AUTO_SAMPLING) = OFF Automatic reorganization (AUTO_REORG) = ON
I think those are the defaults for a BLU database, though I may have already set AUTO_DB_BACKUP to OFF manually myself. I’m also going to turn auto_stats_views on, in case I create one of those. This is the syntax I use for that:
-bash-4.1$ db2 update db cfg for SAMPLE using AUTO_STATS_VIEWS ON DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
And now these configuration parameters look like this:
Automatic maintenance (AUTO_MAINT) = ON Automatic database backup (AUTO_DB_BACKUP) = OFF Automatic table maintenance (AUTO_TBL_MAINT) = ON Automatic runstats (AUTO_RUNSTATS) = ON Real-time statistics (AUTO_STMT_STATS) = ON Statistical views (AUTO_STATS_VIEWS) = ON Automatic sampling (AUTO_SAMPLING) = OFF Automatic reorganization (AUTO_REORG) = ON
Runstats on Row-Organized Tables
The first thing that I know I want to do is to set up profiles for runstats on my row-organized tables.
Setting Profiles
I want to use this syntax for all of my row organized tables in this database:
runstats on table <SCHEMA>.<TABLE> with distribution and detailed indexes all
If I had larger row-organized tables, I might consider using sampling, but my row-organized tables in this database happen to be on the smaller side.
There doesn’t seem to be a way to set a default syntax other than through creating profiles on individual tables. Would love to know in a comment below if I’m missing something there.
To set my profiles, I’m going to use a little scripting trick I like to use when I have to do the same thing for many objects. In this case, I have 106 tables where I want to set the profile identically, so first I create a list of those tables using:
db2 -x "select substr(tabschema,1,18) as tabschema , substr(tabname,1,40) as tabname from syscat.tables where tableorg='R' and tabschema not like ('SYS%') with ur" > tab.list
This creates a file called tab.list that has only the names of my tables – the -x on the command ensures that column headings and the summary row I have telling me how many rows are not returned as a part of the query.
Next, I loop through that list with a one-line shell script:
cat tab.list |while read s t; do db2 connect to bcudb; db2 -v "runstats on table $s.$t with distribution and detailed indexes all set profile"; db2 connect reset; done |tee stats.profile.out
Note that I could have used “set profile only” if I didn’t also want to actually do runstats on these tables, but in my case, I wanted to both do the runstats and set the profile. I then checked stats.profile.out for any failures with this quick grep:
cat stats.profile.out |grep SQL |grep -v DB20000I |grep -v "SQL authorization ID = DB2INST1"
Everything was successful.
Setting up a schedule
I don’t want my runstats to kick off any old time they feel like it. I want to restrict them to run between 1 am and 6 am daily. To do this, I need to set up an automatic maintenance policy. There are samples that I can start with in $HOME/sqllib/samples/automaintcfg.
I first made a copy of DB2MaintenanceWindowPolicySample.xml, renaming it and moving it to a working directory. I ensured my new file contained these lines:
<DB2MaintenanceWindows xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config"> <OnlineWindow Occurrence="During" startTime="01:00:00" duration="5"> <DaysOfWeek>All</DaysOfWeek> <DaysOfMonth>All</DaysOfMonth> <MonthsOfYear>All</MonthsOfYear> </OnlineWindow> </DB2MaintenanceWindows>
I don’t want to set an offline window at this time, because I don’t have one. The sample file has great information on how to configure different scenarios. While there is no option in the xml file to specify a database or different databases, I’m setting the policy with a command against a database, so I named the file with the database name in it and keep it in a place I can easily find it later so that I can change it if need be.
By default, the online window is 24/7.
Now that I have an XML file that will do what I want, I can set that as the policy using the AUTOMAINT_SET_POLICYFILE procedure, like this:
-bash-4.1$ db2 "call sysproc.automaint_set_policyfile( 'MAINTENANCE_WINDOW', 'DB2MaintenanceWindowPolicyBCUDB.xml' )" SQL1436N Automated maintenance policy configuration file named
Well, oops, that didn’t work so well. I learned that the xml file you want to use must be in $HOME/sqllib/tmp. ALSO, it must be readable by the fenced user ID. With the way I have it set up (with the fenced user id in the primary group of my instance id), this is what I had to do to make that work:
-bash-4.1$ cp DB2MaintenanceWindowPolicyBCUDB.xml $HOME/sqllib/tmp -bash-4.1$ chmod 740 $HOME/sqllib/tmp/DB2MaintenanceWindowPolicyBCUDB.xml
I was then able to successfully call the stored procedure:
-bash-4.1$ db2 "call sysproc.automaint_set_policyfile( 'MAINTENANCE_WINDOW', 'DB2MaintenanceWindowPolicyBCUDB.xml' )" Return Status = 0
When DB2 reads the file in, it is not depending on that file to exist forever. It is storing the information from the file in the database. You can use the AUTOMAINT_GET_POLICYFILE and AUTOMAINT_GET_POLICY stored procedures to pull that information back out. Remember that there is only one policy for each of the automatic maintenance categories, so it is best to first get the policy, change it, and then set it, so you do not accidentally overwrite what is already there.
Phew. Ok, that’s what I had to do to set things up for my row-organized tables. My column organized tables will also get runstats by default, and for the sake of trying it, I’m going to go with the defaults there and see what happens. And by see what happens, I mean I’ll be querying up a storm to see what DB2 is doing.
Reorgs
This database does not have an offline maintenance window. So I need to configure online reorgs to occur. Much like with runstats, I’m going to let the BLU tables go for a while and see if the the hype from IBM about just letting DB2 take care of it is really all that. The only reorgs there are for space reclaimation anyway. But for my row-organized tables, I want to make sure they’re taken care of.
Man, was I disappointed to discover that inplace/notruncate reorgs are STILL not supported as a part of automatic maintenance. This means that I cannot do table reorgs through DB2’s automation facilities … off to re-write my reorg script for yet another employer, I guess.
I’m trying to see if DB2 can at least manage my index reorgs for me online, though, with this syntax in my file:
<DB2AutoReorgPolicy xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config"> <ReorgOptions dictionaryOption="Keep" indexReorgMode="Online" useSystemTempTableSpace="false" /> <ReorgTableScope maxOfflineReorgTableSize="52"> <FilterClause /> </ReorgTableScope> </DB2AutoReorgPolicy>
And, of course, implementing that file with:
-bash-4.1$ cp DB2AutoReorgPolicyBCUDB.xml $HOME/sqllib/tmp -bash-4.1$ chmod 740 $HOME/sqllib/tmp/DB2AutoReorgPolicyBCUDB.xml -bash-4.1$ db2 "call sysproc.automaint_set_policyfile( 'AUTO_REORG', 'DB2AutoReorgPolicyBCUDB.xml' )" Return Status = 0
I think the combination of that and no offline reorg window defined will get me what I want on the index side anyway.
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
Regards
[…] Giving Automatic Maintenance a Fair Try […]
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.
Any tips on setting up TSM for backups with automatic maintenance. I’ve yet to get the XML file properly formatted.
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
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.