Introduction

With the continuing global growth of e-business, the necessity of maintaining very large databases and assuring their high availability is becoming a crucial need for the success of any true 24x7 data-oriented business. The suspended I/O feature of IBM DB2® Universal Database^TM (UDB) Version 7 provides an interface for online split mirroring with continuous system availability to meet this crucial need. This paper, written from a DBA perspective, provides definitions of some key concepts (split mirror, suspended I/O and db2inidb) and shares step-by-step instructions for three different implementation scenarios of split mirroring from the DB2 UDB standpoint. Because the splitting process itself varies from vendor to vendor, consult the appropriate storage vendors' documentation for creating split mirror in their applicable systems.

Back to top

Key concepts

In order to implement a split mirror scenario with IBM DB2 Universal Database, it is very important to understand the following three concepts:

• Split mirror
• Suspended I/O
• The db2inidb tool

Split mirror

Split mirror is an identical and independent copy of disk volumes that can be attached to a different system and can be used in various ways, such as to populate a test system, as a warm standby copy of the database, and to offload backups from the primary machine. There is no hardware specific requirements for split mirror. However, it is strongly recommended for users to use any intelligent storage devices such as the IBM Enterprise Storage Server (ESS), known as "Shark"^TM and EMC® Symmetrix® 3330. Using the FlashCopy^TM technology, the ESS can establish near-instantaneous copies of the data entirely within itself. The instant split feature of EMC TimeFinder® software on Symmetrix is also capable of splitting the mirror in a similar manner.

A split mirror of a database includes the entire contents of the database directory, all the tablespace containers, the local database directory and the active log directory if it does not reside on the database directory. The active log directory only needs to be split for creating a clone database using the snapshot option of the db2inidb tool. The other two options, standby and mirror, do not require the active log directory to be split.

Accessing the split mirror does not involve copying. It is dependent on the storage vendor's implementation. Users must use the storage vendor's facilities to access the split mirror as it should not be accessed in any other way.

Suspended I/O

When splitting the mirror it is important to ensure that there is no partial page write occurring on the source database. One way to ensure this is to bring the database offline. This method's required down time is not a feasible solution in a true 24x7 production environment. In an effort to provide continuous system availability during the split mirror process, DB2 Universal Database Version 7 (FixPak 2) introduced a new feature known as suspended I/O, which allows online split mirroring without shutting down the database. The suspended I/O feature ensures the prevention of any partial page write by suspending all write operations on the source database. While the database is in write suspend mode, all of the tablespace states change to a new state, SUSPEND_WRITE, and all operations function normally. However, some transactions may wait if they require disk I/O such as flushing dirty pages from the buffer pool or flushing logs from the log buffer. These transactions will proceed normally once the write operations on the database are resumed. The following command is used to suspend or resume write operations on the source database:

             db2 set write <suspend | resume> for database   

The db2inidb tool

The split mirror created using the suspended I/O feature continues to stay in a write suspend mode until it is initialized to a usable state. To initialize the split mirror, DB2 V7 FixPak 2 introduced a new tool called db2inidb. This tool can either perform a crash recovery on a split mirror image or can put it in a rollforward pending state, depending on the options provided in the db2inidb command, the syntax of which is as follows:

db2inidb <database_alias> as < snapshot |standby |mirror >
[ relocate using <config_file> ]

The "snapshot" option forces the split mirror to go through a crash recovery while the "standby" or "mirror" option puts the database into a rollforward pending state. The "relocate" option is introduced in FixPak 4, and it lets the split mirror be relocated in terms of the database name, database directory path, container path, log path and the instance name associated with the database.

Back to top

Common ways of using suspended I/O and db2inidb

The combination of the suspended I/O feature and the db2inidb tool is necessary to bring the split mirror database into a functional state. With the functionalities of the three options (snapshot, standby, and mirror) provided in the db2inidb tool in conjunction with the suspended I/O feature, it is possible to create a fast snapshot of a database, which can be used to:

• Populate a test system by making a copy of the current data.
• Create a standby database, which can be used as a warm standby.
• Perform an off-line backup of the split mirror database (which contains DMS- only tablespaces) when it is in a rollforward pending state. This functionality was introduced in Version 7 FixPak 3. Backup of a database containing SMS tablespaces is not currently supported when it is in a rollforward pending state.
• Provide a quick file system level recovery option.

The suspend I/O feature is necessary to ensure that all DB2 data gets written out to the disk consistently (that is, no partial page write) before splitting the mirror. This assures a well-defined state where the database can be recovered to later using the db2inidb tool. The db2inidb tool can either force the database to perform a crash recovery (when the snapshot option is specified) or it can put the database into a rollforward pending state (when the standby or mirror option is specified) to allow processing of additional log files.

Back to top

Implementation scenarios

Following are implementations of three scenarios showing how db2inidb and suspended I/O can be used together:

• Creating a clone database to populate a test system
• Creating a standby database to be used as a warm standby
• Creating a mirror database for a quick mirror file recovery

Creating a clone database to populate a test system

The following scenario shows how to create a clone database on the target system using the suspended I/O feature. In this scenario the split mirror database goes through a crash recovery initiated by the db2inidb tool with the snapshot option. You can use a clone database generated in this manner to populate a test database, or to use for generating reports without an impact on the production system. Because of the crash recovery, the clone database will start a new log chain; therefore, it will not be able to replay any future log files from the source database. A database backup taken from this clone database can be restored to the source database. However, it will not be able to rollforward through any log records generated on source database after it was split. Thus, it will be a version level copy only.

Step 1: Suspend I/O on the source database

The following commands suspend I/O (all write activities from DB2 clients) on the source database so that the mirrors of the database containers can be split without the possibility of a partial page write occurring. Please note that suspending I/O on a database will not disconnect the existing connections to the database and all operations would function normally. However, some transactions may wait if they require disk I/O. But as soon as the I/O has been resumed on the database the transactions will proceed normally.

     db2 connect to <source-database>
     db2 set write suspend for database

Step 2: Split the mirror

The process to split a mirror differs from vendor to vendor. Please consult the storage vendor documentation applicable to your device on how to create a split mirror. Regardless of the variations on the split mirror process, all of the following must be split at the same time:

• The entire contents of the database directory
• All the tablespace containers
• The local database directory
• The active log directory, if it does not reside on the database directory

Step 3: Resume I/O on the source database

The following command resumes I/O (all write activities from DB2 clients) on the source database, and the currently running transactions will proceed as normal.

Important: The same database connection that was used to issue the db2 set write suspend command be used to issue the write resume command.

     db2 set write resume for database

Step 4: Make the split mirror accessible on the target machine

After the split of the mirror, the administrator for the target machine must use the facilities of the storage vendor to provide access to the split mirror copy. We refer to that process as "mounting." Do the following steps on the target system:

1. Create the same database instance as it is on the source machine.
2. Catalog the database (system database directory) with the ON parameter:

   db2 catalog database <database-name> as alias on <path> ......

   
   

3. Mount the database directory into the same directory as it is on the source machine.
4. Mount all of the containers to the same paths as they are on the source machine. If the containers are located in several directories, then all container directories must be mounted.
5. If the log files are located in a directory other than the database directory, then mount the log directory into the same directory as it is on the source machine.

Step 5: Start the database instance on the target machine

Start the database manager on the target machine. Assuming that the DB2 registry variable DB2INSTANCE is set to the instance name same as the source machine, the command is simply:

     db2start

Step 6: Bring the clone database into a consistent state

The following command initiates a crash recovery and will roll back all uncommitted transactions, thereby making the database consistent. It is essential to have all the log files from the source that were active at the time of the split. The active log directory must not contain any log file that is not a part of the split mirror. After the completion of the crash recovery, a new log chain will be started, therefore, the database will not be able to roll forward through any of the logs from the source database. The database will now be available for any operation.

     db2inidb <target-database> as snapshot

Creating a standby database to be used as a warm standby

The following scenario shows how to create a standby database on the target system using the suspended I/O feature. In a warm standby database scenario, the log files of the source database will be applied on the target (standby) database. The standby database will be kept in a rollforward pending state until the rollforward has been stopped. A DB2 backup image taken on the clone database (DMS only) can be used for restore on the source database for the purpose of performing a rollforward recovery using the log files produced on the source database after the mirror was split.

Step 1: Suspend I/O on the source database

The following commands suspend the I/O (all write activities from DB2 clients) on the database so that the mirrors of the database containers can be split without the possibility of a partial page write occurring. Please note that suspending I/O on a database will not disconnect the existing connections to the database and all operations would function normally. However, some transactions may wait if they require disk I/O. But as soon as the I/O has been resumed on the database the transactions will proceed normally.

     db2 connect to <source-database>
     db2 set write suspend for database

Step 2: Split the mirror

The process to split a mirror differs from vendor to vendor. Please consult the storage vendor documentation applicable to your device on how to create a split mirror. Regardless of the variations on the split mirroring process, all of the following must be split at the same time:

• The entire contents of the database directory
• All the tablespace containers
• The local database directory

You do not need to split the active log directory in this case.

Step 3: Resume I/O on the source database

The following command resumes I/O (all write activities from DB2 clients) on the source database, and the currently running transactions will proceed as normal.

Important: The same database connection that was used to issue the db2 set write suspend command be used to issue the write resume command.

     db2 set write resume for database

Step 4: Make the split mirror accessible on the target machine

