Being a hardcore computer guy and having worked with developing utility applications in both Turbo C and C# and automating tasks via scripting languages such as VBScript, Powershell, and Perl, every so I stumble across something on the web that deserves some extra recognition. Since I have been in the IT industry for a number of years now, I can appreciate the amount of thought that was given to the script below and the level of readability and structure that was used.
Many of our applications at work are being converted from SQL Server to DB2 primarily due to licensing and costs. As such, many of the tools and techniques that we used under SQL Server no longer work the same with DB2.
I have been toying with rewriting the below script in Powershell, as this is my preferred scripting language on Windows nowadays. If I do, I’ll be sure to update this post.
Please note that I take no credit in the below script. The original script and blog can be downloaded from here and instructions on usage and how to schedule.
1: #!/usr/bin/perl -w
2: use strict;
3: #-----------------------------------------------------------------------------------
4: #
5: # HOT ONLINE BACKUP DB2 - Backs up specified databases from a DB2 instance and
6: # removes backup files that are older than a specified number of days. This
7: # script is designed to be used as a cron job.
8: #-----------------------------------------------------------------------------------
9: #
10: # This script was originally created by the Medusa project:
11: # http://www.lsc-group.phys.uwm.edu/beowulf/medusa/
12: #
13:
14:
15:
16:
17:
18: #-----------------------------------------------------------------------------------
19: # CONFIGURATION CONSTANTS
20: #-----------------------------------------------------------------------------------
21: use vars qw(
22: @ADMIN_EMAIL_ADDRS
23: @DATABASES
24: $LOG
25: $FROM_EMAIL
26: $SERVER_NAME
27: $BACKUP_DIR
28: $URL
29: $DB2_DIR
30: $DAYS_TO_KEEP_BACKUPS
31: $DAYS_TO_KEEP_TRANSACTION_LOGS);
32:
33:
34:
35:
36:
37: #--------------------------------------------------------------------------------------
38: # EMAIL SENDER
39: #--------------------------------------------------------------------------------------
40:
41:
42:
43: # Send the results of the backup to all addresses specified in @ADMIN_EMAIL_ADDRS
44: @ADMIN_EMAIL_ADDRS = (
45: 'Joe Smith ',
46: 'Jane Smith ',
47: 'Jeff Smith '
48: );
49:
50:
51: # $FROM_EMAIL specifies the from email address for the backup summery email
52: $FROM_EMAIL = 'DB2 Backup Script';
53:
54:
55: #List each of the databases to be included in nightly backup
56: @DATABASES = qw(
57: PROD_DB1
58: PROD_DB2
59: DEV_DB1
60: );
61:
62:
63: # Backups older than $DAYS_TO_KEEP_BACKUPS will be deleted automagically
64: $DAYS_TO_KEEP_BACKUPS = 5;
65:
66:
67: # Transaction logs older than $DAYS_TO_KEEP_TRANSACTION_LOGS will be deleted
68: $DAYS_TO_KEEP_TRANSACTION_LOGS = 30;
69:
70:
71: # Set the location and filename for the log
72: $LOG = '/storage/db2/backups/db2backup-' . f_getDateYYYYMMDD() . '.log';
73:
74:
75:
76: #To make it easier for administrators, the log should be
77: # written to a web accessible folder.
78: # $URL specifies the URL that will be sent to the
79: # administrator in the results email for easy access
80: #$URL = 'http://www.lsc-group.phys.uwm.edu/computing/software/ldasdb/logs/backup' . f_getDateYYYYMMDD() . '.log';
81:
82:
83:
84: #The name of the server we are backing up
85: $SERVER_NAME = "PRODUCTION_DB_SERVER";
86:
87:
88:
89: #Location of the backup files:
90: $BACKUP_DIR = '/storage/db2/backups/';
91:
92:
93: #Path to the DB2 executables
94: $DB2_DIR = '/opt/IBM/db2/V9.0';
95:
96:
97:
98: #Set up the environment
99: # DB2 relys on evironment variables which are not set by cron
100: # We need to set them explicitly.
101: $ENV{SHELL}='/bin/bash';
102: $ENV{DB2DIR}='/opt/IBM/db2/V9.0';
103: $ENV{DB2INSTANCE}='db2instance';
104: #$ENV{INSTHOME}='/export/home/db2inst9';
105: #$ENV{JAVA_HOME}='/opt/IBMJava2-14/bin';
106: #$ENV{CLASSPATH}='/usr2/ldasdb/sqllib/java/sqlj.zip:/usr2/ldasdb/sqllib/function:' .
107: '/usr2/ldasdb/sqllib/java/db2java.zip:'.
108: '/usr2/ldasdb/sqllib/java/runtime.zip:.';
109:
110: #Uncomment following to write the environment variables to the log
111: #system "set >> $LOG";
112:
113:
114:
115:
116:
117: #-----------------------------------------------------------------------------------
118: # MAIN - This section of code controls the flow and calls functions to which all
119: # the real work.
120: #-----------------------------------------------------------------------------------
121:
122: # log the begining of the run
123: f_logEntry("\n\n------------------------------\nBeginning database backup.");
124:
125: # $success is 1 if all functions return true, 0 if any of them fail
126: my $success = 1;
127:
128: # first get the databases to backup
129: my @databases = f_getDatabases();
130:
131: # Backup the databases
132: $success = f_backupDatabases(@databases);
133:
134: # delete backups older than $DAYS_TO_KEEP_BACKUP
135: $success = $success * f_purgeOldBackupFiles(@databases);
136:
137: # delete transaction logs older than $DAYS_TO_KEEP_TRANSACTION_LOGS
138: $success = $success * f_purgeOldTransactionLogs(@databases);
139:
140: # notify the administrators of results
141: f_emailAdministrators($success, @ADMIN_EMAIL_ADDRS);
142:
143: # log the end of the run
144: f_logEntry("Database backup completed.\n------------------------------");
145:
146:
147: #-----------------------------------------------------------------------------------
148: # f_getDatabases
149: #-----------------------------------------------------------------------------------
150: # connects to DB2, queries the names of the databases
151: # and returns an array with the database names.
152: #-----------------------------------------------------------------------------------
153: sub f_getDatabases {
154:
155: #for now just return the databases from the the program constant
156: # - later may make this dynamic to query db for list of db's
157: return @DATABASES;
158: }
159:
160:
161: #-----------------------------------------------------------------------------------
162: # f_backupDatabases
163: #-----------------------------------------------------------------------------------
164: # Backs up each of the databases passed to it as an array
165: #-----------------------------------------------------------------------------------
166: sub f_backupDatabases {
167:
168: my $success = 1; #true
169:
170: foreach my $db (@_){
171:
172: #Build the DB2 command to back up the databases
173: my $cmd = "$DB2_DIR/bin/db2 \"backup database $db ONLINE to $BACKUP_DIR " .
174: "with 2 buffers buffer 1000 without prompting \" ";
175: #print $cmd,"\n";
176:
177: #Run the command and write the output to the LOG
178: if (system $cmd . ">> $LOG") #backup command fails
179: {
180: f_logEntry("Backup failed for database $db");
181: $success = 0; #false
182: }else{ #Backup completed with return code 0
183: f_logEntry("Completed backup of $db.");
184: }
185: }
186: return $success;
187: }
188:
189:
190: #-----------------------------------------------------------------------------------
191: # f_purgeOldBackupFiles
192: #-----------------------------------------------------------------------------------
193: # Removes backupfiles for each database (databases passed in as an array).
194: # Files are removed if they are older than $DAYS_TO_KEEP_BACKUPS days
195: #-----------------------------------------------------------------------------------
196: sub f_purgeOldBackupFiles{
197:
198: foreach my $db (@_){
199: if (not opendir(DIR, $BACKUP_DIR)){
200: f_logEntry("Failed to open backup dir to remove old backup files.");
201: return 0; #false
202: }
203:
204: #get list of files begining with database name
205: my @dbFiles = grep {/^$db\./i} readdir(DIR);
206: f_logEntry("Found " . scalar(@dbFiles) . " backups for $db.");
207:
208: #if there are 2 or fewer backups for a db, don't delete em
209: next if (scalar(@dbFiles) < 2);
210:
211: #Sort the directory list
212: @dbFiles = sort @dbFiles;
213:
214: #pop off the most recent backup so at least 1 exists
215: pop(@dbFiles);
216:
217: foreach my $file (@dbFiles){
218: f_logEntry("Checking to see if $file is older than $DAYS_TO_KEEP_BACKUPS days.");
219: #if file is older than $DAYS_TO_KEEP_BACKUPS then delete it
220: my $creationDate = (stat($BACKUP_DIR . $file))[9];
221: my $deleteDate = time - $DAYS_TO_KEEP_BACKUPS*24*60*60;
222: if($creationDate < $deleteDate) {
223: if(unlink($BACKUP_DIR . $file)) {
224: f_logEntry("Deleted file $BACKUP_DIR$file.\n");
225: }else{
226: f_logEntry("Failed to delete $BACKUP_DIR$file.");
227: }
228: }
229: }
230: closedir(DIR);
231: }
232: return 1; #true
233: }
234:
235:
236: #-----------------------------------------------------------------------------------
237: # f_purgeOldTransactionLogs
238: #-----------------------------------------------------------------------------------
239: # Removes transaction logs for each database passed in as an array
240: # if the file is older than $DAYS_TO_KEEP_TRANSACTION_LOGS days.
241: # The directory for the logs is extracted from the databases configuration.
242: #-----------------------------------------------------------------------------------
243: sub f_purgeOldTransactionLogs{
244:
245: #$success will be either 1 for true or 0 for false.
246: my $success = 1; #Lets start out optimistic
247:
248: foreach my $db (@_){
249:
250: #Get the location of the transaction logs from the db2 configuration
251: my $cmd = "$DB2_DIR/bin/db2 get db cfg for $db|grep \"Path to log files\"";
252:
253: #Execute the command and store it in variable
254: #f_logEntry($cmd);
255: #system $cmd . ">> $LOG"; #Uncomment to copy the results of the command to the log
256: my $txt = `$cmd`;
257: my @results = split("=", $txt);
258:
259: #The backup dir is the 2nd element of the array, element 1
260: my $transactionLogDir = $results[1];
261: chomp $transactionLogDir;
262:
263: #Clean off tabs and spaces
264: $transactionLogDir =~ s/(\t|\n| )//ig;
265:
266: #Now read the backup dir
267: if (not opendir(DIR, $transactionLogDir)){
268: f_logEntry("Failed to open transaction log dir $transactionLogDir for DB $db.");
269: $success = 0; #false
270: next;
271: }
272:
273: #get list of log files for the database
274: my @logFiles = grep {/^S\d+\.LOG/i} readdir(DIR);
275: f_logEntry("Found " . scalar(@logFiles) . " transaction logs for $db in $transactionLogDir.");
276:
277: #iterate throught the files and delete the files that are older than days to keep
278: foreach my $file (@logFiles){
279: #if file is older than $DAYS_TO_KEEP_TRANSACTION_LOGS then delete it
280:
281: #Use stat to determine date file was last changed
282: my $modificationDate = (stat($transactionLogDir . $file))[9];
283: my $deleteDate = time - $DAYS_TO_KEEP_TRANSACTION_LOGS*24*60*60;
284:
285: #If the last modification was before the delete date, then delete it (unlink it)
286: if($modificationDate < $deleteDate) {
287: if(unlink($transactionLogDir . $file)) {
288: f_logEntry("Deleted $transactionLogDir$file - modified @ $modificationDate.\n");
289: }else{
290: f_logEntry("Failed to delete $transactionLogDir$file. Check file permissions.");
291: $success = 0; #false
292: }
293: }
294: }
295: closedir(DIR);
296: }
297: return $success;
298: }
299:
300:
301: #-----------------------------------------------------------------------------------
302: # f_notifyAdministrators
303: #-----------------------------------------------------------------------------------
304: # Sends message to administrators when backup has completed.
305: #-----------------------------------------------------------------------------------
306: sub f_emailAdministrators {
307: my ($success, @emails) = @_;
308: my($subject,$message);
309:
310: if($success){
311: $subject = "$SERVER_NAME database backup completed";
312: $message = "Backups completed on " . localtime() . ". No errors were reported.\n\n" .
313: "------------------------------------------\n" .
314: "The backup files are in directory $BACKUP_DIR.\n\n" .
315: "For additional information, review the log $URL";
316: }else{
317: $subject = "Errors occurred during $SERVER_NAME backup.\n";
318: $message = "Errors occurred during the backup completed on " . localtime() . ".\n\n" .
319: "------------------------------------------\n" .
320: "The backup files are in directory $BACKUP_DIR.\n\n" .
321: "For additional information, review the log $URL";
322: }
323: unless($success) {
324: $subject = "Errors occurred during $SERVER_NAME backup.";
325: }
326:
327: foreach my $admin_email_addr (@emails){
328: my $email = "From: $FROM_EMAIL\nTo: $admin_email_addr\nSubject: $subject\n\n$message\n";
329: open(SENDMAIL, "|/usr/lib/sendmail -i -t ") or die "Can't fork for sendmail.";
330: print SENDMAIL $email;
331: close(SENDMAIL);
332: }
333: }
334:
335:
336: #-----------------------------------------------------------------------------------
337: # f_logEntry
338: #-----------------------------------------------------------------------------------
339: # Adds the string passed in to the log with a timestamp
340: #-----------------------------------------------------------------------------------
341: sub f_logEntry{
342: my $str = shift;
343: open LOG, '>>' . $LOG or die "Couldn't open $LOG";
344: print LOG "$str @ " . localtime() . "\n";
345: print "$str @ " . localtime() . "\n";
346: close LOG;
347: return 1;
348: }
349:
350:
351: #-----------------------------------------------------------------------------------
352: # f_getDateYYYYMMDD
353: #-----------------------------------------------------------------------------------
354: # Returns date as YYYYMMDD
355: #-----------------------------------------------------------------------------------
356: sub f_getDateYYYYMMDD{
357: my %month = (
358: Jan => '01',
359: Feb => '02',
360: Mar => '03',
361: Apr => '04',
362: May => '05',
363: Jun => '06',
364: Jul => '07',
365: Aug => '08',
366: Sep => '09',
367: Oct => '10',
368: Nov => '11',
369: Dec => '12'
370: );
371: my @timeparts = split(" ", localtime());
372:
373: #add in leading 0 for day if needed
374: if ($timeparts[2]<10) {$timeparts[2] = "0" . $timeparts[2];};
375:
376: return "$timeparts[4]$month{$timeparts[1]}$timeparts[2]";
377: }
378:
379:
380:
381: This is a hot backup script (online). I also needed a cold, offline backup so I modified the above f_backupDatabases procedure as follows:
382:
383:
384:
385: #-----------------------------------------------------------------------------------
386: # f_backupDatabases
387: #-----------------------------------------------------------------------------------
388: # Backs up each of the databases passed to it as an array
389: #-----------------------------------------------------------------------------------
390: sub f_backupDatabases {
391:
392: my $success = 1; #true
393:
394: foreach my $db (@_){
395:
396:
397: #Build the db2 command to connect to the database
398: my $cmd = "$DB2_DIR/bin/db2 \"connect to $db user username using password \" ";
399: #print $cmd,"\n";
400:
401:
402: #Run the command and write the output to the LOG
403: if (system $cmd . ">> $LOG") #Connect to database command failed
404: {
405: f_logEntry("Connect FAILED for database $db");
406: $success = 0; #false
407: }
408: else
409: { #Backup completed with return code 0
410: f_logEntry("Conneted successfully to database $db.");
411: }
412:
413:
414: #Build the db2 command to disconnect all database users
415: $cmd = "$DB2_DIR/bin/db2 \"force applications all \" ";
416: #print $cmd,"\n";
417:
418:
419: #Run the command and write the output to the LOG
420: if (system $cmd . ">> $LOG") #disconnect applications command fails
421: {
422: f_logEntry("Disconnect users FAILED for database $db");
423: $success = 0; #false
424: }
425: else
426: { #Backup completed with return code 0
427: f_logEntry("Completed disconnect users from database $db.");
428: }
429:
430:
431:
432: #Build the DB2 command to back up the databases
433: $cmd = "$DB2_DIR/bin/db2 \"backup database $db to $BACKUP_DIR with 2 buffers buffer 1000 without prompting \" ";
434: #print $cmd,"\n";
435:
436: #Run the command and write the output to the LOG
437: if (system $cmd . ">> $LOG") #backup command fails
438: {
439: f_logEntry("Backup failed for database $db");
440: $success = 0; #false
441: }
442: else
443: { #Backup completed with return code 0
444: f_logEntry("Completed backup of $db.");
445: }
446: }
447:
448: return $success;
449: }
Pingback: Using Powershell to access DB2 | myblog4fun