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

Skill transfer to DB2 for Windows

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.

Share:

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

Allan W. ThamAllan 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.



08 June 2006

Also available in Chinese

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.


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.


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.

Figure 1. DB2 Express-C memory structure
DB2 Express-C Memory Structure

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:

Figure 2. DB2 UDB Express container layout
DB2 UDB Express container layout
  • 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.

Figure 3. Circular logging with secondary turned on
Circular logging with secondary turned on

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.

Figure 4. DB2 archive logging
DB2 Archive Logging

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.

Figure 5. DB2 Express-C memory structure
DB2 Express-C Memory Structure

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>.

Figure 6. DB2 Express-C memory structure
DB2 Express-C Memory Structure

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 logfilsiz. 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.

Table 1. Logging options and parameters comparisons for MySQL and IBM DB2 Express-C
Logging abilitiesAvailable in MySQL - InnoDBAvailable in DB2 Express-CDB2 Express-C parametersComment
Circular logginglogarchmeth1InnoDB's log is written in circular fashion.
Archived logging×logarchmeth1Turn the binary logging, log-bin, on to enable restore to point in time in InnoDB. Use the output of mysqlbinlog to redirect to mysql for point in time recovery. The two parameters, innodb_log_arch_dir and innodb_log_archive, are not used.
Memory allocated for log bufferlogbufszInnoDB's parameter, innodb_log_buffer_size is used.
Log file sizelogfilsizInnoDB's parameter, innodb_log_file_size, is used. In DB2 Express-C, this value is uniform across all log files.
Total primary logs to be createdlogprimaryIn InnoDB, there is only one log file, ibbackup_logfile, in the backup directory that contains the rollforward information. To apply the rollforward, use the option --apply-log.
Total secondary logs to be created×logsecondIn InnoDB, there is no notion of a secondary log to cater the long-running transaction. Logs will be written in a circular fashion in the directory specified by innodb_log_group_home_dir.
Log shippinguserexitFor InnoDB backup using ibbackup, the log file ibbackup_logfile will be generated, which can be used to rollforward using the flag --apply-log. Starting from DB2 Express-C Version 8.2, logs can be included in the backup images. The parameter userexit is purely for back-level compatibility.
Log mirroringmirrorlogpathInnoDB's mirror log is specified by innodb_mirrored_log_groups.
Changing default log pathnewlogpathInnoDB log file and data files will be written to the path specified by innodb_log_group_home_dir.
Overflow log path×overflowlogpath
Active logs spanningnum_log_spanInnoDB log files innodb_log_files_in_group can cater for long-running transactions.
Setting % space allowed within the active log space×max_logIn DB2 Express-C, max_log indicates the total percentage of primary log space that can be used. This parameter ensures that no single transaction consumes all of the active log space.
Control the frequency of log buffer write to diskmincommitInnoDB log flush is controlled by innodb_flush_log_at_trx_commit. In DB2 Express-C, mincommit ensures that a write from log buffer to disk will only occur after a certain interval.
Failover archive path×failarchpathIn DB2 Express-C, archretrydelay and numarchrety are used in conjunction with failarchpath.
To prevent disk-full error×blk_log_dsk_fulIn DB2 Express-C, this parameter will notify (using an administration log) users of disk-full errors and will attempt to create a new log every five minutes until it succeeds in the event disk space is full. DB2, however, does not provide pre-emptive measures to alert you if your disk is 80% or 90% full. All DB2 does is to put a hold on your transactions until more space is made available.
Log management with tapes×logarchmeth1/logarchmeth2In DB2 Express-C, this is an out-of-the box feature supported by using the parameter logarchmeth1 or logarchmeth2. In addition, the built in command, db2tapemgr can be used to for log files store/retrieve management.
Integrate with TSM/Veritas/etc third party solutions×logarchmeth1/logarchmeth2In DB2 Express-C edition, this is an out of the box feature supported by using the parameter logarchmeth1 or logarchmeth2. Tivoli Storage Manager (TSM) is integrated natively into DB2 Express-C. For other vendors, you can use their backup and restore APIs by specifying the option in these parameters.

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.

Listing 1. Offline backup using command CLP
	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.

Listing 2. DB2 Express-C backup syntax
	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
    Offline Backup using 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
    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
    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
    Offline Backup using 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
    Offline Backup using Control Center - Performance
  • You can run the backup immediately or schedule it to run.
    Figure 12. Offline backup using the Control Center - Schedule
    Offline Backup using 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
    Offline Backup using Control Center - Summary
  • During the execution, process progress elapsed time is shown.
    Figure 14. Offline backup using the Control Center - Backup in progress
    Offline Backup using Control Center - Backup in Progress
  • Finally, a successful prompt will be shown.
    Figure 15. Offline backup using the Control Center - Backup success
    Offline Backup using Control Center - Backup Success

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

Listing 3. Offline backup using Command CLP
    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.


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.

Listing 4. Offline restore using Command CLP
    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):

Listing 5. Offline restore using Command CLP
    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
    Offline Backup 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
    Offline Backup 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
    Offline Backup 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
    Offline Backup 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
    Offline Backup using Control Center - Options
  • Take the default performance options. Click

    Next

    to continue.
    Figure 21. Offline restore using Control Center - Performance
    Offline Backup using Control Center - Performance
  • Take the default run immediate. Click Next to continue.
    Figure 22. Offline restore using Control Center - Schedule
    Offline Backup 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
    Offline Backup using Control Center - Summary
  • An elapsed time will be shown and followed by a successful restore message.
    Figure 24. Offline restore using Control Center - Restore Success
    Offline Backup using Control Center - Restore Success

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.

Offline backup and restore - Table of comparisons
Table 2. Offline backup similarities and differences between MySQL and DB2 Express-C
Features/FunctionsAvailability in MySQL (MyISAM - mysqlhotcopy)Availability in DB2 Express-CComment
Logging mode×Binary logging is used mainly for InnoDB. In DB2 Express-C, circular logging is used to support offline backup and restore.
Authorizations requiredFor mysqlhotcopy, SELECT and RELOAD privileges are required. In DB2 Express-C, special authorizations such as sysadm, sysctrl, and sysmaint are required to perform backup and restore.
Connection allowed for the database being backed up×Mysqlhotcopy uses READ LOCK. Users are not required to disconnect. In DB2 Express-C, no connection is allowed during offline backup and restore operations. Use quiesce to put the database in maintenance mode, and subsequently unquiesce to put the database back to normal operation mode.
Database-level backup and restoreIn DB2 Express-C, this feature is provided.
Table space level backup and restore××In DB2 Express-C, offline backup works only for the database level.
Table-level backup and restoreIn DB2 Express-C, offline backup works only for the database level. There is no explicit need to back up a table to guard against accidental table drop. DB2 takes care of this during a data tablespace creation, the option DROPPED TABLE RECOVERY for the table space where the table reside is turned on by default. This option enables the recovery of a dropped table.
Data-only backup (without index)×Mysqlhotcopy uses the --noindices flag.
Regular Express support×Mysqlhotcopy uses the --regexp flag to match databases or tables that require backup. In DB2 Express-C, this is not supported. As backup is at the database level, you simply pick databases that require backing up.
GUI wizard backup and restoreAs of the time of this writing, MySQL Administrator (version 1.1.9) provides the following modes of backup.
  • InnoDB online backup
  • Lock all tables backup
  • Online with binary log position
  • Normal backup
  • Complete backup
In DB2 Express-C, this feature is provided.
Command prompt-level backup and restoreIn DB2 Express-C, this feature is provided.
Multiple image destinations×In DB2 Express-C, this feature is provided.
Throttling for backup×In DB2 Express-C, this is a new feature provided to better make use of resources in both high and low time.
Backup compression×Compression is provided by mysqldump but not mysqlhotcopy. In DB2 Express-C, this feature is provided.
An extensive backup and restore history list is provided×In DB2 Express-C, the history list contains the entire history of backup and restore for a database. This history list captures the following activites.
  • A database or table spaces are backed up, restored, or rolled forward.
  • A table space is created, altered, dropped, quiesced, or renamed.
  • A table is loaded, reorged, or dropped.
  • The creation of new log file.
  • The archive of a log file.
  • etc.
Ability to restore to a new database×In DB2 Express-C, this feature is provided.
Ability to redirect the database files (containers) location during restore×In DB2 Express-C, this is a restore option provided.
An option to choose backup images from a pre-populated list (GUI)×For MySQL Administrator restore, you need to figure out the backup image location. In DB2 Express-C, this is provided in a GUI wizard.
Backup and Restore API×In DB2 Express-C, an embedded program can call upon a backup and restore API provided.
Integration with Vendors×In DB2 Express-C, the out of the box ability to integrate with vendors' APIs is provided.
Performance options to enhance backup and restore×In DB2 Express-C, you can achieve higher parallelism and total buffers being used to enhance the backup and restore process.
Schedulable backup and restoreFor both, a scheduler is provided. You can schedule a backup and restore task to run at an interval on a particular timestamp.
Save backup and restore script (GUI)×MySQL Administrator allows you to save backup projects but not the underlying backup command to a script. In DB2 Express-C, you have the option to save the backup and restore command to a script.
Progress indicator (GUI)×In DB2 Express-C, a progress indicator showing time elapsed is provided.
Progress monitoring for backup, restore, and recovery operations×In DB2 Express-C, to monitor the progress for backup, restore, and recovery operations, use the LIST UTILITIES command. For example, list utilities show detail.
Error or success logBoth provide extensive error logging.

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:

Listing 6. Turn the Archive logging ON - Command CLP
	  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
    Configure Database Logging - via DB2 Control Center
  • Check on Archive logging.
    Figure 26. Configure database logging - Logging type
    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
    Configure Database Logging - Log Archiving
  • Use the defaults.
    Figure 28. Configure database logging - Log archiving
    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
    Configure Database Logging - Logging Location
  • Specify the backup images location.
    Figure 30. Configure database logging - Backup image location
    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
    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
    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):

Listing 7. Offline backup using command CLP
	  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
    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
    Online Backup using Control Center - Options

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.

Listing 8. Offline restore using Command CLP
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.

Listing 9. Offline restore using Command CLP - Recover database
	  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
    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
    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
    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
    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
    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
    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.

Figure 41. Incremental backup
Incremental Backup

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:

Figure 42. Delta backup
Delta Backup

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.

Listing 10. Restore incrementally
	  db2 restore db icmnlsdb incremental automatic taken at <your_image_timestamp>;
          db2 rollfoward db <your_image_timestamp> to end of logs and complete;

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.

Online backup and Restore - Table of comparisons
Table 3. Online backup similarities and differences MySQL and DB2 Express-C
Features/FunctionsAvailability in MySQL (InnoDB - ibbackup)Availability in DB2 Express-CComment
Logging modeibbackup requires the binary logging to be used. In DB2 Express-C, archive logging is used to support online backup and restore.
Authorizations requiredIn DB2 Express-C, special authorizations such as sysadm, sysctrl, and sysmaint are required to perform online backup and restore.
Connection allowed for the database being backed upBoth allow users to be online to carry on their daily tasks.
Database-level backup and restoreBoth support database-level backup and restore.
Table space-level backup and restore×No table space-level for ibbackup
Table-level backup and restoreThough neither supports table-level backup (Note: This is not what we refer to as table dump), to restore from a table drop is possible for both. In DB2 Express-C, offline backup works only for the database level. There is no explicit need to back up a table to guard against accidental table drop. DB2 takes care of this during a data table space creation, the option, DROPPED TABLE RECOVERY for the table space where the table reside is turned on by default. This option enables the recovery of a dropped table.
Database/table space offline during restoreInnoDB restore requires database to be offline. In DB2 Express-C, online backup and restore works only for database and table space levels.
Incremental and delta backup and restore×No incremental or delta backup support for ibbackup. In DB2 Express-C, these features are provided for large databases where a full database backup is not advisable; only changes need to be backed up.
Ability to rollforward to a specific timestampTo recover InnoDB to a particular timestamp, more work is requied. Manual trimming entries according to timestamp from the output generated by mysqlbinlog is required. In DB2 Express-C, you can either roll forward to local or GMT time, or to the end of logs.
Ability to specify an alternate log location in the event of the primary log location failsBoth support this feature.
GUI wizard backup and restore×ibbackup is essentially a command tool.
Command prompt-level backup and restoreIn DB2 Express-C, this feature is provided.
Multiple image destinations×In DB2 Express-C, this feature is provided.
Throttling for backup×In DB2 Express-C, this is a new feature provided to better make use of resources in both high and low time.
Backup compressionBoth support compression.
A complete and extensive backup and restore history list provided×There are logs provided in ibbackup to indicate a failed or successful backup and log apply. However, it is not as complete and extensive to the level of keeping track of all backup and restore commands issued against a particular database. In DB2 Express-C, a history list contains the entire history of backup and restore for a database. In other words, as long as a database is not dropped, the history list will be attached to it through its entire life-span. This history list captures the following activites:
  • A database or table spaces are backed up, restored, or rolled forward.
  • A table space is created, altered, dropped, quiesced, or renamed.
  • A table is loaded, reorged, or dropped.
  • The creation of new log file.
  • The archive of a log file.
  • And more.
Ability to restore to a new database×There is no way to restore the database while renaming it on the fly in ibbackup. In DB2 Express-C, this feature is provided.
Ability to include logs in the backup imageibbackup backup log is included in ibbackup_logfile. In DB2 Express-C, online backup, this feature is provided.
Ability to redirect the database files (containers) location during restore×In DB2 Express-C, this is a restore option provided.
Ability to specify alternate log path during restore×In DB2 Express-C, this restore option is provided.
An option to choose backup images from a pre-populated list (GUI)×In DB2 Express-C, this is provided in a GUI wizard.
Backup and restore API×In DB2 Express-C, an embedded program can call upon the backup and restore API provided.
Integration with vendors×In DB2 Express-C, the out of the box ability to integration with vendors' API is provided.
Performance options to enhance backup and restore×In DB2 Express-C, you can achieve higher parallelism and total buffers being used to enhance the backup and restore process.
Schedulable backup and restore (GUI)×You can set up a cron job for this purpose in ibbackup. In DB2 Express-C, a scheduler is provided. You can schedule a backup and restore task to run at an interval on a particular timestamp.
Save backup and restore script (GUI)×In DB2 Express-C, you have the option to save the backup and restore command to a script.
Progress indicator (GUI)×In DB2 Express-C, a progress indicator showing time elapsed is provided.
Progress monitoring for backup, restore, and recovery operations×In DB2 Express-C, to monitor the progress for backup, restore, and recovery operations, use the LIST UTILITIES command. For example, list utilities show detail.
Error or success logThis feature is available for both.

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.

Listing 11. Throttling setting
	  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.

Figure 43. Setting throttling priority
Setting Throttling priority

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:

Listing 12. Throttling setting
	  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;
Figure 44. Automatic maintenance for backup - Configuration parameters setting
Automatic Maintenance for Backup - Configuration Parameters Setting

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
    Automatic Maintenance for Backup - Maintenance
  • click Next on the introduction page.
  • click Next to change the setting.
    Figure 46. Automatic maintenance for backup - Type
    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
    Automatic Maintenance for Backup - Timing
  • DBAs in charge can be notified.
    Figure 48. Automatic maintenance for backup - Notification
    Automatic Maintenance for Backup - Notification
  • Some other activities or options that can further classify the backup maintenance:
    Figure 49. Automatic maintenance for backup - Activities
    Automatic Maintenance for Backup - Activities
  • Figure 50. Automatic maintenance for backup - Summary
    Automatic Maintenance for Backup - Summary

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.

Table 4. High level-features MySQL and IBM DB2 Express-C
Features/FunctionsAvailability in MySQL MyISAM (mysqldump/mysqlhotcopy)Availability in MySQL InnoDB (ibbackup)Availability in DB2 Express-C
Offline backup
Online backup
Rollforward capability×
Lock Table during backup××
Flexible archive log write path
Flexible archive log restore path
Flexible log full retry control
Built-in log mirroring to prevent single point of failure××
Backup and restore a single table√ (mysqldump)
Point-in-time restore×
Backup and restore progress monitoring××
Database can be local or remote××
A complete history list for backup and restore××
Throttling××
Compression of backup images√ (mysqldump)
Back up only data (without index)××
Built-in notification using email or pager××
Built-in scheduling××
Utility to check backup image integrity√ (myisamchk)
Error and success log
Manual backup and restore
Automatic Backup maintenance××
Incremental and delta backup and restore××

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.


Disclaimer

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

Resources

Learn

Get products and technologies

  • 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.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=127243
ArticleTitle=Leverage MySQL skills to learn DB2 Express: DB2 versus MySQL backup and recovery
publish-date=06082006