DB2 Backup Script

A database is very usefull but it’s always important to backup very often your data. To do 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

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 "C:\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).

@echo off
REM +----------------------------------------------------------+
REM +   DB2 Windows backup script width delete        +
REM +----------------------------------------------------------+
REM + Ver. + Autor    + Remarques                              +
REM +------+----------+----------------------------------------+
REM + 1.00 + RBerthou +                                        +
REM +----------------------------------------------------------+
REM +-- Attention les numeriques de la forme 01 .. 0999 sont  -+
REM +--   considérés comme des nb octal donc prb de calcul    -+
REM +-- d ou l ajout du 1 devant certaines variables          -+
 
REM +- Warning the date est au considéré au format français  -+
REM +- Format de la date "dd/mm/yyyy"                         -+
REM +-  a modifier suivant vos paramètres                     -+
for /f "tokens=1-4 delims=/-\ " %%a in ('date /t') do (
  set mjour=%%a
  set mmois=%%b
  set mannee=%%c
)
set mydate=%mannee%%mmois%%mjour%
set nbhisto=07
 
if /I "%mjour%" GTR "%nbhisto%" (
   set /a mjour=1%mjour%-1%nbhisto%
) else (
   if /I "%mmois%" == "01" (
         set /a mannee=%mannee%-1
         set /a mmois=12
   ) else (
       set /a mmois=%mmois%-1
   )
   set /a mjour=1%mjour%+30-1%nbhisto%
)
if %mmois% LSS 10 ( set mmois=0%mmois%)
if %mjour% LSS 10 ( set mjour=0%mjour%)
set delmax=%mannee%%mmois%%mjour%
REM +- End init    -+
 
set destdir=c:/db2_backup
set db2backupdir=%destdir%/SAMPLE.0/DB2/NODE0000/CATN0000
 
echo "DB2 Backup date=%mydate%"
db2cmd /c /w /i db2 -v -t -f%destdir%/sample_backup.db2 -z%destdir%/sample_backup.log
 
if not %errorlevel% == 0 (
  echo "ERROR: Backup Error backupDB2, RC=%errorlevel% "
  goto fin
)
 
echo "Compress DB2 Backup file"
del /S %destdir%/db2_zip.jar
jar.exe -cvf %destdir%/db2_zip.jar %db2backupdir%/%mydate%
 
if not %errorlevel% == 0 (
  echo "ERROR: Backup Error zipDB2, RC=%errorlevel% "
  goto fin
)
 
echo "DB2 delete catalogue Backup before %delmax%"
db2cmd /c /w /i db2 -v CONNECT TO sample
db2cmd /c /w /i db2 -v prune history %delmax% and delete
db2cmd /c /w /i db2 -v CONNECT RESET
db2cmd /c /w /i db2 -v TERMINATE
if not %errorlevel% == 0 (
  echo "ERROR: Backup Error, deleteDB2 RC=%errorlevel% "
  goto fin
)
 
echo "DB2 delete previos backup files (before %delmax%)"
for /f "skip=%nbhisto%" %%i in ('dir %db2backupdir% /b /o:-n') do (
   rmdir /Q/S %%i
   if not %errorlevel% == 0 (
     echo "ERROR: Backup Error, deleteHisto  RC=%errorlevel% "
   )
)
 
:fin
if %errorlevel% == 0 (
   echo " DB2 backup ok date=%mydate% "
) else (
   echo " Error in DB2 backup date=%mydate% "
)

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:
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • BlinkList
  • LinkedIn
  • Live
  • MisterWong
  • Reddit
  • StumbleUpon
  • Technorati
  • Wikio
  • Yahoo! Buzz
  • MSN Reporter
  • Slashdot
  • SphereIt
  • Twitter
  • Yahoo! Bookmarks
  • Blogosphere News
  • Diigo
  • FriendFeed
  • Netvibes
  • Symbaloo
  • Suggest to Techmeme via Twitter
  • Upnews