Automated backups for DB2 on * nix
Not so long ago, at work, it was necessary to set up automated offline backups for DB2 on AIX. As it was not possible to do this using the standard method, I decided to try and configure everything with the help of scripts that would be launched using the crown. With a simple poking method, I found out that full offline backups of the database that occupy about a few gigabytes can be very successfully archived using 7z. The final results take about 20-50 mb for databases of 2-3 gigabytes. Thus, you can store full backups packaged by date. It remains to automate the whole thing. For this, I decided to write a few scripts that would automatically kill the connection to the database, backup the schema and the database itself.
For example, consider the DB2 9.7.4 Express Edition installed on Centos 5.5. In order for this whole system to work, you must have perl and 7za installed. The base is installed by default in the / home / db2inst1 directory. We will create two directories, one for scripts and the other for backups.
The first script we need is a script to kill connections to the forcedb database.
As you can see, the getDBApplicationHandles function takes application handle-s only from a specific database, and forceDBApplication closes it. This script will not be used during the backup but may be useful in the future. We will embed its functions in the second script. You ask why we embed functions and do not call this script? It's all about speed. Some systems that connect to db2 have time to create new connections before the backup script manages to run the backup database.
In order for this script to work, add the following entries to the .profile.
')
The second script is backupdb
The getTimeStamp function gives us a date that will be used in the name of the backup files. The getPathPrefix function gives us the path for backups to be packaged. You can change these functions in your own way. You can also experiment with the 7za archiving options in the archiveDirectory function. If you have problems with the backup of the database schema, look at the db2look parameters in the backupDB function.
Add another backup.sh script to bin which we will call with crown.
Check all the scripts and add backup.sh to cron, for example, every day at 3 am.
Thank you for your attention.
For example, consider the DB2 9.7.4 Express Edition installed on Centos 5.5. In order for this whole system to work, you must have perl and 7za installed. The base is installed by default in the / home / db2inst1 directory. We will create two directories, one for scripts and the other for backups.
mkdir bin mkdir backups The first script we need is a script to kill connections to the forcedb database.
#!/usr/bin/perl sub getDBApplicationHandles { my $dbname = $_[0]; my @appHandles = (); open (DB2, "db2 list application for database ".$dbname." |"); $f = 0; while (<DB2>){ $l = $_; if($l =~ /^\-\-\-/){ $f = 1; } if($f == 1 && !($l =~ /^\-\-\-/)){ for($i=0;$i<1000;$i++){ $l =~ s/ / /g; } my @val = split(' ', $l); push(@appHandles, @val[2]); } } close DB2; return @appHandles; } sub forceDBApplication { my $dbname = $_[0]; my $forceS = "( "; my $found = 0; my @appHandles = getDBApplicationHandles($dbname); foreach my $val (@appHandles) { $val =~ s/^\s+//; if(length($val) > 0){ $found = 1; $forceS = $forceS . $val . ", "; } } if($found == 1){ $forceS = substr($forceS, 0, length($forceS)-2) . " )"; print "db2 \"force application ".$forceS."\"\n"; system("db2 \"force application ".$forceS."\""); } } if(length($ARGV[0]) == 0){ print "Please specify database name to force connections\n"; exit 1; } forceDBApplication($ARGV[0]); As you can see, the getDBApplicationHandles function takes application handle-s only from a specific database, and forceDBApplication closes it. This script will not be used during the backup but may be useful in the future. We will embed its functions in the second script. You ask why we embed functions and do not call this script? It's all about speed. Some systems that connect to db2 have time to create new connections before the backup script manages to run the backup database.
In order for this script to work, add the following entries to the .profile.
export PATH=~/bin:$PATH export DB2_BACKUP_DIR=~/backups export DB2_BACKUP_PARALLELISM=4 As you can see, we declared two global variables. We will use them in the second script. The parallelism parameter is used during db2 backup and reduces backup time on multiprocessor systems.')
The second script is backupdb
#!/usr/bin/perl use Time::localtime; $globBackupDirectoryPath = $ENV{'DB2_BACKUP_DIR'}; $globBackupParallelism = $ENV{'DB2_BACKUP_PARALLELISM'}; sub getTimeStamp { $tm = localtime; ($DAY, $MONTH, $YEAR, $HOUR, $MINUTE) = ($tm->mday, $tm->mon, $tm->year, $tm->hour, $tm->min); return $HOUR ."-". $MINUTE . "." . $DAY . "." . ($MONTH+1) . "." . ($YEAR+1900); } sub getPathPrefix { $tm = localtime; return ($tm->year+1900). "/" . ($tm->mon+1); } sub archiveDirectory { $dirPath = $_[0]; $dbname = $_[1]; $tm = localtime; ($DAY, $MONTH, $YEAR, $HOUR, $MINUTE) = ($tm->mday, $tm->mon, $tm->year, $tm->hour, $tm->min); $dirPathArch = $dirPath."/tmp/". $dbname .".". getTimeStamp() . ".7z"; if (-e $dirPathArch){ system("rm",$dirPathArch); } system("7za","a","-r","-mx=9","-mfb=64","-md=64m","-ms=on",$dirPathArch,$dirPath."/tmp/"); system("mv",$dirPathArch,$dirPath . "/db/" . getPathPrefix()); system("rm","-Rf",$dirPath."/tmp/"); print $dirPathArch . " archived\n"; } sub backupDB { my $dbname = $_[0]; my $dbschema = $_[1]; my $dbparallelism = "".$globBackupParallelism; my $backupDir = $globBackupDirectoryPath."/".$dbname; # Schema backup if(length($dbschema) > 0){ system("mkdir","-p",$backupDir . "/schema/" . getPathPrefix()); my $schemapath = $backupDir . "/schema/" . getPathPrefix() ."/". $dbname . "." . getTimeStamp().".sql"; system("db2look","-d",$dbname,"-z",$dbschema,"-e","-o",$schemapath,"-nofed"); print "db2 ".$dbname." schema ".$dbschema." backup completed\n"; } # Database backup system("mkdir","-p",$backupDir . "/db/" . getPathPrefix()); system("rm","-Rf",$backupDir."/tmp/"); system("mkdir","-p",$backupDir."/tmp/"); forceDBApplication($dbname); system("db2","backup","database",$dbname,"to",$backupDir."/tmp/","parallelism",$dbparallelism); archiveDirectory($backupDir,$dbname); } sub getDBApplicationHandles { my $dbname = $_[0]; my @appHandles = (); open (DB2, "db2 list application for database ".$dbname." |"); $f = 0; while (<DB2>){ $l = $_; if($l =~ /^\-\-\-/){ $f = 1; } if($f == 1 && !($l =~ /^\-\-\-/)){ for($i=0;$i<1000;$i++){ $l =~ s/ / /g; } my @val = split(' ', $l); push(@appHandles, @val[2]); } } close DB2; return @appHandles; } sub forceDBApplication { my $dbname = $_[0]; my $forceS = "( "; my $found = 0; my @appHandles = getDBApplicationHandles($dbname); foreach my $val (@appHandles) { $val =~ s/^\s+//; if(length($val) > 0){ $found = 1; $forceS = $forceS . $val . ", "; } } if($found == 1){ $forceS = substr($forceS, 0, length($forceS)-2) . " )"; print "db2 \"force application ".$forceS."\"\n"; system("db2 \"force application ".$forceS."\""); } } if(length($ARGV[0]) == 0){ print "Please specify database name and schema to backup\n"; exit 1; } backupDB($ARGV[0],$ARGV[1]); The getTimeStamp function gives us a date that will be used in the name of the backup files. The getPathPrefix function gives us the path for backups to be packaged. You can change these functions in your own way. You can also experiment with the 7za archiving options in the archiveDirectory function. If you have problems with the backup of the database schema, look at the db2look parameters in the backupDB function.
Add another backup.sh script to bin which we will call with crown.
#!/usr/bin/env bash # Loading Enviroment variables . /home/db2inst1/.profile # Backing up database backupdb DBNAME SCHEMANAME echo "Backup Completed" For each database you want to backup, you need to call backupdb with the name of the database you want to backup and the name of the backup scheme you want to save in the .sql file. If you do not specify a scheme, then only a backup of the database will be made.Check all the scripts and add backup.sh to cron, for example, every day at 3 am.
# DB2 backups 0 3 * * * /home/db2inst1/bin/backup.sh Thank you for your attention.
Source: https://habr.com/ru/post/127794/
All Articles