After the split of the mirror, the administrator for the target machine must use the facilities of the storage vendor to provide access to the split mirror copy. We refer to that process as "mounting." Do the following steps on the target system:

1. Create the same database instance as it is on the source machine.
2. Catalog the database (system database directory) with the ON parameter:

   db2 catalog database <database-name> as alias on <path> ......

   
   

3. Mount the database directory into the same directory as it is on the source machine.
4. Mount all of the containers to the same paths as they are on the source machine. If the containers are located in several directories, then all container directories must be mounted.
5. If the log files are located in a directory other than the database directory, then mount the log directory into the same directory as it is on the source machine.

Step 5: Start the database instance on the target machine

Start the database manager on the target machine. Assuming that the DB2 registry variable DB2INSTANCE is set to the instance name same as the source machine, the command is simply:

     db2start

Step 6: Put the database into rollforward mode

The following command places the split mirror database into a "rollforward pending" state. Crash recovery is not performed, and the database remains inconsistent.

     db2inidb <target database> as standby

Step 7: Continually copy over the log files and roll forward

After the database is placed into a "rollforward pending" state, the log files from the source database can be used to roll forward the target database. You can use a user exit program to automate the continuous archival of the inactive log files. If you use a user exit, both source and target databases must be configured with the same user exit program.

     db2 rollforward db <target-database> to end of logs

Step 8: Activate the standby database

If the source database crashes, the standby database on the target machine can be activated for user access. In order to activate the standby database, you must take the standby database out of the rollforward pending state. To do this, issue the rollforward command with the "stop" or "complete" option to bring the database into a consistent state. After the database is in consistent state, users can switch over to the standby database to continue their work. The user applications will have to make new connections to this standby database. The log files generated on the standby database cannot be applied on the source database.

While the target database is in rollforward pending state it is possible to perform an off-line backup if the database has DMS only tablespaces. This functionality was introduced in Version 7 FixPak 3.

     db2 rollforward db <target-database> stop

Creating a mirror database for a quick mirror file recovery

The following scenario shows how to create a mirror database on the target system using the suspended I/O feature. The purpose of this option is to provide the possibility of using a split mirror database to restore on top of the source database and then to rollforward the log files of the source database. It is important to note that the split mirror must remain in the SUSPEND_WRITE state until you have copied it over on top of the source database.

Step 1: Suspend I/O on the source database

The following commands suspend I/O (all write activities from DB2 clients) on the database so that the mirrors of the database containers can be split without the possibility of a partial page write occurring. Note that suspending I/O on a database does not disconnect the existing connections to the database, and all operations would function normally. However, some transactions may wait if they require disk I/O. But as soon as the I/O has been resumed on the database, the transactions will proceed normally.

     db2 connect to <source-database>
     db2 set write suspend for database

Step 2: Split the mirror

The process to split the mirror differs from vendor to vendor. Please consult the storage vendor documentation applicable to your device on creating a split mirror. Regardless of the variations on the split mirroring process, all of the following must be split at the same time:

• The entire contents of the database directory
• All the tablespace containers
• The local database directory

You do not need to split the active log directory in this case.

Step 3: Resume I/O on the source database

The following command resumes I/O (all write activities from DB2 clients) on the source database, and the currently running transactions will proceed as normal.

Important: The same database connection that was used to issue the db2 set write suspend command be used to issue the write resume command.

     db2 set write resume for database

Restoring the split mirror image after a disk failure

There is no "target" database in this scenario. The intent of this scenario is to use the mirror copy to restore on top of the "source" database to recover from a disk failure. The split mirror cannot be backed up using the DB2 backup utility, but it can be backed up using operating system tools. If the source database happens to crash, it can be restored with the split mirror image by copying it on top of the source database.

Step 1: Stop the source database instance

Shut down the database instance by using the following DB2 command:

     db2stop

Step 2: Restore the split mirror image

Using the storage vendor utilities, copy the data files of the split mirror database over the original database. Do not use the operating system utilities in this case because the operating system does not have any knowledge of this split image.

Step 3: Start the source database instance

Start the source database instance after restoring the split mirror image.

     db2start

Step 4: Initialize the mirror copy on the source database

This step replaces the source database with the mirror copy of the database and places it into a rollforward pending state. No crash recovery is initiated, and the database remains inconsistent until it has been rolled forward to the end of logs.

     db2inidb <database> as mirror

Step 5: Roll forward to end of logs

The log files from the source database must be used to rollforward the database:

     db2 rollforward database  <database> to end of logs and complete

Back to top

Handling partitioned environments

In a multi-partitioned environment every partition is treated as a separate database. Therefore, you must suspend the I/O on each partition during the split mirror process. And, of course, the I/O must be resumed on each partition afterwards. The same applies to the db2inidb tool, which must be run on each mirrored partition before using the database.

