All of us are familiar with backup/restore commands and this is the most widely used method for db2 backup and recovery. But there are other ways you can use to backup and recover your data.
1)Advanced Copy Services(ACS)
2)Split mirror
3)db2relocatedb
Let me explain my experience with these utilities. We had a 4TB database and when we tried a redirected restore to move the database to new SAN it took us around 11 days to recover the database . But when we tried the same operation with db2relocatedb it only took us 1 day to complete the operation. So you can imagine the power of these utilities
Advanced Copy Services(ACS)
Advanced copy services enable you to use flash copying technology to backup/restore the data
To perform snapshot backup and restore operations, you need a DB2 ACS API driver for your storage device. Integrated into IBM® Data Server is a DB2 ACS API driver for the following storage hardware:
You can read more about this here:
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0052870.html
Split Mirror
Please read the below resources to get an overview:
http://www.ibm.com/developerworks/data/library/techarticle/dm-0508quazi/
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/t0006422.html
I am going to illustrate this with real time example. In this example we are going to backup from system A (source) and restore onto system B (target)
Basic flow:
1) db2 set write suspend for database
2) Split the mirror
3) db2 set write resume for database
4) Catalog the database on the target server
5) Db2inidb to initiate the target
The db2inidb command initializes the split mirror so that it can be used:
- As a clone database
- As a standby database
- As a backup image
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
1) db2 set write suspend for database. Issue this on the source system
db2 set write suspend for database
DB20000I The SET WRITE command completed successfully.
2)Split the mirror. This method differs from vendor to vendor . Here I am using a simple tar copy method.
Tar USAGE:
To Tar:
tar –cvzf tarfilename foldername
Untar:
tar –xvzf tarfilename
Go to the source path and issue a tar. You have to refer to sysibmadm.dbpaths to obtain all the database paths that needs to be copied to the target system
/sourcepath/db2inst1 – Is the path where all the db files exist. In this example there is only one path and all the database files exist there.
tar -cvzf samparch.tar.gz /sourcepath/db2inst1
3)Set write resume. Once the copy is done you can issue write resume on the source system
db2 set write resume for database
DB20000I The SET WRITE command completed successfully.
4)Move the tar file to the target directory on the target system and untar it in the desired path. Also catalog the database
Source path : /sourcepath/db2inst1
Destination path : /targetpath/test
—untar—-
cd to /targetpath/test
tar –xvzf samparch.tar.gz
–catalog–
db2 catalog db SAMPLE as SAMPLE on /targetpath/test
DB20000I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
5)Define the relocate config file(below is the config file for our example)
DB_NAME=SAMPLE
DB_PATH=/sourcepath/db2inst1/,/targetpath/test
LOG_DIR=/sourcepath/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/,/targetpath/test/db2inst1/NODE0000/SQL00001/SQLOGDIR/
INSTANCE=db2inst1,db2inst1
STORAGE_PATH=/sourcepath/db2inst1/,/targetpath/test/
6) Issue a initialization command
db2inidb SAMPLE as snapshot relocate using relocate.cfg
Relocating database…
Database relocation was successful.
DBT1000I The tool completed successfully.
Please validate the new database and do a basic health check
Db2relocatedb
Db2relocatedb is a powerful tool that can be used to rename database , relocate tablespace or even relocate complete database
Please go through the information in the link below to begin with:
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0004500.html
1)Analyze your database paths
db2 -x “select type,path from sysibmadm.dbpaths”
Type can be :
| Table 2. TYPE column values |
| Type value |
Description |
| TBSP_DEVICE |
Raw device for a database managed space (DMS) table space. |
| TBSP_CONTAINER |
File container for a DMS table space. |
| TBSP_DIRECTORY |
Directory for a system managed space (SMS) table space. |
| LOGPATH |
Primary log path. |
| LOGPATH_DEVICE |
Raw device for primary log path. |
| MIRRORLOGPATH |
Database configuration mirror log path. |
| DB_STORAGE_PATH |
Automatic storage path. |
| DBPATH |
Database directory path. |
| LOCAL_DB_DIRECTORY |
Path to the local database directory. |
If you have automatic storage and want to know the paths used by your database
db2 “select * from sysibmadm.snapstorage_paths”
2)Preapre the cfg file for db2relocatedb
Various options ::
DB_NAME
Specifies the name of the database being relocated. If the database name is being changed, both the old name and the new name must be specified. This is a required field.
DB_PATH
Specifies the original path of the database being relocated. If the database path is changing, both the old path and new path must be specified. This is a required field.
INSTANCE
Specifies the instance where the database exists. If the database is being moved to a new instance, both the old instance and new instance must be specified. This is a required field.
NODENUM
Specifies the node number for the database node being changed. The default is 0.
LOG_DIR
Specifies a change in the location of the log path. If the log path is being changed, both the old path and new path must be specified. This specification is optional if the log path resides under the database path, in which case the path is updated automatically.
CONT_PATH
Specifies a change in the location of table space containers. Both the old and new container path must be specified. Multiple CONT_PATH lines can be provided if there are multiple container path changes to be made. This specification is optional if the container paths reside under the database path, in which case the paths are updated automatically. If you are changing more than one container where the same old path is being replaced by a common new path, a single CONT_PATH entry can be used. In such a case, an asterisk (*) can be used both in the old and new paths as a wildcard.
STORAGE_PATH
This is only applicable to databases with automatic storage enabled. It specifies a change in the location of one of the storage paths for the database. Both the old storage path and the new storage path must be specified. Multiple STORAGE_PATH lines can be given if there are several storage path changes to be made.
*** CFG contents *** (Config file used in our example)
DB_NAME=SAMPLE
DB_PATH=/sourcepath/db2inst7/db2inst7/NODE0000/SQL00001/
INSTANCE=db2inst7
STORAGE_PATH=/sourcepath/db2inst7,/targetpath/db2inst71
3) Stop the database and make sure nothing is running on the database
4) Copy the files recursively from source storage path to destination storage path. You can also use tar command as stated above in split mirror section
cp-rf present/directory /desire/directory
cp -rp /san01/db2inst7/ /san01/db2inst71/ (Do not ignore the backspace at the end)
Validate that all files are copied successfully.. You can compare the sizes and subfolders.
Do a du –sh * and compare the sizes
5) Execute db2relocatedb
db2relocatedb -f db2relocatedb_new.cfg
Files and control structures were changed successfully.
DBT1000I The tool completed successfully.
6)Validate the database functionality
db2 “select * from sysibmadm.snapstorage_paths” – Validate if new paths appear
Validate all the tablespace container paths
Validate diaglog for errors
7) Remove/backup the old files after thorough testing