Posted by I’m pretty proficient with a redirected restore. As a first or second year DBA, I remember being afraid of their voodoo, but I even at one point wrote a redirected restore script (on DB2 7 or 8) that would do a redirected restore of a PeopleSoft database with hundreds of containers, changing the paths in a predictable way on a specific set of systems. I’ve also never done PRIMARY support of a database larger than about 200 GB (sure have on-call, though), and so haven’t generally needed to use db2relocatedb.
As a matter of fact, the first time I used db2relocatedb was last week, and in the last two weeks, I’ve now used it at least four times. I’m sure this is, in part, due to the fact that there’s suddenly a cloning frenzy of servers in “the cloud” for a couple of clients I support. And I personally HATE supporting two databases with the same name – I think it can get confusing, and I want to be absolutely sure that I’m working on the right database.
What db2relocatedb can do
db2relocatedb is essentially making changes to meta data in DB2. With db2relocatedb, you can:
- Change a database name
- Change the database path or drive
- Change tablespace containers
- Change the logpath, mirrorlogpath, failarchivepath, logarchmeth1, logarchmeth2, or overflow log paths
- Change the instance a database is associated with
You can also do any of the above while moving a database between machines.
But here’s the part it doesn’t do: actually move any data. If you’re changing paths for anything, db2relocatedb assumes that you’ve done the actual data moves via some other method.
There are times when it makes more sense to move data at the OS level, I’m sure. I think I’ll still be using restore for most scenarios where I would need to actually move the data. But if you have a faster method of moving huge amounts of data, db2relocatedb could be useful for that.
The thing I find it most useful for is changing a database name. Instead of a half hour or more of backup and restore, I can do the process below in about 5 minutes.
Using db2relocatedb to change a database name
First – create a configuration file
This is really quite simple. The syntax is all documented in the info center. In my case, I created a file called relocatedb.cfg, and that file consisted of:
DB_NAME=SAMPLE,TESTDB DB_PATH=/db_data INSTANCE=db2inst1 NODENUM=0
You can call the file anything you want, since you specify the filename as input on the db2relocatedb command
Second – deactivate your database
You must have the database offline to run db2relocatedb – no connections, deactivated. My favorite method:
> db2 force applications all; db2 deactivate db sample DB20000I The FORCE APPLICATION command completed successfully. DB21024I This command is asynchronous and may not be effective immediately. SQL1496W Deactivate database is successful, but the database was not activated.
Note: this method forces all applications off every database in your instance, so if you have multiple databases in a single instance, you’ll want to use another method to force off any remaining connections.
Of course, you must have some way of keeping applications from connecting while you’re doing the rest – whether that’s stopping WebSphere Commerce or whatever apps you’re using, or even quiescing the database.
Third – run db2relocatedb
The syntax I use is:
> db2relocatedb -f relocatedb.cfg Files and control structures were changed successfully. Database was catalogued successfully. DBT1000I The tool completed successfully. >
References:
DB2 Information Center entry on db2relocatedb: http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0004500.html
Old, but good developerWorks article on db2relocatedb: http://www.ibm.com/developerworks/data/library/techarticle/dm-0407schlamb/
Need Help,
I want to relocate tablespaces containers to a separate volume, i am using following procedure but unfortunately getting some error.
Method db2relocatedb
Configuration File
$ cat relocatedb.cfg
DB_NAME=FUNDAMO
DB_PATH=/proddb
INSTANCE=db2inst1
STORAGE_PATH=/proddb/FUNDAMO/db2inst1/NODE0000/FUNDAMO/T0000005/C0000000.USR,/syslogs/FUNDAMO/db2inst1/NODE0000/FUNDAMO/T0000005/C0000000.USR
Error
=====
$ db2relocatedb -f relocatedb.cfg
DBT1006N The file/device “/syslogs/FUNDAMO/db2inst1/NODE0000/FUNDAMO/T0000005/C0000000.USR/db2inst1” could not be opened.
Following is the releven information.
$ db2 list active databases
Active Databases
Database name = FUNDAMO
Applications connected currently = 0
Database path = /proddb/db2inst1/NODE0000/SQL00001/
$ db2 list tablespace containers for 5
Tablespace Containers for Tablespace 5
Container ID = 0
Name = /proddb/FUNDAMO/db2inst1/NODE0000/FUNDAMO/T0000005/C0000000.USR
Type = File
Tablespace ID = 5
Name = ALFPF
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x4000
Detailed explanation:
Offline
What is the error you get?
$ db2relocatedb -f relocatedb.cfg
DBT1006N The file/device “/syslogs/FUNDAMO/db2inst1/NODE0000/FUNDAMO/T0000005/C0000000.USR/db2inst1″ could not be opened.
Is the database currently using AST for the tablespace in question? It looks to me as though it is expecting storage paths, and you are providing container paths.
I am not clear about AST for tablespaces pleasegive me moredetails about AST.
While it is fact i am providing containers path because in documentation i found the same.
MY key object to relocate tablespace to balance Filesystem I/O. some of critical tablespace i want to move on seprate filesystem.
Please Advise.
Also let me know i will storage path then how i will create stoareg path. before executing db2relocatedb.
I would recommend posing the question on one of the forums – db2-l or dba.stackexchange.com or developerWorks forums. I’ve never seen this particular error before.
Hi Ember,
My db2relocatedb command ran successfully. The database which I relocated was primary database in hadr setup. After relocating, when i check the hadr status, it’s up and working, but when i list the db directory, its displays just the new db name. My question is, when the db sample got relocated, and it did not had a catalog entry, how the activate and db2pd command on db sample(old name) worked. Below are the steps I peformed
[db2inst1@labserver ~]$ db2 deactivate db sample
DB20000I The DEACTIVATE DATABASE command completed successfully.
[db2inst1@labserver ~]$ db2relocatedb -f relocatedb.cfg
Files and control structures were changed successfully.
Database was catalogued successfully.
DBT1000I The tool completed successfully.
[db2inst1@labserver ~]$ db2 activate db sample
DB20000I The ACTIVATE DATABASE command completed successfully.
[db2inst1@labserver ~]$ db2pd -db sample -hadr
Database Partition 0 — Database SAMPLE — Active — Up 0 days 00:00:12 — Date 2014-11-24-12.11.17.924704
HADR Information:
Role State SyncMode HeartBeatsMissed LogGapRunAvg (bytes)
Primary Peer Sync 0 0
ConnectStatus ConnectTime Timeout
Connected Mon Nov 24 12:11:08 2014 (1416811268) 120
LocalHost LocalService
labserver DB2_db2inst1_1
RemoteHost RemoteService RemoteInstance
labserver DB2_db2inst4_1 db2inst4
PrimaryFile PrimaryPg PrimaryLSN
S0000088.LOG 0 0x0000000018E70010
StandByFile StandByPg StandByLSN
S0000088.LOG 0 0x0000000018E70010
[db2inst1@labserver ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = TESTDB
Database name = TESTDB
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
My relocatedb cfg is
DB_NAME=SAMPLE,TESTDB
DB_PATH=/home/db2inst1
INSTANCE=db2inst1
SAMPLE database was also defined in /home/db2inst1
Unfortunately, I don’t have an answer for you on that one. Though I’d love to hear it when you do find one. You might consider asking IBM support. I usually find sev 4 pmrs frustrating, but can sometimes get the answer I need out of them.