Because each partition is treated as a separate database, all of the nodes can be suspended independently of one another. Therefore, you do not need to issue a db2_all to suspend I/O on all of the nodes. However, if each node is suspended independently, you must suspend the catalog node last. In a multi-partitioned database, an attempt to suspend I/O on any of the non-catalog nodes will require a connection to the catalog node for authorization. If the catalog node is suspended, then the connection attempt may hang.

Recommendation: To suspend I/O in a multi-partitioned database, issue a 'set write suspend' on all nodes independently, suspending the catalog node last. This means that I/O can be suspended without affecting the other nodes.

The db2inidb tool does not require any connections to the database. Therefore, you can run the tool independently on each split mirror, or you can use db2_all to run it on all partitions simultaneously. The only requirement is for the database manager to be started.

Example: If a multi-partitioned database on AIX has 4 database partitions (0,1,2,3) where the catalog partition is 0, then the recommended commands to suspend I/O would be as follows:

export DBNODE=1; db2 terminate;
db2 "connect to <database-alias>"; db2 "set write suspend  for database"

export DBNODE=2; db2 terminate;
db2 "connect to <database-alias>"; db2 "set write suspend  for database"

export DBNODE=3; db2 terminate;
db2 "connect to <database-alias>"; db2 "set write suspend  for database"

export DBNODE=0; db2 terminate;
db2 "connect to <database-alias>"; db2 "set write suspend  for database"

Example: Running db2inidb on all database partitions simultaneously:

db2_all "db2inidb <target-database> as <options>"

Back to top

Relocating the split mirror on the same machine

Because the split mirror database is dependent on the database directory path, container paths, and log directory path, you cannot split off a database into the same machine unless this path information is reconfigured. When you split a database, you are obviously relocating your database directory, containers and log directories to new paths. If you were to move all of these to new directories on the same machine, the database would not be aware of them at the startup time. The startup of the target system also assumes that the setup of the DB2 instance is identical to the source machine, such as having the same user ID and group ID for the instance owner.

FixPak 4 of DB2 UDB Version 7 introduces a new option for db2inidb called "relocate." The relocate option lets you change the database name, database directory path, container path, log path and the instance name associated with the database. With this new option, you can split the mirror on the same system.

To use the relocate option, provide an input text file that contains the original and the intended configuration information necessary for relocating the split mirror database. Because the relocate option invokes the db2relocatedb command in the background, see the "db2relocatedb (new command)" section of the Release Notes for Fixpak 4 for details on the configuration file options.

Briefly, the format of the configuration file is as follows:

DB_NAME=oldName,newName
DB_PATH=oldPath,newPath
INSTANCE=oldInst,newInst
NODENUM=nodeNumber
LOG_DIR=oldDirPath,newDirPath
CONT_PATH=oldContPath1,newContPath1
CONT_PATH=oldContPath2,newContPath2

The syntax of the db2inidb command using the relocate is as follows:

db2inidb <target-database> as <options> relocate using <config-file>

Back to top

Conclusion

The suspended I/O feature of DB2 Universal Database, combined with the split mirror functions of various intelligent storage devices, lets you implement very large and true 24x7 mission-critical databases by providing:

• Continuous system availability with "zero" down time
• Extremely fast backup and disaster recovery methods for very large databases
• The ability to offload backup from the production database
• Minimized impact on the production database
• Fast population of a test database making copy of the current data

Back to top

Acknowledgments

Special thanks to Dale McInnis and Jason Racicot for their technical review of this paper.

Resources

• Data Recovery and High Availability Guide and Reference
  
  
• IBM DB2 Universal Database, Release Notes, V7.2 / V7.1 FixPak 4
  
  
• Creating Hot Snapshots and Standby Databases with IBM DB2 Universal Database V7.2 and EMC TimeFinder - contains commands and details that are specific to EMC's product, but the concepts are going to be useful in other situations
  
  
• Using Multiple BCV Database Copies with IBM DB2 Universal Database V7.2 and EMC TimeFinder
  
  

About the author

Shahed Quazi is a Senior DB2 UDB Software Development Analsyt with IBM Canada Ltd. at the Software Solutions Toronto Lab. His areas of expertise include DB2 UDB engine and connectivity. He is an IBM Certified Advanced Technical Expert - DRDA, IBM Certified Solutions Expert - DB2 UDB Database Administration and IBM Certified Solutions Expert - DB2 UDB Application Development. He holds a Bachelor's Degree in Computer Science from York University, Toronto, Canada. You can contact Shahed at shahed@ca.ibm.com.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Key concepts
• Common ways of using suspended I/O and db2inidb
• Implementation scenarios
• Handling partitioned environments
• Relocating the split mirror on the same machine
• Conclusion
• Acknowledgments
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers