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.
>

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/

You may also like...

8 Responses

  1. Abul Farhan says:

    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

  2. Abul Farhan says:

    $ db2relocatedb -f relocatedb.cfg

    DBT1006N The file/device “/syslogs/FUNDAMO/db2inst1/NODE0000/FUNDAMO/T0000005/C0000000.USR/db2inst1″ could not be opened.

    • Ember Crooks says:

      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.

      • syef@pkibm.com says:

        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.

        • Ember Crooks says:

          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.

  3. Kovthe says:

    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

    • Ember Crooks says:

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *