DB2 Commands: db2relocatedb
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. >
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/