Configuring DB2 Automatic Maintenance using Data Studio
25 January 2014
Have you wanted to use the automatic maintenance feature of DB2 to run your backups, RUNSTATS and REORGS, but got discouraged because you didn’t want to write any XML to do it? Well I have good news. In this article I’ll show you how to use the Data Studio wizard to walk you through turning on DB2 Automatic Maintenance and configuring it to run your maintenance utilities according to your needs. In case you are not familiar with automatic maintenance it is a way to have DB2 run the BACKUP, REORG and RUNSTATS utilities as needed within windows you define without having to configure a job submission system such as Windows Tasks or crontab.
What is Automatic Maintenance?
Automatic Maintenance is a DB2 feature that allows you to have your database automatically run any combination of database backup, RUNSTATS and REORG. This way you do not have to use an external scheduler like Data Studio Web Console. This feature also attempts to only execute the utilities if they are needed. For example you can specify that a backup will only run after a certain amount of log space has been consumed. This means that you do not need to waste resources backing up a database that has had few changes. You can also specify how you want the utilities run. For example you can specify if a backup should be run online or off line and you can specify on which tables you want to collect statistics.
Since many organizations do not want maintenance utilities to run during peak processing times you can create a maintenance window for online activities such as RUNSTATS and an offline maintenance window for activities such as offline backups. If you run all utilities online and do not normally take your database off line, then you would not configure an offline window. DB2 can run utilities during these windows but it may not. For example, if you have specified that you only want to take backup after 1000 pages have been written to the logs and during the start of your nightly maintenance window only 500 pages have been written, then the backup will not run.
Configuring Automatic Maintenance
Although it is easier to use the Data Studio wizard, you can create the maintenance windows and configure the utilities for automatic maintenance at the command line using DB2 provided stored procedures. These stored procedures take an XML file as input to define parameters for the utilities. You also need to set some DBM CFG parameters to allow automatic maintenance to run. You can read all about this in the Configure Automatic Maintenance DevelopwerWorks article.
The good news is that the IBM Data Studio has task assistants (wizards) allow you to configure everything including the DBM parameters and maintenance windows without editing any XML yourself.
To configure automatic maintenance I like to start in the “Database Administration” perspective. If you are unfamiliar with perspectives in Data Studio, I recommend that you read my article called Data Studio Update Part 2: Perspectives, Views and Database Connections. To begin the process of configuring Automatic Maintenance, go to the Administration Explorer view and right click the database that interests you then choose “Setup and Configure” and then “Configure Automatic Maintenance” to begin the process. In figure 1 I am doing this for the GSDBTEST database.
Figure 1. Start Automatic Maintenance Task Assistant
That brings up the Configure Automatic Maintenance window where you can configure all options. You can figure the various aspects of the feature by clicking on the various tabs in the left including configuring the maintenance windows, and the policies of the three types of utilities it can run. The Task Assistant starts with the Options tab selected. In that tab you can see which automatic maintenance features are on or off as defined by the DB CFG. As you can see in figure 2 Automatic Maintenance is on, but some features like Automatic database backup is not. You can begin the process of turning features on or off by checking or un-checking the boxes. The changes do not take effect until you invoke them as discussed later. After I captured this screen shot, I checked all un-checked boxes because I wanted to allow invoke all maintenance types. Just checking the boxes does not change the database manager configurations. You must run them as shown in a later step. You can also use this panel to view which automatic maintenance parameters are set.
Figure 2. Set Database Configurations (DB CFG)
The next step would be to configure your desired maintenance windows. You can configure either the online or offline window or both. In figure 3 I show how I configured the Online Maintenance Window. I decided to have my window start at 3:00 PM and last for 8 hours every day. Be careful when choosing the duration. It needs to be long enough to run the maintenance processes. Otherwise, DB2 will not start your maintenance processes.
Figure 3. Configure Online Maintenance Window
Next you would configure the policies for each maintenance activity that interests you. You can do all three, but I’ll only show an example of the backup policy here. One good thing about using Data Studio instead of configuring the XML file yourself is that Data Studio knows which options are allowed. When I was writing this article I had not turned on archive logging for the GSDBTEST database. This means that online backups could not be done. So when I entered the Backup Policy tab, the option for Online at the bottom was greyed out and could not be chosen. I had to turn on archive logging to select Online a shown in Figure 4.
I then came back to this window where I chose the Backup criteria to balance recoverability and performance. Except for the “Customize the Criteria” choice, these buttons set different values for the “Time Since Last Backup” in hours and the number the “Log Space Consumed Since Last Backup” in 4k pages. Since I chose “Balance database recoverability with performance” Data Studio chose the values of 24 hours and 2562 pages respectively. The next time an online maintenance window rolls around, a backup will only be taken if either more than 24 hours has elapsed and more than 2562 pages have been written to the log. Both of these criteria need to be met for a backup to happen. I was able to determine these values by trying different option and then using the “Preview Command” link (circled in red) to look at the code being generated.
Figure 4. Configure Automatic Backup
You can review the commands to be executed by clicking the Preview Command link at any time, buy you should definitely look at it before running the commands. I show the link circled in red at the top of Figure 4. The changes on all of the preceding windows are captured as you can see at the bottom of Figure 5.
Figure 5. Preview Command
At this point you can either run the commands or save the script and run it from any CLP window connected to the database. Depending on the operating system of the database server and your Data Studio connection configuration, Data Studio may try to make a connection to the DAS or connect using SSH. If these are not configured the run button will fail. If that happens the easiest thing to do is to copy the script and run it yourself from a CLP window on your workstation on the database server itself.
Once you have configured automatic maintenance, you will want to monitor the maintenance activities to verify that they are happening as they should. There are a few ways to do this and I will summarize them here.
There are other interesting aspects of Automatic Maintenance configuration and monitoring that I have not shown here. Please take a few minutes to click around and see what is available. Please add any features that you really like to my Facebook Page or my db2Dean and Friends Community and help the extended community.