31 Jan

DB2 backup script on Linux

Author: rberthou

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 :

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.

Berthou.com

DB2 backup script on Linux

DB2 Backup Script

DB2 command

Batch script file

Remarques

Links

Ajouter une réponse

Categories

Tags

Recent Posts

Recent Comments

Archives

Berthou.com A french developper blog Home About Download Contact
31 Jan DB2 backup script on Linux Author: rberthou Version Fr. 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 Share and Enjoy: Windows script for a DB2 backup DB2 – An image problem on the Open system ? Filed under: db2 RSS feed for comments on this post TrackBack URI Ajouter une réponse Name () E-mail () URI	Entries (RSS) Comments (RSS) Categories applet db2 design dhtml games java mysql OpenSource php Uncategorized websphere wordpress www Tags applet certificat chart css db2 design dhtml exemple game games image java javascript jce linux mysql OpenSource pdf php plugin sample script servlet source theme websphere wordpress www zip Recent Posts Verify PDF signature with iText Wordpress – a good printing with the CSS A ServletFilter to monitor the execution state of a JSP. Plugin – Rbl-Navigator andLinux – Try Linux directly under Windows Recent Comments Jesper Kamstrup Linnet on Sign a PDF document Subhadip on Sign a PDF document narendra on PhpTliste – An Opensource treeview in php parithi on Sign a PDF document mssmotorrd on Plugin Wordpress – rbl-listtag Archives July 2009 June 2009 February 2009 April 2008 March 2008 February 2008 January 2008 December 2007 November 2007 October 2007 September 2007