Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    13

    Question Unanswered: database fail to connect after db2relocatedb

    I wanted to move my database "test" from D drive to E drive. I tried to use db2relocatedb command to realize that, however, I could not connect to the database again after the command.

    Here is what I did:
    1. copy every content from D:\test\ to E:\test\
    2. deactivate test database, close all db connections
    3. run db2relocatedb command "db2relocatedb -f D:\relocate.cfg"
    DB_NAME=TEST
    DB_PATH=D:\TEST,E:\TEST
    INSTANCE=DB2
    NODENUM=0
    4. check if directory is changed by command "list database directory"
    5. check if container path is changed by command "select * from sysibmadm.dbpaths"
    5. restart db2
    6. activate test database

    Step 1-4 works well as I expected. The Local database directory is shown as E:\TEST.

    But the result of step 5 shows everything (db_storage_path, containers, local_db_directory, dbpath) is still at the old path, which I could not understand. After step 5, I found out that I cannot activate or connect to my database anymore. So I have to change the db_path back to D:\TEST and can connect to my database again.

    Can anyone please help me where did I go wrong? How should this be done? Many many thanks!!

    PS: I am using windows 2008 server, DB2 Enterprise Edition 9.7 Fixpack 8. In my database there are tablespaces with and without automatic storage.
    Last edited by Kilimanjaro; 10-11-13 at 07:06.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you are moving the tablespaces also, you need to tell db2relocatedb where the new paths are. See the manual on how to do this.

    Andy

  3. #3
    Join Date
    Jan 2012
    Posts
    13
    Thank you very much for your reply!!

    But according to the manual:

    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 making changes to 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 (*) could be used both in the old and new paths as a wildcard.

    All my tablespaces are under D:\test\, so I think the CONT_PATH should update automatically.

  4. #4
    Join Date
    Jan 2012
    Posts
    13
    I also tried to move only the containers but not the db_path, as follows:

    DB_NAME=TEST
    DB_PATH=D:\TEST
    INSTANCE=DB2
    NODENUM=0
    CONT_PATH=D:\TEST\tablespace1,E:\TEST\tablespace1
    CONT_PATH=D:\TEST\tablespace2,E:\TEST\tablespace2
    the db2relocatedb command returns:
    No changes to files or control structures were required. DBT1000I The tool completed successfully.

    But when I checked, no container path is ever being updated.
    0 TBSP_CONTAINER D:\TEST\tablespace1.uts
    0 TBSP_CONTAINER D:\TEST\tablespace2.uts


    This is so strange!
    Last edited by Kilimanjaro; 10-11-13 at 12:10.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    How big is the database? I think it's easier to just take a backup, drop db and then restore it and change db path / tablespace containers during restore.

  6. #6
    Join Date
    Jan 2012
    Posts
    13
    Quote Originally Posted by db2girl View Post
    How big is the database? I think it's easier to just take a backup, drop db and then restore it and change db path / tablespace containers during restore.
    Thank you very much for your reply!

    The database is 2 TB.

    I have not so much experience with DB2. I read an article Production to Development: Moving Databases about restoring db.

    I think restore a db from backup is more difficult than just copy the content and relocate the database? E.g. as state in that article, stored procedures have to move separately since they are not included in the backup image.

  7. #7
    Join Date
    Dec 2008
    Posts
    44
    Try this:
    DB_NAME=TEST,TEST
    DB_PATH=D:\TEST,E:\TEST
    INSTANCE=DB2,DB2
    LOG_DIR=<D's log path>,<E's log path>

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    You may find this helpful:
    The db2relocatedb tool

    I had to change db local dir/ tablespace containers for lots of databases and always used backup/restore method. But my databases were all relatively small.

  9. #9
    Join Date
    Jan 2012
    Posts
    13
    Quote Originally Posted by db2girl View Post
    You may find this helpful:
    The db2relocatedb tool

    I had to change db local dir/ tablespace containers for lots of databases and always used backup/restore method. But my databases were all relatively small.
    Thanks a lot db2girl! You encouraged me to do the back/restore. And it works! It even took much shorter time as I expected. I found this thread especially helpful: http://www.dbforums.com/db2/1638079-...ws-v9-5-a.html

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •