Giving Automatic Maintenance a Fair Try

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.