Skripting DB2 Automatic Maintenance

Last Update:

Author: Christoph Stoettner
Read in about 3 min · 433 words

Fountain pen and a notebook

Photo by Aaron Burden | Unsplash

After skripting jdbc and j2ee on WebSphere i was interested to fasten the DB2 configuration after a Connections installation.

There are two ways to get valid source xml-Files for automatic maintenance in DB2. I use automatic maintenance, because i can set a maximum backup time and how many dumps of one database should be stored. DB2 deletes old backups (which i have in a filebackup each night) automatically.

You can’t script this settings directly, IBM provides two system calls for this: sysproc.automaint_set_policyfile and sysproc.automaint_set_policy.

I use the file variant here, because i think it easier to handle files than blob content.

Sample Files

In /opt/ibm/db2/v9.7/samples/automaintcfg you can find example files for all settings within automatic maintenance.

You can edit them and then put them to INSTANCE_ROOT/sqllib/tmp (on Linux|AIX /home/db2inst1/sqllib/tmp).

The xml-Files must have read/write rights for db2fenc1 user or db2iadm group!

Export settings from db2

You can setup one of the connections databases for automatic maintenance (timeperiods, backup path, and so on). These settings can be done through db2cc or DB2 Data Studio. After providing all needed parameters you can export these settings through db2 commandline interface.

 connect to homepage
 call sysproc.automaint_get_policyfile('AUTO_BACKUP','DB2AutoBackupPolicy.xml')
 call sysproc.automaint_get_policyfile('AUTO_RUNSTATS','DB2AutoRunStatsPolicy.xml')
 call sysproc.automaint_get_policyfile('AUTO_REORG','DB2AutoReorgPolicy.xml')
 call sysproc.automaint_get_policyfile('MAINTENANCE_WINDOW','DB2AutoMaintenancePolicy.xml')

The files get automatically stored to INSTANCE_ROOT/sqllib/tmp.

Set automatic maintenance through script

I created a script with following code (e.g. setbackup.sql):

 connect to peopledb;
 update db cfg using AUTO_MAINT ON;
 update db cfg using AUTO_DB_BACKUP ON;
 update db cfg using AUTO_TBL_MAINT ON;
 update db cfg using AUTO_RUNSTATS ON;
 update db cfg using AUTO_STATS_PROF ON;
 update db cfg using AUTO_PROF_UPD ON;
 update db cfg using AUTO_REORG ON;
 call sysproc.automaint_set_policyfile('AUTO_BACKUP','DB2AutoBackupPolicy.xml');
 call sysproc.automaint_set_policyfile('AUTO_RUNSTATS','DB2AutoRunStatsPolicy.xml');
 call sysproc.automaint_set_policyfile('AUTO_REORG','DB2AutoReorgPolicy.xml');
 call sysproc.automaint_set_policyfile('MAINTENANCE_WINDOW','DB2AutoMaintenancePolicy.xml');

You have to copy this for all databases where you want to configure automatic maintenance!

After a full IBM Connections installation this are: blogs, cognos, dogear, files, forum, homepage, metrics, mobile, opnact, peopledb, sncomm, wikis.

So you have to copy the text block 10 times and change the “connect to” line.

Now you can apply the settings with db2 -tvf setbackup.sql and you’re done. Restart db2admin or the database server after these settings.


Automatic maintenance needs a TOOLSDB! Without it the configured tasks will not start. When you forgot to enable TOOLSDB on the DB2 setup, you can create one with:

 db2 create tools catalog cc create new database toolsdb

You can check if a TOOLSDB is present and configured with: db2 get admin configuration

Last lines should be:

 Tools Catalog Database              (TOOLSCAT_DB) = TOOLSDB
 Tools Catalog Database Instance     (TOOLSCAT_INST) = db2inst1
 Tools Catalog Database Schema       (TOOLSCAT_SCHEMA) = CC

Example SQL File (for all Connections Databases)


Add a comment
There was an error sending your comment, please try again.
Thank you!
Your comment has been submitted and will be published once it has been approved.

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

Suggested Reading
Aaron Burden: Fountain pen and a notebook
Today i spoke at Social Connections V User Group Meeting in Zurich. What should i say. It was a pleasure and i enjoyed it very much. Hope we can discuss more scripts the next weeks. Session Slides Here you can watch the session slides: Saving my time using scripts #soccnx #soccnxv Scripts Download You can download all scripts without warranty and on your own risk on: Please download the master branch, develop and bugfix can contain not ready scripts. Slidedownload soccnx More Slides on Update 2022: Slideshare is requesting a paid scibd account now (30 day evaluation possible). I have removed all my slides from Slideshare, you can find them under [https://stoeps.
Last Update:
Read in about 1 min
Aaron Burden: Fountain pen and a notebook

Today i read a question in the IBM Connections Forum about setting the EMPLOYEE_EXTENDED role to all users in a Connections deployment.

Last Update:
Read in about 1 min
Aaron Burden: Fountain pen and a notebook
Missing command history on Linux is a little problem when using command line utilities like wsadmin, db2, sqlplus and so on. I found a solution for this today. You can use rlwrap to get command history for all applications on the console and it is possible to recall and edit the commands. Rlwrap uses readline. Installation on CentOS: yum install readline-static gcc make tar -xvzf rlwrap-0.41.tar.gz cd rlwrap-0.41 ./configure make make install Call rlwrap with wsadmin: rlwrap -r /opt/IBM/WebSphere/AppServer/profiles/Dmgr01/bin/ -lang jython -username wasadmin -password password rlwrap and db2 rlwrap -r db2 Use rlwrap everytime with alias vim ~/.bash_profile export WAS_HOME=/opt/IBM/WebSphere/AppServer export DMGR=Dmgr01 alias db2='rlwrap -r db2' alias wsadmin='cd $WAS_HOME/profiles/$DMGR/bin;rlwrap -r .
Last Update:
Read in about 1 min