{"id":285,"date":"2010-10-29T18:54:59","date_gmt":"2010-10-29T12:54:59","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=285"},"modified":"2016-01-07T11:31:08","modified_gmt":"2016-01-07T05:31:08","slug":"db2-backup","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=285","title":{"rendered":"Dealing with DB2 UDB backup and archive log"},"content":{"rendered":"

Backing up your database is equally important as maintaining it. \u00c2\u00a0One of the most important duty of a DBA is to ensure that the database is recoverable with zero to minimum data loss. \u00c2\u00a0Why is it important to backup your database ? \u00c2\u00a0Its an easy answer, recoverability. If you want to recover from a data corruption either caused by software or hardware or even worse a disaster. \u00c2\u00a0 You might have a well tuned application that can handle a million transactions per minute, but what good is it if the hard drive corrupts the database or one of your DBA accidentally drops a table or an earthquake swallows your datacenter and you cannot recover the data. \u00c2\u00a0Imagine you telling your VP or CIO that you cannot restore the database and all the data is lost. \u00c2\u00a0Ensuring that you have a recent, full\u00c2\u00a0backup\u00c2\u00a0of your database is an integral part of planning and implementing a disaster recovery strategy for your system. Last year I wrote a script <\/a>that will warn you if a database backup doesn’t exsist.<\/p>\n

DB2 UDB offers two major options : Offline backup and Online backup.
\n1. Offline backup : \u00c2\u00a0Its a very comprehensive backup option. \u00c2\u00a0An offline backup requires the database to be offline, \u00c2\u00a0all connections to the database to be disconnected so that there is no inflight transactions. \u00c2\u00a0This is the only way that you can restore without rollforward. \u00c2\u00a0Here is a sample command for it :
\nBACKUP DB rajudb to \/db2backup;<\/p>\n

2. Online backup: \u00c2\u00a0Unlike offline backup, online backup doesn’t require applications to be disconnected. Online backup is otherwise known as hot backups. \u00c2\u00a0Applications can be selecting and updating the data while the backup is running. \u00c2\u00a0You need to have archive logging turned on for executing an online backup. \u00c2\u00a0In this case, you can restore the backup and use the archive log to rollforward for point in time (PIT) recovery. \u00c2\u00a0 There are three flavors of online backup : \u00c2\u00a0full online backup, \u00c2\u00a0incremental backups and delta backups.
\nHere is a sample command for online backup :
\nBACKUP DB rajudb to \/db2backup online;<\/span><\/p>\n

With online backup, you need to ensure that you save the corresponding logs, so that you can restore database and rollforward to a consistent point. \u00c2\u00a0Since DB2 does log ahead , it will log all the changes before it externalize it to the table, you can the log to reach to a point in time during restore process. \u00c2\u00a0Starting from DB2 V8.1, you have two options of turning on archive logging, \u00c2\u00a0the traditional USEREXIT and LOGRETAIN combination and the LOGARCHMETH1. IBM recommends \u00c2\u00a0using the LOGARCHMETH1 for turning on archive log. This does not require the traditional userexit to be configured. You can specify where the log needs to be archived and DB2 will automatically do that for you.
\nTo turn archive log you simply issue :
\nUPDATE DB CFG USING LOGARCHMETH1 DISK: \/db2inst1_archivelog1<\/span><\/p>\n

You will have to take a backup \u00c2\u00a0of database once you issue this command.<\/p>\n

I am not going to explain the different types of online backup, you can find those explained really well on the IBM Information Center.<\/a><\/p>\n

With archive log turned on and multiple online backups, one of the challenge a dba faces is cleaning up the old archive log and old backups. \u00c2\u00a0 \u00c2\u00a0On V8.x era, \u00c2\u00a0I used the find and rm command to delete old archive log , backups and then prune the history file to keep it small. \u00c2\u00a0 Now with V9.7 \u00c2\u00a0there two \u00c2\u00a0new ways to accomplish all of this : The PRUNE<\/a> command and the automated PRUNE command.
\nYou can now issue PRUNE HISTORY <timestamp> AND DELETE \u00c2\u00a0command which will prune the history prior to the timestamp you specify and delete all the backup image and log files associated with them. \u00c2\u00a0If you are not using the LOGARCHMETH1 \u00c2\u00a0and using LOGRETAIN, then you can use PRUNE LOGFILE PRIOR to <logfile name>.
\nSince most of us now use the LOGARCHMETH1, PRUNE HISTORY will be the main focus. \u00c2\u00a0You can build the PRUNE command within your backup, so its a much cleaner approach :<\/p>\n

PRUNE HISTORY 20101020 AND DELETE<\/span> will delete all the entries from the history file prior to 20101020, the backup image files and archive log prior to 20101020. Optionally you can issue this through the ADMIN_CMD stored procedure as well:
\nCALL \u00c2\u00a0SYSPROC.ADMIN_CMD(‘prune history 20101020 and delete’)<\/span><\/p>\n

Now if you were like me and would like to automate simple administrative task, there is a configure and forget feature on V9.7 on the database configuration. \u00c2\u00a0There are three parameters that can be set to automate the pruning task:<\/p>\n

num_db_backups – <\/strong>specifies the number of database backups to retain for a database
\nrec_his_retentn -\u00c2\u00a0specifies the number of days that history will be retained
\n<\/span><\/strong>auto_del_rec_obj – \u00c2\u00a0specifies whether the file should be deleted or not.<\/span><\/strong><\/p>\n

When auto_del_rec_obj is set to ON, and whenever there are more successful database backup entries than the num_db_backups configuration parameter, then the database manager will automatically prune \u00c2\u00a0history file entries that are older than rec_his_retentn.<\/span><\/strong><\/p>\n

For example , if you have set num_db_backups to 7, rec_his_retentn to 7 and auto_del_rec_obj to ON. \u00c2\u00a0Then once \u00c2\u00a0there are more than 7 successful backups , db2 will prune recovery history data that is older than 7 days and will delete all the backup image and archive log that are associated with the deleted recovery history data.<\/p>\n

These three setting eliminates the need for manually pruning the history file on a regular basis. \u00c2\u00a0You can still issue the PRUNE HISTORY if you need to do for a adhoc requirement.<\/p>\n

Here is a script that lets you backup the database and prunes history file :backupdb.sh<\/a>\u00c2\u00a0. As always ensure that the script is safe to run on your environment and do let me know if you find a bug or have suggestions for improvement.<\/p>\n","protected":false},"excerpt":{"rendered":"

Backing up your database is equally important as maintaining it. \u00c2\u00a0One of the most important duty of a DBA is to ensure that the database is recoverable with zero to minimum data loss. \u00c2\u00a0Why is it important to backup your database ? \u00c2\u00a0Its an easy answer, recoverability. If you want to recover from a data […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[303,3,358],"tags":[450,457,456,85,462,192,451,458,461,453,452,454,88,447,448,449,455,459,196,460],"class_list":["post-285","post","type-post","status-publish","format-standard","hentry","category-database","category-db2","category-how-to","tag-archive-log","tag-automate-pruning","tag-auto_del_rec_obj","tag-backup","tag-backup-script","tag-db2-backup","tag-delete-archive-log","tag-logarchmeth1","tag-logretain","tag-manage-archive-log","tag-manage-backup-file","tag-num_db_backups","tag-online-backup","tag-prune","tag-prune-history","tag-recovery-history-file","tag-rec_his_retentn","tag-setting-up-archive-log","tag-sysibmadmdb_history","tag-userexit"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/285","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=285"}],"version-history":[{"count":11,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/285\/revisions"}],"predecessor-version":[{"id":863,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/285\/revisions\/863"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=285"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=285"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=285"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}