DB2 Backup Script

After my news about DB2 backup script under Windows, I give you in this small article a solution to do the same thing under Linux.

To Backup a database this IBM/DB2 gives you a graphic interface quite simple to use “Control-Center”, but if you want you include this backup in a batch script you must write a backup script without GUI.

In this new I explain how to write a windows script procedure (.bat or .cmd) to backup your DB2 database.

In this sample I considered that you must close your database before backup ( It’s possible to do this in “ONLINE” mode but it’s more complexe — include log file, rollforward…–) and I use a database SAMPLE (it is his name) for this test .

the goal of this scripts is to :

• Quiesce database (close)
• Backup your data
• UnQuiesce database (open)
• Zip backup file
• Clean/Delete old backup rows in DB2 catalogue
• Delete old backup files

Warning ! If you want to do a backup between two differents OS you must use db2move tools (for exmple a backup/restore between Windows and Linux not work).

DB2 command

To execute in a script a command you must initialize DB2 working context. In Windows it is made by a db2cmd but in linux/unix you can do this with the execution of db2profile.

Commands :

• QUIESCE
• UNQUIESCE
• BACKUP DATABASE
• PRUNE

File sample_backup.db2

CONNECT TO sample ;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
 
BACKUP DATABASE sample TO "/db2_backup" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
 
CONNECT TO sample;
UNQUIESCE DATABASE;
CONNECT RESET;
 
TERMINATE ;

Warning : If your current user can not be connected to your database to make a backup you must change this user in CONNECT with these parameters.

CONNECT TO sample USER monuser USING monpassword;

DB2 saves in his catalogue somes informations about each backup. I advise you to clean it often with the command PRUNE HISTORY .

PRUNE HISTORY 20071021 AND DELETE;

This command will delete from the catalogue the informations on previous backup and will delete associated log files

Batch script file

In my opinion I’m not a Windows batch specialist and according to me the date calculation (delmax) of this script can be amelirated (more simple).

#!/bin/sh
 
# +----------------------------------------------------------+
# +   Script de sauvegarde DB2 avec gestion de delete        +
# +----------------------------------------------------------+
# + Ver. + Auteur   + Remarques                              +
# +------+----------+----------------------------------------+
# + 1.00 + RBerthou +                                        +
# +----------------------------------------------------------+
 
# definition de l environnement de travail DB2
. /home/db2inst/sqllib/db2profile
 
NBHISTO=12
MINDATE=`date --date "$NBHISTO days ago" +%Y%m%d`
REM +- Fin initialisation    -+
 
destdir=/db2_backup
db2backupdir=$destdir/SAMPLE.0/DB2/NODE0000/CATN0000
 
echo "DB2 Backup"
db2 -v -f$destdir/sample_backup.db2 -z$destdir/sample_backup.log
returnCode=$?
if [ "$returnCode" != "0" ]
then
   echo "Erreur BACKUP DB2 : $returnCode"
   exit $returnCode
fi
 
echo " Suppression historique backup DB2 PIB "
db2 -v CONNECT TO SAMPLE user myuser using mypassword
db2 -v prune history $MINDATE and delete
db2 -v connect reset
returnCode=$?
if [ "$returnCode" != "0" ]
then
   echo "Erreur BACKUP DB2 PRUNE : $returnCode"
   exit $returnCode
fi
 
# Suppression physique des anciens fichiers
DELTA=50
while [ $DELTA -gt $NBHISTO ]
do
      MAXDATE=`date --date "$DELTA days ago" +%Y%m%d`
      rm /db2data/SAMPLE.0.db2inst.NODE0000.CATN0000.$MAXDATE*
      DELTA=$((DELTA - 1))
done
 
echo "Fin BACKUP DB2 : $returnCode"
exit $returnCode

Remarques

• I advise you, of course, to test this script in developpement environnement and mainly to try to test a restore procedure.
• You can also make an Online database or tablespace backup but it’s more difficult (include log, rollforward,…) and if you are interested in that I write a new on this on the following weeks.

Links

Introduction to DB2 UDB Scripting on Windows
Exemple de script batch windows