The db2relocatedb tool

A tool that makes it easier to move your databases and table space containers

The db2relocatedb tool allows a DB2® Universal Database™ for Linux, UNIX® and Windows® DBA to physically move the location of an entire database, or of one or more table space containers, without having to perform a backup and restore. This article will discuss the tool, explain when it can be used and how to use it, and offer some real life examples that should give a DBA the confidence to use it like any other tool in the DB2 tool belt.

Share:

Kelly Schlamb (kschlamb@ca.ibm.com), Senior Software Developer, IBM Canada Ltd.

Kelly Schlamb is a Senior Software Developer with the IBM Toronto Lab. Over his 9 year career with the lab, Kelly has performed various roles related to DB2 UDB including support analyst, product planning, and software developer. His area of expertise is the DB2 kernel/engine with his primary focus being the buffer pool services and storage management components. Kelly has presented on these topics at various conferences around the world.



23 June 2004

Also available in Russian

Introduction

The db2relocatedb tool that is shipped with DB2 UDB V7 and V8 can be a very powerful device in the hands of a DBA that knows what it can do and how to use it. The tool allows a DBA to physically move the location of an entire database, or of one or more table space containers, without having to perform a backup and restore (which can be a time and resource-consuming process). It also allows for the renaming of databases and switching the instance that a database belongs to.

The db2relocatedb tool is documented in the DB2 manuals, but people usually learn about it through word of mouth. They are very excited to try it, but because the tool does things like altering internal database structures, files, and so on, they are often nervous about using it. This article will discuss the tool, explain when it can be used and how to use it, and offer some real life examples that should give a DBA the confidence to use it like any other tool in the DB2 tool belt.


What is db2relocatedb?

In addition to the user data it stores, a DB2 database has a great deal of internal metadata that describes where all of that user data can be found, who it belongs to, and how it is referenced. Metadata is always maintained "under the covers" by DB2, not directly by the user or DBA. At the most basic level, db2relocatedb is a tool that can make changes to this metadata when there is either no other way to do it or when the operations to properly do so within the database would cause an unacceptable outage or involve too many steps (compared to using db2relocatedb).

For instance, changing the name and the path associated with a database can be accomplished using DB2’s backup and restore commands. The database is backed up and then restored with a new name and/or target path. The backup may be an online operation, but the restore is offline and the time that the database is unavailable may be unacceptable given your availability requirements.

Another example is changing the location of a table space container, which can also be done using backup and restore (more specifically a redirected restore). However, if the table space is DMS then the ALTER TABLESPACE SQL statement can be used to first add a container in the new location and then drop the one in the old location. The downside to this approach is that the operations must be performed serially, and a table space rebalance may occur after each of these steps.

The db2relocatedb tool can be used to perform these same tasks and in many cases the time it takes (and the effort involved) is less than what would be necessary when doing the operation "within" DB2. But these aren’t the only things the tool can assist with. Here are all of the tasks where the tool can be useful:

  • Changing a database name
  • Changing the path/drive that a database was created on
  • Changing one or more table space containers
  • Changing the log path associated with the database
  • Changing the instance associated with a database
  • Copying/moving a database to the same (or different) machine and making one or more of the above changes

One thing must be made clear at this point since it is something that people are often confused about (and this is described in more detail later on). When you are changing the location of the database or its containers using db2relocatedb, you are actually responsible for moving or copying the files (using whatever methods you choose). The tool itself will not physically move anything; it only makes changes to DB2’s internal metadata to reflect what you have done.


Where can I find it?

The db2relocatedb tool was first introduced in DB2 UDB V7 Fixpak 4. It was initially only described in the Release Notes but has since been added to the product documentation. db2relocatedb is also shipped with all levels of V8.

db2relocatedb is a server-side tool (that is, it runs on the same system where the database is located) so it will be included as part of any server installation. It can be found in the following location depending on the platform:

UNIX: Instance directory/sqllib/bin/db2relocatedb
Windows: Installed drive\sqllib\bin\db2relocatedb.exe


The basics

The syntax for the db2relocatedb tool can be found in the documentation, but you can also get it from the command line. In V7 it was as simple as typing db2relocatedb without specifying any options. In V8, doing this will return a DBT1017N error stating that the syntax is incorrect. However, you can get an expanded version of the error message by running db2 ? dbt1017n which will show the full syntax:

db2relocatedb Syntax
DBT1017N The syntax of the DB2RELOCATEDB tool is incorrect.

Explanation:

 The DB2RELOCATEDB tool has the following syntax:

     db2relocatedb -f <config_file>

 <configFile>: Name of file containing  configuration information.

 File format is:

       DB_NAME=oldName,newName
       DB_PATH=oldPath,newPath
       INSTANCE=oldInst,newInst
       NODENUM=nodeNumber
       LOG_DIR=oldDirPath,newDirPath
       CONT_PATH=oldContPath1,newContPath1
       CONT_PATH=oldContPath2,newContPath2
       ...

 Notes:

o   Database name, database path, and instance name are all
    required fields.  If one of these fields is not changing then
    it is not necessary to list the old and new value for it,
    just give the old/current one.

o   Blank lines or lines beginning with a comment character (#)
    will be ignored.

As the message states, the tool is run by specifying a configuration file. This is a file that the user creates that tells the tool what it needs to change internally within the database (again, remembering that it is up to the user to actually move any files that need to be moved). Here is a detailed description of all of the configuration file parameters:

DB_NAME: This is a required parameter that represents the database name. If the database name is not changing then you only need to specify the current name (that is, you don’t need to list a new name). However, if you are changing the database name then you would specify the current name and the new name, separated by a comma.

DB_PATH: This is a required parameter that represents that path or drive (on Windows) on which the database was originally created. If the database path is not changing then you only need to specify the current path. However, if you are changing the database path then you would specify the current path and the new path, separated by a comma.

INSTANCE: This is a required parameter that represents the DB2 instance name. If the instance name is not changing then just specify the current instance name. Otherwise, specify the current and the new instance name, separated by a comma.

NODENUM: This is an optional parameter that represents the database partition number that we are executing against. This defaults to 0 if it is not specified. See the "Multi-Partitioned Databases" section for more details on using this parameter.

LOG_DIR: This is an optional parameter that represents the location of the database log files. Usually, the UPDATE DB CFG USING NEWLOGPATH command is what people use to change the log file directory but this is available as an alternative if you are changing some of the other paths with the tool anyway.

CONT_PATH: This is an optional parameter that is used to change the location of one or more table space containers. You can specify this parameter any number of times to represent all of the changes that need to be made. For each entry, specify the old container name and the new container name, separated by a comma.

If you have multiple changes to be listed but they all share the same type of change (such as all being moved from one common directory to another) then you can use the asterisk wildcard character. For example:

  CONT_PATH=/oldLocation/*,/newLocation/*

Note: If you create table spaces with relative path containers (that is, you do not give an absolute file or directory name) then DB2 will automatically create them in the database directory (see the next section for information on what this is). If you are changing the database path (using DB_PATH) then you do not need to list all of these containers in the configuration file. The db2relocatedb tool will assume that any containers located within the database directory are being moved as well. In fact, the tool will assume that any containers that were located under the old database path are moving to the new path, so you don’t need to list them either. Example #6 below illustrates these points.


Database directory

When a database is created, DB2 will create a directory to hold the default table spaces and the various control files associated with the database (such as SQLSPCS.1/2, SQLDBCON). The location of this directory is based on information provided by the user (the database path) and information determined from the environment (the instance name and the partition/node number).

Note that if the database path is not provided by the user when the database is created then the value of the database manager configuration parameter DFTDBPATH is used. By default this is the instance owner’s home directory in UNIX, or the path on which DB2 was installed in Windows.

Let’s look at a couple of simple examples to illustrate how DB2 determines the database directory location:

Example #1: Single-partition database

Within instance db2inst1, the following statement is executed:

  CREATE DATABASE TESTDB ON /db2/databasePath

DB2 will create the database in the following directory:

 /db2/databasePath/db2inst1/NODE0000/SQL00001

As you can see, DB2 starts with the database path that was provided on the create database statement (in this example, /db2/databasePath). From there, DB2 then appends the name of the instance (/db2inst1). Next, a directory designating the database partition is added (/NODE0000). The partition number is 0 in this case because we are dealing with a single-partition database. Finally, DB2 adds a directory of the form SQL##### that makes it unique with respect to other databases that might be created within the same path (/SQL00001). As databases are created, they are assigned SQL00001, SQL00002, SQL00003, and so on.

Example #2: Multi-partition database

An instance db2mpp exists with 3 partitions (0, 10, and 20). The following statement is executed which will create a 3 partition database:

  CREATE DATABASE MPPDB ON /database

In this case, DB2 will create the following three directories for the three database partitions (the only difference between the paths is the partition number). Depending on whether the partitions are logical or physical, these directories may or may not be on the same machine.

 /database/db2mpp/NODE0000/SQL00001
 /database/db2mpp/NODE0010/SQL00001
 /database/db2mpp/NODE0020/SQL00001

Additionally, DB2 will create a directory called sqldbdir in the same location as the SQL##### directories. For our examples above, the following directories would be created:

 /db2/databasePath/db2inst1/NODE0000/sqldbdir

 /database/db2mpp/NODE0000/sqldbdir
 /database/db2mpp/NODE0010/sqldbdir
 /database/db2mpp/NODE0020/sqldbdir

The information stored within sqldbdir is known as the volume database directory or the local database directory. It is this information that says "database TESTDB can be found in directory SQL00001," and it is this information that gets returned when you execute a LIST DB DIRECTORY ON <path> command. The important thing to remember though is that this directory is absolutely required for DB2 (or any tools like db2relocatedb) to be able to find the database. As you will see in the examples below, this directory must be moved or copied any time that a database is moved or copied using db2relocatedb.


Multi-partitioned databases

If you need to make changes to a multi-partitioned database (where you are using DB2 UDB EEE on V7 or DB2 UDB ESE with the data partitioning feature on V8), then there are some things to be aware of.

  • First, each partition has its own subset of metadata. Therefore, for most operations you will need to run the tool against each partition. Changing any one of the instance name, database name, or database path requires that the changes be made on all partitions. If this is not done, then either the database will not start up, or you may encounter unexpected results at a later time. However, if you only wish to change the location of a particular table space container on one partition, all you need to do is run db2relocatedb on that one partition.
  • Second, each partition that you plan on running the tool against must have its own configuration file. The reason for this is that the NODENUM parameter must be specified, and it must match the database partition number that you are using it on.
  • Third, you cannot use the tool to change the partition number associated with a database partition. For example, you cannot change database partition 10 to be database partition 20.
  • Finally, the NODENUM parameter does NOT direct the tool to run on the corresponding partition. In other words, if the configuration file has NODENUM set to 10 but you are running it on database partition 0 (which is on a separate physical machine), the tool will not go out and find partition 10 to run it on. As described above, some of the information provided in the file (such as NODENUM in the case of a multi-partitioned database) must be specified even if the value isn’t changing. These must be provided such that the tool can actually go out and find the database and its files, and the location of these things are based on things like the instance name, the database path, and the database partition number.

Examples

Note: The database must be shutdown prior to running the db2relocatedb tool.

Example #1: Changing the name of a single-partition database

Database name: TESTDB
Database path: /home/db2inst
Instance name: db2inst

Scenario: You wish to change the name of this database from TESTDB to NEWNAME.

Configuration File "example1.cfg"

DB_NAME=TESTDB,NEWNAME
DB_PATH=/home/db2inst
INSTANCE=db2inst

Command:

  db2relocatedb –f example1.cfg

Example #2: Changing the name of a multi-partition database

Database name: PRODUCTS
Database path: /dbdir
Instance name: db2mpp1
Partitions: 1, 2, 3

Scenario: You wish to change the name of this three partition database from PRODUCTS to OLDPROD.

Configuration File "example2-1.cfg"

DB_NAME=PRODUCTS,OLDPROD
DB_PATH=/db2dir
INSTANCE=db2mpp1
NODENUM=1

Configuration File "example2-2.cfg"

DB_NAME=PRODUCTS,OLDPROD
DB_PATH=/db2dir
INSTANCE=db2mpp1
NODENUM=2

Configuration File "example2-3.cfg"

DB_NAME=PRODUCTS,OLDPROD
DB_PATH=/db2dir
INSTANCE=db2mpp1
NODENUM=3

Commands:

  { If on different physical machines, go to node 1 (else "export DB2NODE=1") }
  db2relocatedb –f example2-1.cfg

  { If on different physical machines, go to node 2 (else "export DB2NODE=2") }
  db2relocatedb –f example2-2.cfg

  { If on different physical machines, go to node 3 (else "export DB2NODE=3") }
  db2relocatedb –f example2-3.cfg

If you are an experienced multi-database partition DBA, you can probably come up with something more clever to avoid having to go to each node and run the commands individually. For example, an alternative would be to have all of the files in a location accessible by all partitions and execute something like the following:

  db2_all "db2relocatedb –f {common_location}/example2-\$DB2NODE.cfg"

Example #3: Changing the path associated with a database

Database name: SALES
Database path: /home/kschlamb
Instance name: kschlamb

Scenario: You’ve realized that you’ve created your database in your instance’s home directory and not on a larger, dedicated file system like you intended. The new database path is called /salesdb.

As described in one of the sections above, the following directories were created when the database was originally created:

  /home/kschlamb/kschlamb/NODE0000/SQL00001
  /home/kschlamb/kschlamb/NODE0000/sqldbdir

The first step is for you to manually move these two directories to the locations listed below. Remember, the database path is only part of the directory structure that DB2 uses so you must follow the same rules when you determine the target location. You can use any OS commands that you want to move the files (for example cp/rm, mv, tar).

  /salesdb/kschlamb/NODE0000/SQL00001
  /salesdb/kschlamb/NODE0000/sqldbdir

The next step is to create the configuration file and run db2relocatedb.

Configuration File "example3.cfg"

DB_NAME=SALES
DB_PATH=/home/kschlamb,/salesdb
INSTANCE=kschlamb

Command:

  db2relocatedb –f example3.cfg

Example #4: Changing the containers associated with a table space

Database name: FINANCE
Database path: /finance/database
Instance name: fin

Scenario: You have three large file systems called /finance/fs1, /finance/fs2, and /finance/fs3. You intended to create a table space spread across all three file systems by executing the following SQL statement:

  CREATE TABLESPACE BIGTS MANAGED BY SYSTEM USING
    ('/financ/fs1/BIGTS', '/financ/fs2/BIGTS', '/financ/fs3/BIGTS')

What you failed to realize was that you misspelled all of the container names, and rather than using the large file systems you’ve created the containers in your root file system instead (which is usually rather small, and can cause problems if you run out of space). Of course, you finally realize this a month later when you run out of space and you see that you need to move it… fast.

One option is to do a redirected restore, but you’re worried that might take too long. An alternative is to use db2relocatedb.

Using whatever method you wish, you first need to move the following directories from the old location to the new one (remember that we created the table space as SMS – DMS works the same but you would be moving the DMS files, not directories).

  /financ/fs1/BIGTS   =>   /finance/fs1/BIGTS
  /financ/fs2/BIGTS   =>   /finance/fs2/BIGTS
  /financ/fs3/BIGTS   =>   /finance/fs3/BIGTS

The next step is to create the configuration file and run db2relocatedb.

Configuration File "example4.cfg"

DB_NAME=FINANCE
DB_PATH=/finance/database
INSTANCE=fin
CONT_PATH=/financ/fs1/BIGTS,/finance/fs1/BIGTS
CONT_PATH=/financ/fs2/BIGTS,/finance/fs2/BIGTS
CONT_PATH=/financ/fs3/BIGTS,/finance/fs3/BIGTS
#
# We can also use wildcards to represent all of the changes in
# a single command.  Any of the following statements can be
# used instead of the three above:
#
#  CONT_PATH=/financ/fs*,/finance/fs*
#  CONT_PATH=/financ/*,/finance/*
#  CONT_PATH=/financ*,/finance*

Command:

  db2relocatedb –f example4.cfg

Example #5: Moving a database to a new machine (changing instance name, database path, and database name)

Database name: TESTDB
Database path: /testinst_filesystem
Instance name: testinst

Scenario: You’ve created a test database system that you want to move into production on a separate system. In doing this, it is being moved to an instance called prodinst, the database will be called PRODDB, and the database path will be /proddb.

As described in one of the sections above, the following directories were created when the database was originally created:

  /testinst_filesystem/testinst/NODE0000/SQL00001
  /testinst_filesystem/testinst/NODE0000/sqldbdir

The first step is for you to manually copy these two directories to the directories listed below on the production server. Remember, the database path is only part of the directory structure that DB2 uses, so you must follow the same rules when you determine the target location. You can use any method that will result in all directories and files being copied identically.

  /proddb/prodinst/NODE0000/SQL00001
  /proddb/prodinst/NODE0000/sqldbdir

Note: This example assumes that there are no table spaces that exist outside of the database directory. If there are any such table spaces, then those containers must be copied to the new system as well (and if their paths are changing then they need to be listed in the configuration file).

Depending on how you copied the files over, the files may still be owned by the old instance. Use "chown" to change the ownership of these files to be the new instance owner.

The next step is to create the configuration file and run db2relocatedb (this must be done on the production server from within the new instance).

Configuration File "example5.cfg"

DB_NAME=TESTDB,PRODDB
DB_PATH=/testinst_filesystem,/proddb
INSTANCE=testinst,prodinst

Command:

  db2relocatedb –f example5.cfg

Example #6: Complicated example

Database name: TESTDB
Database path: /db2/Databases
Instance name: db2inst

Table spaces were created such that the following directories/files exist:

  /db2/Databases/db2inst/NODE0000/SQL00001/*
  /db2/Databases/db2inst/NODE0000/sqldbdir/*
  /db2/Databases/DMS1
  /db2/Databases/SMS1/*
  /largedir/DMS2
  /largedir/SMS2/*
  /dev/rDMS3

Scenario: The database is being moved such that the database name and path are changing to NEWDB and /dbdirectory respectively. Additionally, the DMS2 and SMS2 containers are to be moved from the /largedir directory to /dbdirectory. DMS1 and SMS1 are to stay within the same relative position under the database path.

With the database shut down, the DBA must manually move the files/directories from the locations listed above to these locations:

  /dbdirectory/db2inst/NODE0000/SQL00001/*
  /dbdirectory/db2inst/NODE0000/sqldbdir/*
  /dbdirectory/DMS1
  /dbdirectory/SMS1/*
  /dbdirectory/DMS2
  /dbdirectory/SMS2/*
  /dev/rDMS3 {no change}

The next step is to create the configuration file and run db2relocatedb. Note that because db2relocatedb assumes that all containers under the old path are moving to the new path, there is no need to have any CONT_PATH entries for DMS1 and SMS1. Therefore, it is only SMS2 and DMS2 that need to be mentioned in the file.

Configuration File "example6.cfg"

DB_NAME=TESTDB,NEWDB
DB_PATH=/db2/Databases,/dbdirectory
INSTANCE=db2inst
CONT_PATH=/largedir/DMS2,/dbdirectory/DMS2
CONT_PATH=/largedir/SMS2,/dbdirectory/SMS2
#
# Or, the following line could have been used instead of
# the two above:
#
#  CONT_PATH=/largedir/*,/dbdirectory/*

Command:

  db2relocatedb –f example6.cfg


Hints, tips, and gotchas

  • The examples that are used throughout this article are UNIX-based. However, they should still apply to Windows-based databases (except that only drive letters are supported for database paths).
  • db2relocatedb is an offline tool that should never be run while the database is up. It runs outside of the database engine and can potentially create havoc to a running system (possibly leading to data corruption and a database that will subsequently not start up).
  • The changes that db2relocatedb makes to a database’s files and control structures are not logged and are therefore not recoverable. Because of this, it is highly suggested that a full backup be taken after running the tool against a database (especially if the database is recoverable with log files being retained).
  • If you are making substantial changes or ones that you feel would be difficult to reverse, you may want to consider taking a database backup prior to making the changes as well.
  • If using DB2’s split/mirror technology and the db2inidb tool, there is a RELOCATE USING option that can be used to call db2relocatedb under the covers during the initialization of a secondary system. This would be useful if creating a secondary system that has a different set of paths associated with it than the primary.
  • Although the tool can be used to change the location of a table space container, it cannot be used to change the number of containers or the size of them (for DMS table spaces). A redirected restore is the only way to accomplish this.
  • If you are moving a database from a database path where more than one database resides, make sure that you copy the sqldbdir directory and not just move it. Remember, DB2 will still need that directory in the old location to be able to find the remaining databases.
  • If you are moving or copying a database from a database path where more than one database resides, you will be carrying along some extra information about those other databases when you copy the sqldbdir directory over. This is fine, but you should be aware that when you do a LIST DB DIRECTORY ON <newPath> you will see those other databases listed even though they don’t exist there. These entries in the directory have essentially been orphaned and cannot be removed. What this means is that you cannot create any databases under this same path that has those names. However, you can create databases with those names on a different path.
  • There is nothing to stop you from moving all of the databases from one path to another; you just need to create a configuration file for each database and run db2relocatedb for each one.
  • You can use db2relocatedb to move DMS table space containers that are situated on raw devices. However, you cannot use OS commands like cp or mv on raw devices, so you must use something that can read and write binary data from them (such as the UNIX dd command). Be careful though; the OS usually writes its own metadata to the beginning of a raw device, and you don’t want to copy that. DB2 places its data starting at the 512 byte offset (1024 on HP-UX) so this is where you should start copying from in the old location, and where you should start writing to in the new location.
  • If you move a database and forget to move a container, or forget to have an entry for it in the file (if the location is changing), you will still be able to connect to the database. Be sure to do a LIST TABLESPACES SHOW DETAIL to look at the state for all of the table spaces and make sure that none of them are OFFLINE. This indicates that DB2 wasn’t able to locate one or more of its containers. In this case, shut the database down, fix the problem, and run the tool again (the tool is smart enough to know when it has already done some of the work so running it a second time should not harm anything).

Conclusion

The db2relocatedb tool is a powerful tool for giving the DBA control over some of the DB2 metadata in order to make it easier to perform functions such as moving or renaming databases without requiring major downtime. As DB2 databases grow larger, capabilities like this make the life of a DBA just a little bit easier. Don't forget the gotchas, and you'll find db2relocatedb to be a useful tool indeed.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=14612
ArticleTitle=The db2relocatedb tool
publish-date=06232004