Scripting/Automation for DBAs

Posted by

So if you’re in a multiple-dba situation, you’ve probably got one DBA who is the “scripting guru”. If you’re a lone DBA, you’ve either written or acquired from others the scripts for basic automation of the most simple tasks. Everyone has their shortcuts and tricks. I thought I’d write a general post on things you should be aware of when automating or scripting things for DB2.

What language to use?

Some DBAs come from an application development background, and they are at an advantage in this area. They are much more familiar with the fundamentals of programming, and may have an easier time picking a language they already know. Overall, just about any language will do. If you’re starting out from almost nothing, I wouldn’t use C or JAVA or any language that is that detailed – it’s just a level of control you don’t really need.

Some sort of shell is an obvious choice for those on Linux or Unix. I’m a fan of KSH, but that’s probably because I started out on AIX systems. The advantage of one of the “SH”es is that it flows natrually from just knowing how to administer a db2 database at the command line. These inculde:

  1. Inability to handle numbers with precision to the right of the decimal point
  2. Text parsing must be handled via sed and awk
  3. oddities in loops – sometimes you have to connect within a loop
When a scripting-specific sub-team or committee I was on when working in an organization with dozens of DBAs decided on Perl for scripting, I was mad. I was good with KSH, and now I had to learn something new and re-write everything in that new language. However, when I read “Learning Perl” and re-wrote my most complicated script, my professional life was changed forever. I am now a Perl addict and use it for just about everything. The things I love about Perl for scripting DB2 database administration are:
  1. Cross-platform – I write for unix/linux, but with an awareness of windows, and when I’ve had to alter something to work on Windows it’s often fast and easy
  2. Already there – If I stick to core modules, it’s already on Linux and Unix systems – nothing to install
  3. Text parsing capabilities are awesome and easy to use. Support for RegEx is built in
  4. It’s easy to build re-usable modules
  5. It’s easy to strip configuration parameters off into a separate script
One thing about Perl is that I do not end up using the DBI. Unfortunately some of the commands I need (runstats, reorg, etc) aren’t available through the DBI so I just end up doing everything through system commands, for better or worse.
There are plenty of other languages out there that are perfectly valid – I’ve integrated some KSH stuff into Jython for example. Just about any scripting language is fine. Performance is rarely an issue at the levels we’re talking about, so languages like C or JAVA are just overkill if you ask me – though you could certainly do it.

DB2’s Built-in automation

So in short, DB2’s built-in automation sucks. Am I saying this because I’ve been using DB2 since version 5, when there sure as heck was no built-in automation, and I just got used to my own scripts? Maybe, but I have either given various pieces of automation a chance, or seen a client do so, or read about the limitations and decided it wasn’t even worth a chance.

The best personality traits for a DBA are to be a detail-oriented control freak. And along those lines, I want to KNOW without even looking when my last runstats and backups were. With DB2’s automated facilities for these you specify a window where they are acceptable, and then DB2 decides if they need to be done or not. I’d hate to discover that a low rate of database change meant my most recent backup was two months old. And on runstats, I tend to believe that even a tiny proportional change can make a major difference if it doesn’t match the distribution of the existing data.

The only kind of reorgs DB2 automates are the offline ones, and since I can very rarely get a window for that, there’s no point in automating them – I’ll write my own to run them on my schedule.

And don’t even get me started on the Health Monitor. I’m continually shocked at it’s overhead from many sources from practical experience and anecdotal stories from the real world. One of the first things I do in setting up new databases is to turn the Health Monitor off. I have actually seen it cause an outage on databases I support within the last year. So out it goes, and my own scripts for monitoring too.

In short, avoid the DB2 automation.


One of the important things in writing scripts is to have your own standards. This goes from documentation to variable naming and so on. There are actually a number of sets of standards out there that you can choose from – either in part or in whole. A few examples from my own scripts include:

    1. All Perl scripts are named where specific_name is lower-case underscore delimited and defines the purpose of the script, prefferably in two words.
    2. All mature Perl scripts have a separate config file where variables that may change from implementation to implementation are stored. This script is named
    3. All scripts use variable names in lower case underscore delimited format (for example: $variable_name)
    4. All scripts list global variables towards the top
    5. All scripts list subroutines towards the top
    6. All scripts return a usage diagram with the syntax -h
    7. All scripts return a version number with the syntax -v
    8. All scripts start with a section of comments describing the purpose of the script – in this format for the base scripts(not configuration files:
#!/usr/bin/perl -w
 # Script Name :
 # Author : Ember Crooks
 # Company : Brulant
 # Date Written : July 2008
 # Script Version: -v
 # DB2 Version : 8.2
 # Purpose : To reset database monitors, sleep, then take snapshots
 # Usage : --help
 # Cfg file :
 # Change Log :
 # Date Chg by Ver. Description
 # -------- ------------ ------- ---------------------------------------
 # 07/17/09 Ember Crooks 1.0.1 Fixed file archiving and retention
 # 07/2010  Ember Crooks 1.1.0 ****CFG FILE CHANGE****
 #                              Added multiple database support
 # 08/2010  Ember Crooks 1.1.1 Bug Fix: One db failing should not stop
 #                             snapshots for other databases being
 #                             written.
 # 11/2010  Ember Crooks 1.1.2 Bug Fix: Archiving failing

Error Checking

One thing that is sorely lacking in many scripts I’ve seen is checking for errors. When you run a DB2 command at the command line, the OS basically considers it successful if it can find “db2” to pass the command to. All the SQL error messages are considered successful execution by the OS. So that means that you MUST parse the output of each and every command to ensure it returns what you expect it to. I have a collection of quick subroutines that I use that take the output and do things like ensure it starts with a specific string, or verify that it does NOT start with ‘SQL’. This is really one of the hallmarks of a successful scripter, and should be a requirement for every line in every script.

Reusable Code

It is useful to code a few things that you’ll use over and over again – like checking for errors or emailing output and so forth in such a way that you can at least copy and paste from one script to another. Eventually you can work this stuff into modules and such, but just writing it for easy copy and paste is a good start.

Sharing Scripts

I really wish I could just post every script I’ve written right here for my readers to share, but there are a couple of problems with that. One is liability – you use my script and my script destroys your database and you sue me. I’m just this person writing a technical blog in her spare time, and am not insured for that. The other is that technically, my scripts belong to the company I work for, and they sell them to their clients as a part of specific contracts.

The other thing to mention here is to be careful with the scripts you get from others. Every script has a set of assumptions that it’s running on, and not knowing those assumptions can be dangerous. You also need to be careful of actual maliciousness from things you get from unknown sources – you should understand what a script is doing before actually using it.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

One comment

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.