Home » DB2 » DB2 backup script on Linux

DB2 backup script on Linux

Pages

September 2012
M T W T F S S
« Aug   Oct »
 12
3456789
10111213141516
17181920212223
24252627282930

RSS Link

  • An error has occurred; the feed is probably down. Try again later.

Twitter Updates

Blog Stats

  • 345,582 hits

Archives

Pengunjung


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.

1 Comment

  1. SAP OS DB Migration Check minimizes technical risk involved in an operating

    system and/or database migration and avoids unforeseen costs that can occur

    during the SAP OS DB MIGRATION ONLINE TRAINING.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: