Leverage MySQL skills to learn DB2 Express: DB2 versus MySQL backup and recovery

Document options Document options requiring JavaScript are not displayed Discuss Learn and share! Exchange know-how with your peers -- try our new Pass It Along beta app Rate this page Help us improve this content

Level: Intermediate

Allan W. Tham (thamawh@my.ibm.com), DB2 Presales Technical Specialist, ASEAN Techline, IBM Malaysia

08 Jun 2006

A database backup mechanism is an integral part of any database. Without a backup, a database would be crippled in the event of media failure. What's more, for most corporations, the seriousness of losing customer data would result in their customers losing trust, with a resulting loss of revenue. IBM® DB2® Express-C comes with a built in-mechanism (not plug-in) for backing up and recovering corporate databases.

Introduction

This article serves as a skill transfer for existing MySQL database administrators to learn about DB2 Express-C edition for backup and recovery. You may already be familiar with the way databases are backed up and restored in MySQL; this article highlights the features and functions and strengths of a backup and recovery strategy, and how it can be accomplished in DB2 Express-C. The article also features how you can explore the absolute no-data lost option in DB2 Express-C.

This article focuses on the capabilities of DB2 Express-C while highlighting the similarities and differences between open source MySQL and DB2 Express-C.

Back to top

Overview

The following topics are covered:

• System structure
• Backup - Offline
• Restore - Offline
• Backup - Online
• Restore - Online
• Throttling
• Automatic backup maintenance
• Other utilities

Introduction

This article focuses primarily on backup and restore using MyISAM and InnoDB storage engines, as they are the most commonly used engines in the MySQL world. This article does not pay attention to MySQL's replication feature.

As MySQL has the options of the underlying storage engines such as MyISAM, Oracle's InnoDB, and Sleepycat's Berkeley BDB, the backup and recovery mechanism therefore varies, depending on the storage engine used. For example, if your underying storage engine is primarily MyISAM, that will leave you with two utilities for backup and recovery: mysqldump and mysqlhotcopy (for performance reasons, this article focuses on mysqlhotcopy). The mysqlhotcopy script makes file-level copies (*.frm, *.MYD, and *.MYI files), and only works for the MyISAM storage engine. It can be run on the same machine as the database that needs to be backed up. To restore, replace your database directory with the backup directory.

Mysqldump is much slower to execute for larger environments; however, it's particularly useful when it comes to backing up important tables. It dumps tables into a readable format. Although mysqldump provides the flag --single-transaction to accomplish online backup for InnoDB (read lock required; FLUSH TABLES WITH READ LOCK), and binary log for rollforward, it's still best to leave it to the chargeable InnoDB backup and recovery tool, better known as ibbackup (Note that this is on top of the InnoDB storage engine itself, which is a chargeable component, as well).

Though inherently non-ACID by nature, MyISAM storage engine backup and recovery can be exercised to the level of version recovery without the data integrity promise. With this storage engine, in the event of a media failure, all units of work since the last good backup is lost.

For a mixed MySQL database environment that comes with both MyISAM and InnoDB storage engines, perhaps a better choice to back up is to use innobackup. This utility backs up InnoDB online while taking a snapshot of the MyISAM tables.

For a transaction-safe storage engine, the InnoDB backup ibbackup is preferred. However, do take note of the compatibility issues listed as follows:

• MySQL/InnoDB 5.0 requires InnoDB Hot Backup 3.0 or newer.
• MySQL/InnoDB 4.1 requires InnoDB Hot Backup 2.0 or newer.
• MySQL/InnoDB 4.0 requires InnoDB Hot Backup 1.40 or newer.
• MySQL/InnoDB 3.23 requires InnoDB Hot Backup 1.40 or newer.
• InnoDB Hot Backup 2.0 works with every MySQL/InnoDB version up to MySQL/InnoDB 4.1, but it is not compatible with MySQL/InnoDB 5.0 or newer.
• InnoDB Hot Backup 3.0 is compatible with every MySQL/InnoDB version from 3.23 to 5.0.

DB2 Express-C comes with a built-in backup and restore mechanism. There is less worry for version mismatch or any compatibility issues. Also, if you use the DB2 Control Center backup and restore, you hardly need to know a single line of code and therefore the process is less error prone. DB2 Express-C backup and restore is simple and easy to accomplish with relatively less effort.

There are two modes databases can be backed up and restored in DB2 Express-C. It differs from MySQL backup and restore, as DB2 Express-C does not have a plug-and-play storage engine that requires its own backup and restore mechanism. For example, backing up MyISAM is different from backing up InnoDB or Berkeley. Special care, or a shell/perl script such as innobackup, is required to back up both MyISAM and InnoDB databases, for example. DB2 Express-C backup and restore, however, is much simple in the sense that it requires only one command to back up (BACKUP)and another to restore (RESTORE).

In DB2 Express-C, other than user data, objects that can be backed up are:

• User-defined functions
• User-defined data types
• Stored procedures
• Triggers
• Sequence
• Tables
• Indexes
• Views

The offline mode is for non-24x7 environment where databases are usually backed up as a whole either once a day or once a week, depending on the intensity of the database. You can also employ this offline mode for databases that used mainly for read-only purpose. This mode is simple and easy though it comes with a cost; data will be lost since the last back up (you have been warned!). Oftentimes, the interval for backup is determined by the amount of transactions and acceptance of data lost in the event of hardware failure. Some simply afford to re-enter data after restoring. This mode in DB2 Express-C is called version recovery where the entire database (non-recoverable database) is backed up at an interval and restored in the event of failure.

There is no need for the entire filesystem to be backed up as DB2 Express-C provides an easy way to carry this offline backup and restore of your databases; there is one command to backup and one command to restore.

The online backup and restore is a built-in mechanism provided to ensure no data is lost in corporate databases. This mode is usually employed by the typical 24x7 shop, where every piece of information is guarded from mishaps. This mode of backup and restore provided by DB2 Express-C comes with more colors and options. For example, for a smaller shop that implements a no-data-lost policy can opt for online backup and restore of databases as a whole. In other words, the backup and restore functions are accomplished at the database level. For a better and more flexible maintenance option, you can choose to back up at the table space level. Last but not least, for really big environments (more applicable for DB2 Workgroup and Enterprise editions), you can perform delta and incremental backups on top of the online full database backup.

And again, don't attempt to back up your filesystem with the hope to back up your databases as well. DB2 Express-C has internal mechanisms to ensure data integrity and syncronization. One may render a database completely useless should he back it up at the filesystem level. And you shouldn't, as DB2 Express-C comes with a simple command for backup and recovery, one command to do online backup and one for online restoring.

Haven't tried cruising down interstate hands'-free? Well, it's rather incomplete to not mention the automatic backup and restore maintenance feature available in DB2 Express-C. This is the feature that enables DBAs to sleep soundly as database backup and recovery can be fully automated.

Finally, the DB2 Express-C edition backup mechanism comes with a throttling feature (more applicable for Workgroup and Enterprise editions) where more CPU power can be acquired more aggressively during less productive hours and vice versa. Throttling has the intelligence not to bring down the server due to the inherent resource-hungry activities such as backing up databases. With this feature, back-up exercises can be run in parallel to even the most productive hours in a corporation.

We will now proceed to look at each category in more detail.

Back to top

System structure

In order to understand the backup and recovery mechanism provided by DB2 Express-C, it's best to have an overview of how it's architected and structured. For example, the log buffer writes each transaction to logs and how different log mode can be used to accomplished different modes of backup and recovery.

Memory structure

The following diagram shows a simplified version of the DB2 Express-C memory structure. For a more complete and elaborate memory structure, refer to the memory model in developerWorks. What essentially we can get away with is that log buffer located in database shared memory is the mechanism provided to ensure each transaction is logged to log files.

The following lists the respective functions for these components:

• Package cache - Memory allocated to store both static and dynamic SQL statements
• Buffer pool - Memory allocated to store data before flushing it to disks
• Log buffer - Memory used to store all changes to database before it is flushed to the logs on disk

Directories layout

With a default installation, DB2 Express-C edition comes with the following table spaces:

• Syscatspace - Stores system catalogue information
• Tempspace1 - Stores system temporary tables. Temporary table space can be either system or user. It is best to create user temporary table space from the system one.
• Userspace1 - storing user data

However, table spaces represent the logical aspect of the database container, the physical layout itself will determine where the actual data are stored in the filesytems.

The layout of the physical filesystem can be broken down, as the following diagram shows:

• Drive/Directory - The drive or directory specified on the CREATE DATABASE command
• DB2 Instance Name - The name of the DB2 Instance owner
• NODE0000 - The partition number of the database (0 for a non-partitioned database)
• SQL00001 - Database ID starting from 1
• SQLOGDIR - The default log directory for the database
• SQLT0000.0 - The catalog table space, SYSCATSPACE
• SQLT0001.0 - The temporary table space, TEMPSPACE1
• SQLT0002.0 - The user table space, USERSPACE1

What is more important as in the event logs need to be shipped to offsite location as versions prior to DB2 Express-C 8.2, DBAs are required to know what archived logs need to be shipped, and therefore where these logs are. With the introduction of the include logs option in the backup command, logs are included in backup images as well. This makes it easier for logs management. For a rollforward recovery, it is not necessary to know where the actual logs sit as the rollforward command figures that out for you.

Logging mechanism

DB2 Express-C comes with two logging modes, circular and archive log modes. In circular log mode, transactions will write to the log in a circular manner. Circular logging only affords you a version recovery. In other words, you cannot roll forward to a specific point in time or to the end of logs from the last full database backup. This mode is good only for recovery where a complete last full backup can be restored. The implication is of course all transaction after the last backup will be lost. As the diagram below shows, the logs will be overwritten as it goes round-robin manner. Active logs are logs that prevent lost of data during crash recovery such as power failure. Secondary logs are created for long-running transactions to spill over; specifying -1 as the value allows infinite secondary logs.

Archive logging enables an online backup and recovery. With online backup, there is a means to roll forward the logs to any specific point in time or to the end of the logs. This is the biggest advantage to configure archive logging. As active logs are logs required for crash recovery, archive logs are logs that are active, but no longer required for crash recovery. However, during logs re-play, archive logs are required. The rollforward to a specific point in time or to the end of logs can only be possible if there aren't "gaps" between these logs. In other words, to ensure absolute no data lost, these logs need to stay intact since the last online backup. By default, after an online backup is complete, DB2 forces the currently active log to be closed in order to ensure a complete set of archive is available for rollforward. Starting with DB2 Express-C Version 8.2, you can include logs into your backup image with the include logs option in the backup command. User exit is another means to ship logs and is only there for back-level compatibility.

DB2 Express-C logging parameters can be controlled and set in either the DB2 Control Center or command prompt CLP. For the Control Center, simply select a database and right-click to choose configure parameters.

To retrieve parameters in command CLP, issue the command get db cfg for <your_db_name>. To update a parameter, issue the command update db cfg using <parameter_name> <value>. For example, to set logretain to ON, issue the command update db cfg using logretain on. Some of the parameter settings will only be effective after you disconnect all applications from the database in effect. Finally, if you need to go back to the system default settings, issue the command reset db cfg for <your_db_name>.

The following defines some important logging-related parameters used in DB2 Express-C. As mentioned earlier, you may need reminders in order to understand the configuration parameters better.

Active logs are logs that required for crash recovery
Archive logs are logs that are active and required for rollforward recovery but no longer needed for crash recovery.

• logarchmeth1/logarchmeth2 (default: OFF) - They first decide what logging mechanism will be used and then the locations to store the archive logs. They enforce archive log files to be written to a location that is not the active log path (by default SQLOGDIR). The default values for these parameters are OFF, which indicates circular logging mode to be used. There are five values you can set for these two parameters:
  • OFF - The default setting to indicate circular logging to be used. Only version recovery is possible with this setting, with no rollforward capability.
  • LOGRETAIN - Setting the value LOGRETAIN will automatically change the value for the parameter, LOGRETAIN to RECOVERY, which indicates archive logging mode will be used. This value enables rollforward during database recovery. This value is valid only for logarchmeth1.
  • USEREXIT - Causes an invocation of a user exit program for archiving and retrieving logs. Setting this option will update the configuration parameter USEREXIT to ON automatically. This value is valid only for logarchmeth1.
  • DISK - Specifies the location (the physical filesystem) to store the archived logs. This option allows you to use tape to store archived logs as well.
  • TSM - Use Tivoli® Storage Manager to maintain archive logs. To use this option, you need to specify the TSM management class name. For example, to use CMDISK as the management class, issue the command update db cfg for <your_db_name> using logarchmeth1 TSM:CMDISK. The configuration parameter TSM_MGMTCLASS will not be automatically updated.
  • VENDOR - This option specifies that the vendor backup and restore API will be used instead.
• logbufsz (default: 8 x 4K) - Indicates the size of memory to used as log buffer.
• logfilsiz (default: 4096 x 4K) - Specifies the uniform size of the log files. Logical limit for total active log space is 256GB.
• logprimary (default: 3) - Specifies the total number of primary logs to be used. The uniform size of each of the log is specified by the parameter logfilsz. Choosing the total number and size of primary logs to be created is a tradeoff between disk space available and the ability of the application to endure the log's full situation. Each log created requires the same disk space, whether it's 10% or 90% full. For long-running transactions, often there is a need to create primary logs with bigger capacity. With the current DB2 Express-C version, the upper limit for total active log space size is 256GB.
• logsecond (default: 2) - Specifies the total log files to be created when the primary log files are full. These log files will only be created after primary log files become full. The default 2 means only 2 log files will be created. You can, however, specify the value to be -1, which allows infinite active log space.
• logretain (default: OFF) - Indicates either circular or archive logging to be used. This value is typically set using logarchmeth1 configuration parameter.
• userexit (default: OFF) - Indicates user exit to be used. This option is deprecated as it is more for back-level compatibility. Moving forward, this value is set using the logarchmeth1 configuration parameter instead.
• mirrorlogpath (default: NULL) - This configuration parameter allows logs protection by keeping the same copy in the path specified. This is particular useful to guard against media failure or accidental deletion of logs that may leave 'gaps' during version or rollforward recovery.

  Note, however, that there are two things you need to pay attention to; that in the event where turning logarchmeth1 to rollforward recovery, the current active logs will not be copied to the path specified in mirrorlogpath and that you need to keep track of this path in the event of log damage in the primary log location.

• newlogpath - The default for this path varies depending on the database ID (the first database created has ID 1) and the node number (more applicable to Workgroup and Enterprise editions). For single node setting with the first database created, the path will be C:\DB2\NODE0000\SQL00001\SQLOGDIR (refer to the directories layout section above for the layout explanation). If you don't like the default path, you can change it using this configuration parameter so that both active and archived logs can be written to the new location.
• overflowlogpath (default: NULL) - Indicates the log path to use during rollforward recovery. Note that the OVERFLOW LOG PATH option is available from the restore command, which has a higher precidence over this configuration parameter.
• num_log_span (default: 0) - Specifies within a unit of work the total number of active logs that can be spanned across. 0 indicates unlimited span.
• failarchpath (default: NULL) - Path to be used when the actual archive path is not available. This path will only be used after the default 5 attempts specified in numarchretry is elapsed. Within this five attempts, however, you can specify the amount of interval (in seconds) or the default (20 seconds) to wait as indicated by the parameter archretrydelay. In other words, using the defaults, you need to wait (5 x 20) seconds before archive logs will be written to the path specified in failarchpath.

Below is a comparison table of features that can be found in both databases, and the parameters that control them. For the MyISAM storage engine using mysqlhotcopy, most of this logging is not applicable.

Back to top

Backup - Offline

Offline backup is the backup method that enables version recovery. This method is used for non-recoverable databases with no archived logs available for recovery. With this method, circular logging is used and logs can be overwritten in a round robin manner. During database restore operation, the entire database (not partial) will be restored using the images backup earlier. This will ensure that the database is brought to the precise time when the images were created. Subsequent transaction since the last backup image will be lost.

A few quick facts about offline backup can be seen here:

• Only version recovery is possible. All unit of works since last backup will be lost.
• This is the default backup mode as indicated by logarchmeth1 to be OFF.
• Allows long running transactions as indicated by logsec.
• No connection is allowed when the backup command is issued.
• Special authorities are required to carry out offline backup - sysadm, sysctrl and sysmaint.
• Backup is at database level. Table or table space level backup is not available.
• A complete backup history list is available.
• No incremental or delta backups.
• Simple, easy, and flexible.

There are two ways to do offline backup, either using command CLP or Control Center. We will show how easily it can be done both ways.

Offline backup using command CLP

To do an offline backup using the command CLP is simple. First make sure that all applications are disconnected by issuing the command db2 force applications all or the quiesce command. The backup command is listed as follows.

					
	db2 backup db <your_db_name> to <your_backup_path>
  

And that's it, even though you can have more options wrapped around it, as shown in the complete syntax below. Essentially, often all you need is just this simple backup command.

					
	BACKUP DATABASE database-alias [USER username [USING password]]
        [TABLESPACE (tblspace-name [ {,tblspace-name} ... ])] [ONLINE]
        [INCREMENTAL [DELTA]] [USE {TSM | XBSA} [OPEN num-sess SESSIONS]
        [OPTIONS {options-string | options-filename}] | TO dir/dev
        [ {,dir/dev} ... ] | LOAD lib-name [OPEN num-sess SESSIONS]
        [OPTIONS {options-string | options-filename}]]
        [WITH num-buff BUFFERS] [BUFFER buffer-size] [PARALLELISM n]
        [COMPRESS [COMPRLIB lib-name [EXCLUDE]] [COMPROPTS options-string]]
        [UTIL_IMPACT_PRIORITY [priority]] [{INCLUDE | EXCLUDE} LOGS] [WITHOUT PROMPTING]
  

Offline backup using DB2 Control Center

Backing up the database offline using the DB2 Control Center is another means to get it done without the need to know a single line of command. The DB2 Control Center is a GUI approach of administration. It's mostly wizard based and self guided for administrative tasks. It provides an option to show the underlying command at the end of each wizard. To do an offline database backup, follow the steps below.

• Bring up DB2 Control Center.
• Pick the database you desire to back up (for example, ABX84) and right-click to choose the backup option.
  
  Figure 7. Offline backup using the Control Center - Right-click to choose the Backup option
  
  
  
• From the first screen, you can acquire information here. Click Next to continue.
  • Database to be backed up.
  • Database State, either available or not available.
  • Last timestamp this database was backed up.
  • Automatic Backup Maintenance. By default, this is disabled. This article will explore this in a later section.
  • Logging type, either circular or archived.
  • Online backup. This option will always be NO for offline backup.
  • Table space level backup. This option will always be NO for offline backup.
  
  
  Figure 8. Offline backup using Control Center - Introduction
  
  
  
• There are few destination options you can choose from. For simplicity's sake, take File System by adding the path. Click Next.
  • File System
  • Tape
  • Tivoli Storage Manager (TSM)
  • XBSA
  • Vendor
  
  
  Figure 9. Offline backup using Control Center - Image
  
  
  

  There are a number of things we can learn from this step.

• Backup Type - Only full backup is enabled. With circular logging, only offline full database backup is possible.
• Availability - With circular logging mode, only offline full backup is possible. Should you desire to do an online backup (which you will explore later), log mode needs to be modified to archived logging. This can be accomplished using either the command prompt CLP or DB2 Control Center using Configure Database Logging Wizard.
• Quiesce Database - You can explicitly disconnect all applications or you can use this new quiesce database feature. It essentially forces all users off an instance or a database and put it into a quiesced mode for maintenance. This option is checked by default.
• Throttling - As mentioned earlier, this is one of the best features to ensure no server overload during the backup period. By default, this option is unchecked. Once checked, you can set the priority accordingly, based on the resources you want to give to this backup exercise.
• Compression - Unchecked by default. Turning it on causes backup images to be compressed.
• 
  
  Figure 10. Offline backup using the Control Center - Options
  
  
  
• This step deals with performance setting. For normal usage, leave this as the default.
  
  Figure 11. Offline backup using the Control Center - Performance
  
  
  
• You can run the backup immediately or schedule it to run.
  
  Figure 12. Offline backup using the Control Center - Schedule
  
  
  
• Next, from the summary page, all previous options/settings are shown. At the same time, the show command button can be used to generate the command and you have the option to save it for later use. Click the Back button should you desire to make changes or the Finish button to execute.
  
  Figure 13. Offline backup using the Control Center - Summary
  
  
  
• During the execution, process progress elapsed time is shown.
  
  Figure 14. Offline backup using the Control Center - Backup in progress
  
  
  
• Finally, a successful prompt will be shown.
  
  Figure 15. Offline backup using the Control Center - Backup success
  
  
  

You probably noticed that the backup image is written in the following format:

					
    DB_alias.Type\Inst_name\NODEnnnn\CATNnnnn\yyyymmdd\hhmmss.Seq_num 

In my environment, I would have something similar to this E:\temp\ABX84.0\DB2\NODE0000\CATN0000\20060519\151906.001.

Back to top

Restore - Offline

In the event of version recovery, a full offline backup image is required to restore to any point in time or the latest consistent state. Restoring the backup image is as simple as backing it up. This can be accomplished in command CLP and then DB2 Control Center.

Offline Restore using Command CLP

Restoring an offline backup is as easy as backing it up. Prior to the offline restore command, you need to either issue commands such as force applications all or the new quiesce and subsequently unquiesce in order to enter into the maintenance mode; lest perhaps you receive the error, SQL1035N The database is currently in use. SQLSTATE=57019.

There are some characteristics for offline restore that are worth noting:

• Restore can overwrite an existing database or be renamed to a new database name.
• Specific images can be restored according to its backup timestamp.
• Authorities such as sysadm, sysctrl and sysmaint is required.
• Restore is at database level. Table or table space level restore is not available.
• A complete restore history list is available.
• No incremental or delta restores.
• Simple, easy, and flexible.

The following command shows how to restore from an offline backup image.

					
    restore db <your_db_name> from <your_backup_directory>

Essentially the above command is all you need. However, there are times when you need more flexibility in that you need to pick a specific backup image to restore, rename a database during restore, etc. For completeness, the restore syntax is as follows (note, however, that some options are meant for online restore):

					
    RESTORE DATABASE source-database-alias { restore-options | CONTINUE | ABORT }
       restore-options:
       [USER username [USING password]] [TABLESPACE [ONLINE] |
       TABLESPACE (tblspace-name [ {,tblspace-name} ... ]) [ONLINE] |
       HISTORY FILE [ONLINE] | LOGS [ONLINE] | COMPRESSION LIBRARY [ONLINE]]
       [INCREMENTAL [AUTOMATIC | ABORT]] [USE {TSM | XBSA} [OPEN num-sess SESSIONS]
       [OPTIONS {options-string | options-filename}] |
       FROM dir/dev [{,dir/dev} ... ] | LOAD shared-lib [OPEN num-sess SESSIONS]
       [OPTIONS {options-string | options-filename}]] [TAKEN AT date-time]
       [TO target-directory] [INTO target-database-alias] [LOGTARGET directory]
       [NEWLOGPATH directory] [WITH num-buff BUFFERS] [BUFFER buffer-size]
       [DLREPORT file-name] [REPLACE HISTORY FILE] [REPLACE EXISTING] [REDIRECT]
       [PARALLELISM n] [COMPRLIB lib-name] [COMPROPTS options-string]
       [WITHOUT ROLLING FORWARD] [WITHOUT DATALINK] [WITHOUT PROMPTING]

Offline restore using DB2 Control Center

There are only a couple of steps to follow for an offline backup using DB2 Control Center.

• Bring up DB2 Control Center.
• Right-click on the database you desire to restore. Choose Restore as the option.
  
  Figure 16. Offline restore using Control Center - Right-click to choose the Restore option
  
  
  
• There are three options to restore in this introduction page of the restore wizard. One of the use cases where history file restore is particularly useful is when you need to decide which database image to restore when the current history file is corrupted. The history stored in the backup image can be individually restored and it contains all backups and restores information. For this case, just use the default. Click Next to continue.
  
  Figure 17. Offline restore using Control Center - Introduction
  
  
  
• Restored images can be entered manually. If the manual option is taken, you need to specify the location of the backup images. Alternatively, pick an available image from the Available backup images column and move it to Selected backup images. This list is populated using the information available logged in the history file (remember the history file is the blueprint of your backup and restore). Click Next to continue.
  
  Figure 18. Offline restore using Control Center - Available Image
  
  
  
• Containers redirection during restore can be useful when the original containers path isn't available or if you need to restore to a non-default container path. We will just take the default. Click Next to continue.
  
  Figure 19. Offline restore using Control Center - Containers
  
  
  
• There are four options available in this step of the wizard.
  • Quiesce database is to put database in maintenance mode with no application connected to it.
  • Datalink is used to reference an object stored external to a database.
  • History file option is to replace the corrupted history file.
  • Restore log file option restores the log files saved in the backup image (more applicable for online backup and restore).

  Choose Queisce database and click Next to continue.

  
  
  Figure 20. Offline restore using Control Center - Options
  
  
  
• Take the default performance options. Click

  Next

  to continue.
  
  Figure 21. Offline restore using Control Center - Performance
  
  
  
• Take the default run immediate. Click Next to continue.
  
  Figure 22. Offline restore using Control Center - Schedule
  
  
  
• At the summary page, review your settings. To view the command that will be issued, click the Show command button. Click Finish to run.
  
  Figure 23. Offline restore using Control Center - Schedule
  
  
  
• An elapsed time will be shown and followed by a successful restore message.
  
  Figure 24. Offline restore using Control Center - Restore Success
  
  
  

Offline backup and restore - Table of comparisons

The objective of the following table of comparisons is to compare the features and functions available for offline backup and restore in MySQL and DB2 Express-C. With this quick mapping, as an existing user of MySQL, you will be able to tell the features that are available in DB2 Express-C, and make good use of them. Though not totally offline, I find it more appropriate to compare mysqlhotcopy with DB2 Express-C offline backup and restore, while leaving the online backup and restore comparisons to be drawn with InnoDB's ibbackup.

Back to top

Backup - Online

Similar to the two backup methods, command CLP and GUI using the DB2 Control Center, we will show how these two methods can be used to accomplish online backup in DB2 Express-C. We will first examine the online backup using the command CLP and then the online backup using DB2 Control Center. Likewise, the restore will be discussed following the same sequence.

Before any attempts to back up a database online, first and foremost, the logging mechanism needs to be modified from the default circular logging to archive logging. To do this, you have the option of issuing a command in command CLP or in a guided GUI wizard. Notably, there isn't a command that can be issued that isn't available in GUI wizard or vice versa. We will, however, show you how to turn it on both ways.

To do this in command CLP, issue the following command:

					
	  db2 update db cfg for <your_db_name> using logarchmeth1 LOGRETAIN
       

To confirm your setting, issue get db cfg for <your_db_name> and find the archlogmeth1 entry. Note that setting the archlogmeth1 parameter to LOGRETAIN will trigger the logretain parameter to RECOVERY. This is the default behavior. Should you ever change your mind about this setting, a quick way to revert is to issue the command reset db cfg for <your_db_name>. Once archive logging is turned on, DB2 will force you to perform an offline full database backup.

Other than just turning the archive logging on, you may want to turn on mirrorlogpath and failarchpath.

To accomplish the same task in DB2 Control Center, there are only couple of clicks:

• Right-click on the database you desire to turn the archive logging on.
  
  Figure 25. Configure database logging using DB2 Control Center
  
  
  
• Check on Archive logging.
  
  Figure 26. Configure database logging - Logging type
  
  
  
• Though there are three options provided, it's recommended to allow DB2 to automatically archive the log files. Using the automatic option requires you to specify primary archive log path and fail archive log path.
  
  Figure 27. Configure database logging - Log Archiving
  
  
  
• Use the defaults.
  
  Figure 28. Configure database logging - Log archiving
  
  
  
• Use the default logging location. The mirror path is to ensure no single point of failure.
  
  Figure 29. Configure database logging - Logging location
  
  
  
• Specify the backup images location.
  
  Figure 30. Configure database logging - Backup image location
  
  
  
• Specify the backup options. The last two steps are scheduling and summary review, respectively.
  
  Figure 31. Configure Database Logging - Backup Options
  
  
  
• Turning archive logging on requires an offline full database backup. Click Finish.
  
  Figure 32. Configure Database Logging - Offline full database backup
  
  
  

Online Backup using Command CLP

One line sums it up (for complete backup syntax, refer to Listing 2):

					
	  db2 backup db <your_db_name> online to <your_backup_path>
       

Online backup using DB2 Control Center

There are only six steps to back up databases or table spaces online using DB2 Control Center, of which the last three steps we will not repeat in screen shots as they are the same as in offline backup. The six steps are:

• Introduction
• Image
• Options
• Performance
• Schedule
• Summary

Follow these steps to back up databases or table spaces online.

• Note that you can either back up the entire database or some selected table spaces within this database. We will select entire database backup to illustrate a few points. If, however, table space backup is selected, you have to select table spaces that you desire to do online backup on the next page.
  
  Figure 33. Online backup using Control Center - Introduction
  
  
  
• Pick a location to store the backup image.
• The incremental and delta backups are greyed out. To turn them on, the configuration parameter trackmod needs to be turned on. Also, there is a way to include logs in the backup image. This is particularly useful for log shipping.
  
  Figure 34. Online backup using Control Center - Options
  
  
  

Back to top

Restore - Online

Online restore, however, is not as mundane as its offline counterpart, and it offers many more options. We will examine what these options are and when to use them best in a given scenario.

Online restore using Command CLP

You need to issue the following commands. Note, however, that you have the option to roll forward to a given local time (new feature) or GMT time.

					
db2 restore db <your_db_name> from <your_image_location> taken at <your_image_timestamp>;
db2 rollforward db <your_db_name> to end of logs and complete;
       

Alternatively, you can issue the simplified command, the RECOVER DATABASE command, which automates the rollforward task for you. See the example below.

					
	  db2 recover db <your_db_name> to end of logs;
	  OR
          db2 recover db <your_db_name> to <your_image_timestamp>;
       

Online restore using DB2 Control Center

You will now walk through the main wizard pages (out of 10) to see some of the features in online restore.

• The three options you can use to restore online are:
  
  Figure 35. Online restore using Control Center - Introduction
  
  
  
• You are presented with two object restore options, table space or the entire database. For table space-level restore, pick the table spaces to be restored in the next page. In our case, we just chose the entire database restore.
  
  Figure 36. Online restore using Control Center - Restore Objects
  
  
  
• Pick an image from a pre-populated list or manually enter in the image location. It would be easiest to pick from the pre-populated list. This list is generated from history list.
  
  Figure 37. Online restore using Control Center - Available images
  
  
  
• There is a way to redirect the container path. We will leave it as the default.
  
  Figure 38. Online restore using Control Center - Containers
  
  
  
• The options given are plenty here, of which the explanations can be found as follows:
  • You could restore only without rolling forward. Not rolling forward leaves your database in pending mode.
  • Rollforward to
    • End of logs - This is typically used when the requirement is for no data lost. It will roll forward to the end of logs, to the very last transaction mode.
    • To a specific local time (new feature) - To roll forward to a specific local timestamp. This feature is added as GMT conversion can proved to be clumsy for some.
    • To a specific GMT time - To roll forward to a specific GMT timestamp.
  • Logs retrieval options:
    • Default archived log path - This is the default path.
    • Alternate archived logs location - This is essentially the same as specifying OVERFLOW LOG PATH in the rollforward command or, alternatively, the OVERFLOWLOGPATH configuration parameter. The former takes precedence if both are used.
    • Disabled retrieval of archived logs - This is controlled by the NORETRIEVE configuration parameter to disable the retrieval of logs.
  
  
  Figure 39. Online restore using Control Center - Roll Forward
  
  
  
• Choose the complete restore and return to the active state.
  
  Figure 40. Online restore using Control Center - Final State
  
  
  
• You can use the defaults for the rest of the wizard steps, Options, Performance, Schedule, and Summary, which I won't repeat as they are similar steps to offline restore. A successful message will be prompted for the restore and rollforward operation.

Incremental and delta Backup and Restore

Though mostly used for bigger environments where DB2 Universal Database™ (UDB) Workgroup and Enterprise are used, this topic is here for completeness.

To facilitate the backup on changes only, DB2 UDB provides incremental (level 1) and delta (level 2) backup on top of a full online backup (level 0).

An incremental backup is a backup of all changes since the most recent successful, full backup. This form of backup is also known as cumulative backup. Each successive incremental image contains the entire contents of the previous incremental image, and the changes since the previous full backup. In the case of a failure, for example on Saturday after the incremental backup is taken, you can just restore the first Sunday full backup and apply the incremental on Saturday. See the diagram below.

A delta backup is a backup of changes since the last successful full, incremental, or delta backup. In the case of a failure on Saturday, you could restore the first Sunday full backup and apply the delta backups from Monday until Saturday. See the diagram below:

In order to carry out incremental or delta backups, the db cfg parameter TRACKMOD needs to be turned on. Assume that we have a weekly level-0 backup on Sunday, a level-1 backup on Wednesday and Saturday, and level-2 backup for the rest of the days, a series of steps will look similar to the following:

• Turn TRACKMOD on - Issue the command db2 update db cfg for icmnlsdb using trackmod on.
• On Sunday - Issue the command db2 backup db icmnlsdb online.
• On Monday - Issue the command db2 backup db icmnlsdb online incremental delta.
• On Tuesday - Issue the command db2 backup db icmnlsdb online incremental delta.
• On Wednesday - Issue the command db2 backup db icmnlsdb online incremental.
• On Thursday - Issue the command db2 backup db icmnlsdb online incremental delta.
• On Friday - Issue the command db2 backup db icmnlsdb online incremental delta.
• On Saturday - Issue the command db2 backup db icmnlsdb online incremental.

To recover from the incremental backup, there are only two steps to follow, Restore the incremental database and roll forward to completion.

					
	  db2 restore db icmnlsdb incremental automatic taken at <your_image_timestamp>;
          db2 rollfoward db <your_image_timestamp> to end of logs and complete;
  

Online backup and Restore - Table of comparisons

Similar to what we have done earlier, the objective of the following table of comparisons is to compare the features and functions available for Online backup and restore in MySQL and DB2 Express-C. Due to its online and rollforward capability, InnoDB's ibbackup will be used to compare against DB2 Express-C.

Back to top

Throttling

Throttling is one of the exciting new things offered in DB2 Express-C. DB2 Express-C backup is a throttled utility that comes with the intelligence to make use of spare resources during the backup period or "steal" resources during a peak hour while backup in running Throttling is a much sought-after feature. To use this feature, you need to have authorizations such as sysadm, sysctrl, and sysmaint. The new configuration parameter, UTIL_IMPACT_PRIORITY, specifies the priority for the throttling utility.

The syntax for setting throttling priority is simple.

					
	  set util_impact_priority for <utility_id> to priority
				

The utility ID can be obtained by issuing the command list utilities. You can set priority within the ranges:

• Priority 0 - Unthrottle
• Priority 1-100 - Highest number being 100, which represents the highest priority

Together with the util_impact_priority parameter is the util_impact_lim configuration parameter. It is a way to specify (in percentage) how much impact is allowed to the server for any given throttled utility. A value of 100 (the default value) will not invoke any throttling. This value will only be meaningful when util_impact_priority is set to non-zero.

The other way to set throttling priority is by using the DB2 Control Center.

Back to top

Automatic backup maintenance

Automatic backup maintenance is the most exciting topic by far. This feature is a hassle-free, sound-sleep feature that most DBAs have been longing for (I remember being one of them!). What is it exactly? Well, automatic backup maintenance is one of the capabilities DB2 Express-C provides to enhance the manageability of database systems. Automatic maintenance for backup (other than runstats and table reorg) joins the list of self-managed properties that can be invoked by one of the criteria below:

• When to perform a full database backup (if you have never done so).
• Elapsed time since the last full backup.
• For online backup (using archive logging), the threshold of the log space used is triggered.

The two database configuration parameters that affect automatic maintenance in backup are auto_db_backup and auto_maint. Set the configuration parameters using the command below:

					
	  db2 update db cfg for <your_db_name> using auto_db_backup ON;
	  db2 update db cfg for <your_db_name> using auto_maint ON;
     

The same configuration parameters can be set using the DB2 Control Center.

One fast way to configure the options for automatic backup maintenance is to use the DB2 Control Center.

• From the right panel (close to the bottom), click on Maintenance.
  
  Figure 45. Automatic maintenance for backup - Maintenance
  
  
  
• click Next on the introduction page.
• click Next to change the setting.
  
  Figure 46. Automatic maintenance for backup - Type
  
  
  
• At this page, both online and offline backup maintenance windows can be set.
  
  Figure 47. Automatic maintenance for backup - Timing
  
  
  
• DBAs in charge can be notified.
  
  Figure 48. Automatic maintenance for backup - Notification
  
  
  
• Some other activities or options that can further classify the backup maintenance:
  
  Figure 49. Automatic maintenance for backup - Activities
  
  
  
• 
  
  Figure 50. Automatic maintenance for backup - Summary
  
  
  

Back to top

Other utilities

Finally, there are a few utilities provided by DB2 Express-C for objects' integrity checks that can detect corruptions:

• inspect - Checks the structures of table objects and table spaces to ensure that they are valid.
• db2dart - Stands for Database Analysis and Reporting Tool, which checks the architectural correctness for a database.
• db2ckbkp - Checks the backup image for any corruption. This check will ensure a particular image is restorable or not.

For an overall comparisons, the following table show how they fare with one another in backup and restore space.

Back to top

Conclusions

This article looked at numerous aspects of both MySQL and DB2 Express-C backup and recovery mechanisms including architecture, memory structure, logging types, and backup and restore type. In an non-exhaustive manner, the comparisons were drawn between MySQL MyISAM and Oracle's InnoDB storage engines, with DB2 Express-C's built-in backup and restore. With MySQL DBAs as the primary target, this article gives insights to planners and IT staffs as well.

Back to top

Disclaimer

This article is written to the best of my knowledge. Should you find any discrepancy, please feel free to contact the author.

Resources

• Optimize the user interface of DB2 CommonStore for Lotus Domino: Learn how to customize user interface for CSLD
  
  
• Explore the IBM Mail Management and Compliance Solution
  
  
• Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to expand DB2 skills.
  
  

• Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.

• Participate in the discussion forum.

About the author

		Allan Tham works in DB2 Content Manager presales support for IBM Business Partners. He helps business partners solve a wide range of technical problems. He is certified for DB2 Content Management administration. Prior to joining IBM, Allan worked in an end-user environment, where he was an Oracle DBA for 3 years.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top