DATA Studio 3.2 : Backup removal script scheduling

Recently while working for one of my financial customers, I was struggling with a lack of documentation for DATA STUDIO 3.2 web console job scheduling.  The task given was to delete the backup images, delete the db cfg , dbm cfg, and db2look , which were taken seven days before the current date .  Every day an offline backup was scheduled to take place at 11:00 PM. The directory where the backups were stored was  d:\backup_acedba .

After connecting to the data base, ACEDBA10 ,I added a job in data studio web console,  by the name of removal_backup_image.  I selected Executable/Shell Script for Type  and  enabled it for scheduling.  In the description I added  “Removing Backup Image every day”.

My data studio  3.2 web console and the database  ACEDBA10 are on the same windows 7 server.  I wrote the script as :

Step 1: In this step I took the dump of the current directory d:\backup_acedba  and redirected it to a file   directory_back.txt

Step 2:  Deleted the files   del_stmts.bat, date_ext.msg.

Step 3:  After connecting to the DATABASE ,  I imported the directory structure into the table db2admin.backupdate_extract. The description of  db2admin.backupdate_extract is as follows:

Step 4 : I called the stored  procedure BACKUPDATE_DEL() . The definition of the Stored procedure is:

In the above stored procedure I selected those backup images ,  CFG(DB AND DBM)  and DB2LOOK files which  were less  than  7  days  and inserted them into a table.

Step 5 : From the backupdate_extract table I  concatenated the rows into batch file (.BAT extension)  because I had to run this at the windows prompt.

Step 6: Now the only steps remaining were to delete the logs which were already older than 7 days.   So I wrote the stored procedure DELETE_LOGS_7DAYS_BEFORE(?) . The code for  the stored procedure is :

CALL SYSPROC.ADMIN_CMD(v_prune_stmt);

End result: The job has been running successfully for   2 weeks.

On a personal note, I wrote the tutorial for DB2 UDB 10.1 610 Part 4 “Working with DB2 Data using SQL”. The article was published yesterday on developer’s network. So please read it and give me suggestions to improve it.  The URL for the tutorial is:

Working with DB2 Data using SQL http://www.ibm.com/developerworks/data/tutorials/db2-cert6104/index.html

Tip of The week:  All those (ZOS/LUW/I series) DBA’s or Developers who have db2 9.x certification, have to write db2 fundamental 10.x again to get certified on 10.x onwards. I learned that when I went for my advanced dba certification. Good luck to everyone for their DB2 10.1 fundamentals certification.