Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA
01 November 2008
A critical part of any database disaster recovery plan is to make sure that the environment surrounding the database is the way it was before the disaster in order to provide the same level of performance and level of service to users after a recovery. This article explains a process to backup UNIX and DB2 environment information that contains important information that can be used during a disaster recovery or operational recovery. An example of a KORN shell script is provided to help you keep this information so it will be available when you need it.
Introduction
If you ever need to recover a DB2 environment from a disaster or serious operational system problem and want the database to perform like it did before the recovery, information about the environment is the fastest, most thorough way to accomplish your goal. This information is critical to your success in addition to restoring DB2 database backups that are a part of your disaster recovery plan.
This article describes the information you need to have available and a script that will help accomplish this and tells you step-by-step, how to use them.
Pros and cons
The advantage of having UNIX and DB2 environment information backed up and stored off site with your database backups is that you will have the information you need to verify that the environment and the database are completely restored after a disaster and that the database will perform and provide the same level of service after the recovery.
If you do not have UNIX and DB2 environment information backed up and stored off site with your database backups, then you risk having performance problems and unreliable service from a database after a recovery from a disaster.
Description
Here is a list of information that is helpful to have in addition to a DB2 database backup that can be used to verify an environment has been recovered or rebuilt in the same way before you restore your database backup after a disaster or severe operational problem.
oslevel
This information can be used to verify that the same operating system level is available in support of the recovery.
lslpp -h | grep -vp bos | grep -vp device | grep -vp X11
This above command backs up a list of all of the software installed on the server and can be used to verify that the operating system and programs installed on the server are as they were before the disaster. (This can be accomplished with different commands on different operating systems.)
db2level
This information can be used to verify that the same DB2 level is available in support of a recovery.
cat /etc/services
A backup of this can be used to ensure that the TCP/IP ports used by a DB2 instance are the same as before. Ports used to support partitioned database environments and tools like the high performance unload can be confirmed as well.
df –g
A backup of this UNIX command can be used to verify the file systems, space allocations and mount point paths restored or created after a disaster. This must be verified before you restore a database backup to ensure that the restore will be successful and that the database will fit in the file systems on the server.
mount A backup of this information can be used to verify that file systems use the same mount options that were used before a database is restored. You can verify that jfs, jfs2, cio, rbr, rbw and other mount options match so that you will experience similar operating system I/O performance after recovering from a disaster.
id instance Use information backed up by this command to verify that the instance id is set up with the correct operating system groups.
ulimit –a
Output from this command run for DB2 instance id’s can be used to verify that the id is set up the same way as before.
crontab –l
List output from the crontab command will save a UNIX crontab schedule if you use that to run jobs under a DB2 instance id. If you use a different scheduler to run jobs then you should have those schedules backed up as part of your disaster recovery plan.
cp .profile
A copy of the .profile file for a DB2 instance id’s can be copied for reuse after a disaster.
userprofile A copy of the DB2 userprofile for a DB2 instance id can be copied to reuse after a disaster if you customize your db2profile environment with environment variables in the sqllib userprofile file that is shipped with DB2.
cp db2.conf
A copy of the Veritas NETBACKUP db2.conf backup configuration file for a DB2 instance can be restored to reestablish your DB2 backup and restore environment. (Change this to commands used to backup TSM, HP Data Protector or other types of backup configuration information depending on the software you use for backups.)
db2licm -l This information can be used to verify that you have applied the correct license information for a DB2 instance in the event that you have to recreate an instance after a disaster.
db2 get admin cfg
The output from this DB2 command can be used to reestablish the DB2 administration configuration during a restore.
db2cfexp
This DB2 command will export configuration information that can be imported to completely restore DB2 environment information like a DB2 database directory, a DB2 node directory and other DB2 configuration information. The db2cfimp filename command can be used to import this information and help recover a large part of a DB2 environment. This should usually be run before restoring a database backup.
db2 list node directory The output from this DB2 command can be used to verify that a DB2 node directory is like it was before a problem requiring a restore or rebuild.
db2 list database directory
The output from this DB2 command can be used to verify the DB2 database directory.
db2set -all
Use this DB2 command information to verify the DB2 registry settings for a DB2 instance as part of your recovery steps.
db2 get dbm cfg
Use this output to verify that the DB2 instance configuration is what it was before the operational problem or disaster.
db2 get db cfg for DATABASE_NAME
A backup of the output from this command can be used to verify that DB2 database configurations are restored correctly. (db2_all “db2 get db cfg for DATABASE_NAME” may be used for partitioned databases.)
Environment
The script in this article was tested on a server running the AIX operating system and DB2 versions 8, 9.1 and 9.5. The KORN shell script should work on any server that has a UNIX-like operating system that supports these types of scripts. (Linux and Linux/390 come to mind.) This script is designed to run in a native UNIX environment and is not intended to run under the DB2 Script Center Client Window. This script should be run under a DB2 instance id or scheduled to run on a daily basis for each DB2 instance id. This script is designed to run against stand alone DB2 environments or DB2 partitioned database environments though it must be run on each server that is part of a DB2 partitioned environment if you want to have complete coverage.
Before you get started, a couple of words of caution:
- Practice running the script and verify the results.
- Make sure you have a DB2 database backup solution implemented in addition to this DB2 environment backup solution. The combination of the two will help provide what you need to recover from an operational problem or a disaster.
- Storing DB2 database backups and DB2 environment backup information off site can help ensure that the information you need will be available after a disaster.
CRONTAB schedule example
00 00 * * * /db2util/scripts/db2backup_environment.ksh db2inst1 >/dev/null 2>&1
The script
#!/bin/ksh
#-----------------------------------------------------------------------
# Program : db2backup_environment.ksh
# Description : This script backs up DB2 environment information
# : that is important to have for disaster recovery
# : purposes.
# Author : Mark Mulligan
# Date : 05/18/2001
#-----------------------------------------------------------------------
ScriptVer='05/18/2001'
ScriptName='db2backup_environment'
Server=$(hostname)
if [ "$1" = "" ]
then echo ""
echo "Parm 1 Instance (required)."
echo "Parm 2 Script Output Directory Path (optional) Defaults to /dbawork/reports"
echo "Parm 3 Disaster Recovery Directory Path (optional) Defaults to /dbawork/reports/DISASTER_RECOVERY_INFO"
echo ""
return 8
else Instance=`echo $1 | tr '[A-Z]' '[a-z]'`
fi
if [ "$2" = "" ]
then OutputDir="/dbawork/reports"
else OutputDir="${2}"
fi
if [ -d $OutputDir ]
then :
else echo "OutputDir $OutputDir must exist before you run this script."
echo "Run mkdir commands to create this directory path."
return 8
fi
if [ "$3" = "" ]
then DisasterRecoveryPath="/dbawork/reports/DISASTER_RECOVERY_INFO"
else OutputDir="${3}"
fi
if [ -d $DisasterRecoveryPath ]
then :
else echo "DisasterRecoveryPath $DisasterRecoveryPath must exist before you run this script."
echo "Run mkdir commands to create this directory path."
return 8
fi
. $HOME/sqllib/db2profile
Datetime="`date +%Y%m%d%H%M%S`"
FileName=${OutputDir}/${ScriptName}_${Server}_${Instance}_${Datetime}
OutputFile=${FileName}.txt
DisasterRecoveryFile=${DisasterRecoveryPath}/${ScriptName}_${Server}_${Instance}_${Datetime}
echo "$ScriptName Vers: $ScriptVer started on " `uname -n` on `date` | tee $OutputFile
# backup the oslevel
echo "oslevel > ${DisasterRecoveryFile}.oslevel.txt" | tee -a $OutputFile
oslevel > ${DisasterRecoveryFile}.oslevel.txt
# list software installed on the server
echo "lslpp -h | grep -vp bos | grep -vp device | grep -vp X11 > ${DisasterRecoveryFile}.lslpp.txt" | tee -a $OutputFile
lslpp -h | grep -vp bos | grep -vp device | grep -vp X11 > ${DisasterRecoveryFile}.lslpp.txt
# backup db2level
echo "db2level > ${DisasterRecoveryFile}.db2level.txt" | tee -a $OutputFile
db2level > ${DisasterRecoveryFile}.db2level.txt
# copy /etc/services file that contains DB2 TCP/IP information
if [ -f /etc/services ]
then echo "cp /etc/services ${DisasterRecoveryFile}.services" | tee -a $OutputFile
cp /etc/services ${DisasterRecoveryFile}.services
fi
# display file system information
echo "df -g > ${DisasterRecoveryFile}.df_g.txt" | tee -a $OutputFile
df -g > ${DisasterRecoveryFile}.df_g.txt
# backup mount information
echo "mount > ${DisasterRecoveryFile}.mount.txt" | tee -a $OutputFile
mount > ${DisasterRecoveryFile}.mount.txt
# backup id information for the instance id
echo "id $Instance > ${DisasterRecoveryFile}.id.txt" | tee -a $OutputFile
id $Instance > ${DisasterRecoveryFile}.id.txt
# backup ulimit settings for the instance id
echo "ulimit -a > ${DisasterRecoveryFile}.ulimit.txt" | tee -a $OutputFile
ulimit -a > ${DisasterRecoveryFile}.ulimit.txt
# backup crontab
echo "crontab -l > ${DisasterRecoveryFile}.crontab.txt" | tee -a $OutputFile
crontab -l > ${DisasterRecoveryFile}.crontab.txt
# copy instance profile
if [ -f $HOME/.profile ]
then echo "cp $HOME/.profile ${DisasterRecoveryFile}.profile" | tee -a $OutputFile
cp $HOME/.profile ${DisasterRecoveryFile}.profile
fi
# copy userprofile
if [ -f $HOME/sqllib/userprofile ]
then echo "cp $HOME/sqllib/userprofile ${DisasterRecoveryFile}.userprofile" | tee -a $OutputFile
cp $HOME/sqllib/userprofile ${DisasterRecoveryFile}.userprofile
fi
# copy Netbackup configuration
if [ -f $HOME/db2.conf ]
then echo "cp $HOME/db2.conf ${DisasterRecoveryFile}.db2.conf" | tee -a $OutputFile
cp $HOME/db2.conf ${DisasterRecoveryFile}.db2.conf
fi
# backup DB2 license information
echo "db2licm -l > ${DisasterRecoveryFile}.license.information" | tee -a $OutputFile
db2licm -l > ${DisasterRecoveryFile}.db2licm.license.information
cp $HOME/sqllib/adm/*.lic $DisasterRecoveryPath/. >/dev/null 2>&1
# backup DB2 administration server configuration.
echo "db2 get admin cfg > ${DisasterRecoveryFile}.admin.cfg" | tee -a $OutputFile
db2 get admin cfg > ${DisasterRecoveryFile}.admin.cfg
# export database directory, node directory, instance configuration and other DB2 environment information
echo "db2cfexp ${DisasterRecoveryFile}_db2cfexp.bak backup" | tee -a $OutputFile
db2cfexp ${DisasterRecoveryFile}_db2cfexp.bak backup
echo "db2 list node directory > ${DisasterRecoveryFile}.db2.list.node.directory" | tee -a $OutputFile
db2 list node directory > ${DisasterRecoveryFile}.db2.list.node.directory
echo "db2 list database directory > ${DisasterRecoveryFile}.db2.list.database.directory" | tee -a $OutputFile
db2 list database directory > ${DisasterRecoveryFile}.db2.list.database.directory
# list DB2 environment registry variable settings
echo "db2set -all > ${DisasterRecoveryFile}.db2set" | tee -a $OutputFile
db2set -all > ${DisasterRecoveryFile}.db2set
# save off instance configuration for comparison purposes even though this can be imported
# using db2cfimp with the db2cfexp.bak file created above
echo "db2 get dbm cfg > ${DisasterRecoveryFile}.dbm.cfg" | tee -a $OutputFile
db2 get dbm cfg > ${DisasterRecoveryFile}.dbm.cfg
# copy db2nodes.cfg if it exists
if [ -f $HOME/sqllib/db2nodes.cfg ]
then echo "cp $HOME/sqllib/db2nodes.cfg ${DisasterRecoveryFile}.db2nodes.cfg" | tee -a $OutputFile
cp $HOME/sqllib/db2nodes.cfg ${DisasterRecoveryFile}.db2nodes.cfg
fi
db2 list database directory > $FileName.dat
cat $FileName.dat | while read column1 column2 column3 column4 column5 column6
do
if [[ "$column1" = "Database" && "$column2" = "alias" ]]
then export Database=$column4
fi
if [[ "$column1" = "Directory" && "$column5" = "Indirect" ]]
then if [ -f $HOME/sqllib/db2nodes.cfg ]
then cat $HOME/sqllib/db2nodes.cfg | while read NodeNumber HostName LogicalPort NetName
do
echo "PARTITION ${NodeNumber}: db2 -v get db cfg for ${Database} show detail
>> ${DisasterRecoveryFile}_${Database}.db.cfg" | tee -a $OutputFile
echo "DB2NODE=$NodeNumber" >> ${DisasterRecoveryFile}_${Database}.db.cfg
export DB2NODE=$NodeNumber
db2 -v terminate >> ${DisasterRecoveryFile}_${Database}.db.cfg
db2 -v connect to $Database >> ${DisasterRecoveryFile}_${Database}.db.cfg
db2 -v get db cfg for ${Database} show detail >> ${DisasterRecoveryFile}_${Database}.db.cfg
done
else echo "db2 -v get db cfg for ${Database} show detail >> ${DisasterRecoveryFile}_${Database}.db.cfg" | tee -a $OutputFile
db2 -v terminate > ${DisasterRecoveryFile}_${Database}.db.cfg
db2 -v connect to $Database > ${DisasterRecoveryFile}_${Database}.db.cfg
db2 -v get db cfg for ${Database} show detail > ${DisasterRecoveryFile}_${Database}.db.cfg
fi
fi
done
echo "$ScriptName Vers: $ScriptVer ended on " `uname -n` on `date` | tee -a $OutputFile
echo "Output located in $OutputFile" | tee -a $OutputFile
Example
If you run the script in foreground without passing it any parameters, it will echo display the parameters that the script uses. The script output directory path and the disaster recovery directory path must exist before the script will run the commands to create information about your DB2 environment that can be used during a disaster recovery or recovery from a severe operational problem.
db2backup_environment.ksh
Parm 1 Instance (required). Parm 2 Script Output Directory Path (optional) Defaults to /dbawork/reports Parm 3 Disaster Recovery Directory Path (optional) Defaults to /dbawork/reports/DISASTER_RECOVERY_INFO
When you run the script under a DB2 instance id in foreground and pass it the DB2 instance id you should see output displayed that is similar to the example shown below.
/db2util/scripts/db2backup_environment.ksh db2inst1 db2backup_environment Vers: 05/18/2001 started on edwasu on Fri Nov 14 08:57:39 EST 2008 oslevel > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.oslevel.txt lslpp -h | grep -vp bos | grep -vp device | grep -vp X11 > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.lslpp.txt db2level > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2level.txt cp /etc/services /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.services df -g > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.df_g.txt mount > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.mount.txt id db2inst1 > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.id.txt ulimit -a > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.ulimit.txt crontab -l > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.crontab.txt cp /db2home/db2inst1/.profile /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.profile cp /db2home/db2inst1/sqllib/userprofile /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.userprofile cp /db2home/db2inst1/db2.conf /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.conf db2licm -l > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.license.information db2 get admin cfg > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.admin.cfg db2cfexp /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_db2cfexp.bak backup db2 list node directory > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.list.node.directory db2 list database directory > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.list.database.directory db2set -all > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2set db2 get dbm cfg > /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.dbm.cfg cp /db2home/db2inst1/sqllib/db2nodes.cfg /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2nodes.cfg PARTITION 0: db2 -v get db cfg for PROD01 show detail >> /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg PARTITION 1: db2 -v get db cfg for PROD01 show detail >> /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg PARTITION 2: db2 -v get db cfg for PROD01 show detail >> /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg PARTITION 3: db2 -v get db cfg for PROD01 show detail >> /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg PARTITION 4: db2 -v get db cfg for PROD01 show detail >> /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg db2backup_environment Vers: 05/18/2001 ended on edwasu on Fri Nov 14 08:57:48 EST 2008 Output located in /dbawork/reports/db2backup_environment_edwasu_db2inst1_20081114085739.txt
List Example
The output created by this script can be listed for a particular day to see the DB2 backup environment information as of that day. Cut and paste the file to a cat command to see the contents.
ls /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739* /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.admin.cfg /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.crontab.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.conf /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.list.database.directory /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2.list.node.directory /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2level.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2licm.license.information /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2nodes.cfg /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.db2set /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.dbm.cfg /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.df_g.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.id.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.lslpp.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.mount.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.oslevel.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.profile /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.services /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.ulimit.txt /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739.userprofile /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_PROD01.db.cfg /dbawork/reports/DISASTER_RECOVERY_INFO/db2backup_environment_edwasu_db2inst1_20081114085739_db2cfexp.bak
Conclusion
A DB2 Backup Environment Script can be an integral part of your disaster recover plan to protect the valuable information assets in the databases you support. It can help give you information about the operating system, software installed on that system and database environments that you can use to verify that the restored database and environment will operate in the same way that it did before the disaster.
If you have any questions related to this article, feel free to contact me, Mark Mulligan, at mark.mulligan.sr@sbcglobal.net and I will be glad to help in any way that I am able
About the author
Mark Mulligan is a DBA living in the Dallas-Fort Worth area of Texas. He has a Bachelor of Science Degree in Business Administration from Merrimack College in North Andover, Massachusetts, and 30 years of experience working with computer software design, development, maintenance, enhancement and support. He has 10 years of experience working with DB2 on servers running UNIX-like operating systems and 15 years of experience working with DB2 on Z/OS. Mark can be contacted at mark.mulligan.sr@sbcglobal.net.