Backing up an entire database or several tablespaces of a large database can put a strain on system resources, require a considerable amount of additional storage space (to hold the backup images), and can reduce the availability of the database system (particularly if the system has to be taken off line in order to be backed up). This article, from Informit, discusses a popular alternative to creating and maintaining backup images of such databases known as a split mirror.
A split mirror is an "instantaneous" copy of a database that is made by mirroring the disk(s) that contain the database's data, and splitting the mirror when a backup copy of the database is required. Mirroring is the process of writing all database data to two separate disks (or disk subsystems) simultaneously; one disk/subsystem holds the database data while the other holds an exact copy (known as a mirror) of the primary disk/subsystem being used. Splitting a mirror simply involves separating the primary and secondary copies of the database from each other. Split mirroring provides the following advantages:
- The overhead required to create backup images of the database is eliminated
- Entire systems can be cloned very quickly
- Provides a fast implementation of idle standby failover.
To further enhance split mirroring, DB2 UDB provides a way to temporarily suspend (and later resume) all database I/O so that a mirror can be split without having to take a database offline. The command that provides this functionality is the SET WRITE command and the syntax for this command is:
SET WRITE [SUSPEND | RESUME] FOR [DATABASE | DB]
Thus, if you wanted to temporarily suspend all I/O for a database, you would do so by establishing a connection to that database and executing a SET WRITE command that looks like this:
SET WRITE SUSPEND FOR DATABASE
When executed, the SET WRITE SUSPEND FOR DATABASE command causes the DB2 Database Manager to suspend all write operations to tablespace containers and log files that are associated with the current database. (The suspension of writes to tablespaces and log files is intended to prevent partial page writes from occurring until the suspension is removed.) All database operations, apart from online backup and restore operations, will function normally while database writes are suspended. That's because read-only transactions are not suspended and are able to continue working with the suspended database, provided they do not request a resource that is being held by the suspended I/O process. Furthermore, applications can continue to process insert, update, and delete operations using data that has been cached in the database's buffer pool(s). However, new pages cannot be read into the buffer pool(s) and no new database connections can be established.
I/O for a suspended database can be resumed at any time by executing a SET WRITE command that looks like this:
SET WRITE RESUME FOR DATABASE
When executed, the SET WRITE RESUME FOR DATABASE command causes the DB2 Database Manager to lift all write suspensions and to allow write operations to tablespace containers and log files that are associated with the current database to continue. Note: Database I/O must be resumed from the same connection from which it was suspended.
Before a split mirror copy of a DB2 UDB database can be used, it must first be initialized; a split mirror database copy is initialized by executing the system command db2inidb. The syntax for this command is:
db2inidb [DatabaseAlias] AS [SNAPSHOT | MIRROR | STANDBY]
- DatabaseAlias: Identifies the alias assigned to the database the split mirror copy that is to be initialized references.
- ConfigFile: Indicates that the database files contained in the split mirror copy are to be relocated according to information stored in the configuration file specified.
As you can see, a split mirror database copy can be initialized in one of three ways:
- SNAPSHOT. The split mirror copy of the database will be initialized as a clone of the primary database. (It will be a working copy that has its own transaction log files.)
- MIRROR. The split mirror copy of the database will be initialized as a backup image that can be used to restore the primary database.
- STANDBY. The split mirror copy of the database will be initialized and placed in roll-forward pending state so that it can be continuously synchronized with the primary database. (New logs from the primary database can be retrieved and applied to the copy of the database at any time.) The standby copy of the database can then be used in place of the primary database if, for some reason, the primary database goes down.
Thus, if you wanted to initialize a split mirror copy of a database named SAMPLE and make it a backup image that can be used to restore the primary database, you could do so by executing a db2inidb command that looks like this:
db2inidb SAMPLE AS MIRROR
(The split mirror copy of the SAMPLE database used could have been created while the database was online by temporarily suspending I/O with the SET WRITE command, making the split mirror copy of the database using an appropriate non-DB2 UDB utility, and resuming I/O as soon as the split mirror copy was successfully created.)
Read more about split mirrors and other DB2 topics at Informit.
This was first published in November 2004