Database administration for fun and profit

Automatic startup and shutdown of DB2 instances on Linux

The default approach to enabling automatic startup of DB2 instances on Linux operating systems relies on the DB2 Fault Monitor. During the installation a line like this:

fmc:2345:respawn:/opt/IBM/db2/V8.1/bin/db2fmcd #DB2 Fault Monitor Coordinator

is added to the /etc/inittab file. The fault monitor then takes care of starting and restarting the instances which were enabled for autostart by db2iauto on.

There seem to be several problems with that approach. Firstly, the use of /etc/inittab to start programs is deprecated in the modern Linux versions. Secondly, one may want to have a more flexible tool to manage the lifecycle of DB2 instances on the system, such as that provided by the System V init scripts.

I’ve created a couple of scripts — one for the Red Hat Linux, another for SuSE — that can be used in place of the default startup control. They can be downloaded from GitHub.

The installation is easy: drop the appropriate for your server version of the script into /etc/init.d, naming it “db2”. Change the file permissions to allow execution. Run chkconfig --add db2 to enable it.

At this point the scripts respond to three commands: start, stop, and status.

The start command causes the script to check all instances listed in the DB2 global registry file and start those for which the startatboot flag is set to “1”.

The stop command, on the other hand, will stop all running DB2 instances, regardless of their startatboot flag value.

To modify the flag you can use the command:

db2greg -updinstrec instancename=db2inst1!startatboot=0

Obvisously, you’ll need to replace “db2inst1” with your instance name. Set the flag to “1” to enable autostart; set it to “0” to disable it.

For more information on the global registry file and the db2greg utility check the manual, as always. Running db2greg -h also provides much useful information.

Top 5 SQL statements

Or may be top 10. Or 3. Whatever the number, we are often looking for the worst offenders kicking up the server’s CPU utilisation or I/O wait time to the skies. DB2 built-in snapshot functions are a great help. Run “select * from table (snapshot_dyn_sql('YOURDB', -1)) t order by rows_read desc fetch first 5 rows only” and you will get a list of the queries retrieving the most data. The only thing is, snapshot functions provide, well, snapshots of the DB2 monitor data and are not by themselves suitable for the collection of historical data. Monitor counters can be reset without your knowledge, for example when the database is deactivated or when the SQL statement expires from the package cache.

To ensure continuity of the data collected by snapshot functions we can quickly create a table where we will store historical snapshot information:

create table DYNSQLDATA as (select * from table (snapshot_dyn_sql('YOURDB', -1)) t) with no data

We will then insert output of the snapshot function into this table at regular intervals:

insert into DYNSQLDATA select t.* from table (snapshot_dyn_sql('YOURDB', -1)) t

Now we can easily determine which statements demand most resources and analyze their historical performance. Read More

Automatic monitoring of the SSL certificate expiration date

This is not really about database administration, but one of the problems I often face is monitoring of the expiration of SSL certificates on my clients’ web servers. It usually takes some time to renew a certificate, and it helps to know in advance that I need to get the process started.Here’s a little script that checks the certificate on a given web server and sends a reminder if it is about to expire:

#!/bin/bash# checks the ssl certificate expiration date of a given host
# Usage: ./checksslcert.sh <hostname> [<port>]
# Port defaults to 443 if not specified
test -z "$1" && echo "Usage: $0 <hostname> [<port>]" && exit 0
tempstr=$(openssl s_client -connect $1:${2:-443} 2>/dev/null >$0.log)
test $? -gt 0 && echo "Error accessing SSL certificate on $1" && exit 1
exptime=$(date -d"${tempstr#*=}" +"%s")
expdays=$(((${exptime} - $(date +"%s"))/84400))
echo "SSL certificate on $1 expires in $expdays days"
test $expdays -lt 45 && echo "Do something!" | mailx -s "SSL certificate on $1 expires in $expdays days" admin@domain.com

Run it daily by cron and you will never miss the expiration date again. The script needs the GNU date utility and openssl to be installed. It has been tested under bash, but you can easily modify it to run under other shells.

Poor man’s logrotate for Windows

Have you ever been bothered by the db2diag.log growing out of control? I have. While on Linux and Unix you have nice tools like logrotate and its analogs written in a number of script languages, no such luck if you run DB2 on Windows. There is no Windows port of logrotate, and your MSCE colleagues don’t always look favourably at you trying to install ActivePerl or Cygwin on a production server.

Having suffered enough from Notepad not being able to open a 300 megabyte db2diag.log, I have written a Windows command script to rotate the DB2 diagnostic log file. It uses only built-in Windows commands and therefore can run on any out of the box Windows 2000, Windows XP, or Windows 2003 system. By the way, it should work for any log files (with some limitations), not necessarily those generated by DB2.

Read More

HTML documentation for a database schema

table Some time ago I was looking for a “quick and dirty” way of generating HTML documentation of one of my clients’ database schema. I didn’t need an ER diagram – just a list of tables to insert into a word document. At that time TOAD for DB2 did not exist, so I could not use it for my purpose.

Eventually, I ended up creating a simple solution for that simple problem, using a SQL script fetching information from the database catalog and an XSL template to transform its output into HTML, similar to this.
Read More