db2relocatedb - Relocate database command

This command renames a database, or relocates a database or part of a database (for example, the container and the log directory) as specified in the configuration file provided by the user. This tool makes the necessary changes to the Db2® instance and database support files.

The target database must be offline before running the db2relocatedb command to modify the control files and metadata of the target database.

The changes that the db2relocatedb command makes to files and control structures of a database are not logged and are therefore not recoverable. A good practice is to make a full backup after running the command against a database, especially if the database is recoverable with log files that are retained.

Authorization

None

Prerequisite

If automatic storage for the database is enabled, you must move the data from each storage path to a new location by using the following command:

$ mv old_storage_path_N/inst_name/NODE0000/X/ old_storage_path_N/inst_name/NODE0000/Y

where old_storage_path_N represents the old storage path name, inst_name represents the instance name, X represents the old database name and Y represents the new database name.

Run this step to help ensure that the db2relocatedb command runs without generating an error message.

Command syntax

Command parameters

-f configFilename

Specifies the name of the file that contains the configuration information necessary for relocating the database. This command parameter can be a relative or absolute file name. The format of the configuration file 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
  ...
  STORAGE_PATH=oldStoragePath1,newStoragePath1
  STORAGE_PATH=oldStoragePath2,newStoragePath2
  ...
  FAILARCHIVE_PATH=newDirPath
  LOGARCHMETH1=newDirPath
  LOGARCHMETH2=newDirPath
  MIRRORLOG_PATH=newDirPath
  OVERFLOWLOG_PATH=newDirPath
  ...

Where:

DB_NAME

Specifies the name of the database that is relocated. If the database name is being changed, both the old name and the new name must be definitely specified.

Note: DB_NAME changes are not supported for databases with remote storage table spaces.

DB_PATH

Specifies the original path of the database that is relocated. If the database path is changing, both the old path and new path must be definitely specified.

INSTANCE

Specifies the instance where the database exists. If the database is being moved to a new instance, both the old instance and new instance must be definitely specified.

Warning: The new instance must be at the same release level as the instance where the database currently resides.

NODENUM

Specifies the node number for the database node that is changed. The default is 0.

LOG_DIR

Specifies a change in the location of the log path. If the log path is being changed, both the old path and new path must be specified. This specification is optional if the log path is under the database path, in which case the path is updated automatically.

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 multiple container path changes are to be made. This specification is optional if the container paths are under the database path, in which case the paths are updated automatically. If you are changing more than one container where the same old path is replaced with a common new path, a single CONT_PATH entry can be used. In such a case, an asterisk (*) must be used in both the old and new paths as a wildcard.

Note: This parameter does not support remote storage aliases.

STORAGE_PATH

Specifies a change in the location of one of the storage paths for the database. Both the old storage path and the new storage path must be specified. Multiple STORAGE_PATH lines can be given if there are several storage path changes to be made. You can specify this parameter to modify any storage path in all storage groups. However, you cannot specify this parameter to modify the storage paths for an individual storage group.

Note:

This parameter is not applicable to a database created with the AUTOMATIC STORAGE NO clause. Although you can create a database specifying the AUTOMATIC STORAGE NO clause, the AUTOMATIC STORAGE clause is deprecated and might be removed from a future release.
This parameter does not support remote storage aliases.

FAILARCHIVE_PATH

Specifies a new location to archive log files if the database manager fails to archive the log files to either the primary or the secondary archive locations. Specify this field only if the database that is relocated has the failarchpath configuration parameter set.

LOGARCHMETH1

Specifies a new primary archive location. Specify this field only if the database that is relocated has the logarchmeth1 configuration parameter set.

LOGARCHMETH2

Specifies a new secondary archive location. Specify this field only if the database that is relocated has the logarchmeth2 configuration parameter set.

MIRRORLOG_PATH

Specifies a new location for the mirror log path. The string must point to a path name, and it must be a fully qualified path name, not a relative path name. Specify this field only if the database that is relocated has the mirrorlogpath configuration parameter set.

OVERFLOWLOG_PATH

Specifies a new location to find log files that are required for a rollforward operation to store active log files retrieved from the archive, and to find and store log files that are required by the db2ReadLog API. Specify this field only if the database that is relocated has the overflowlogpath configuration parameter set.

Blank lines or lines beginning with a comment character (#) are ignored.

-g configFilename

Generates a configuration file and specifies the name of the file containing the configuration information. This can be a relative or absolute file name. Without the option -r, the output looks as follows:

  DB_NAME=oldName,oldName
  DB_PATH=oldPath,oldPath
  INSTANCE=oldInst,oldInst
  NODENUM=nodeNumber
  LOG_DIR=oldDirPath,oldDirPath
  CONT_PATH=oldContPath1,oldContPath1
  CONT_PATH=oldContPath2,oldContPath2
  ...
  STORAGE_PATH=oldStoragePath1,oldStoragePath1
  STORAGE_PATH=oldStoragePath2,oldStoragePath2
  ...
  FAILARCHIVE_PATH=oldDirPath
  LOGARCHMETH1=oldDirPath
  LOGARCHMETH2=oldDirPath
  MIRRORLOG_PATH=oldDirPath
  OVERFLOWLOG_PATH=oldDirPath
  ...

-d databaseName

Specifies the database name for which the file must be generated.

-r replaceDefinition

With this option, you replace strings in the generated script. Parameter replaceDefinition must have the format regularExpression=replacement. See the following examples.

Examples

Example 1

To change the name of the database TESTDB to PRODDB in the instance db2inst1 that is on the path /home/db2inst1, create the following configuration file:

 
  DB_NAME=TESTDB,PRODDB
  DB_PATH=/home/db2inst1
  INSTANCE=db2inst1
  NODENUM=0

When the configuration file is created, you must alter any automatic storage paths to match the new database name:

rename /home/db2inst1/db2inst1/TESTDB /home/db2inst1/db2inst1/PRODDB

Save the configuration file as relocate.cfg and use the following command to change the database files:

  db2relocatedb -f relocate.cfg

Example 2

To move the database DATAB1 from the instance jsmith on the path /dbpath to the instance prodinst do the following:

Copy the files in the directory /dbpath/jsmith to /dbpath/prodinst.
Use the following configuration file with the db2relocatedb command to change the database files:
```
  DB_NAME=DATAB1
  DB_PATH=/dbpath
  INSTANCE=jsmith,prodinst
  NODENUM=0
```
See Usage notes section for more details.

Example 3

The database PRODDB exists in the instance inst1 on the path /databases/PRODDB. The location of two table space containers needs to be changed as follows:

SMS container /data/SMS1 needs to be moved to /DATA/NewSMS1.
DMS container /data/DMS1 needs to be moved to /DATA/DMS1.

After the physical directories and files are moved to the new locations, the following configuration file can be used with the db2relocatedb command to change the database files so that they recognize the new locations:

  DB_NAME=PRODDB
  DB_PATH=/databases/PRODDB
  INSTANCE=inst1
  NODENUM=0
  CONT_PATH=/data/SMS1,/DATA/NewSMS1
  CONT_PATH=/data/DMS1,/DATA/DMS1

Example 4

The database TESTDB exists in the instance db2inst1 and was created on the path /databases/TESTDB. Table spaces were then created with the following containers:

  TS1
  TS2_Cont0
  TS2_Cont1
  /databases/TESTDB/TS3_Cont0
  /databases/TESTDB/TS4/Cont0
  /Data/TS5_Cont0
  /dev/rTS5_Cont1

TESTDB is to be moved to a new system. The instance on the new system is newinst and the location of the database is /DB2.

When moving the database, all files that exist in the /databases/TESTDB/db2inst1 directory must be moved to the /DB2/newinst directory. This means that the first 5 containers are relocated as part of this move. (The first 3 are relative to the database directory and the next 2 are relative to the database path.) Since these containers are located within the database directory or database path, they do not need to be listed in the configuration file. If the 2 remaining containers are to be moved to different locations on the new system, they must be listed in the configuration file.

After the physical directories and files have been moved to their new locations, the following configuration file can be used with db2relocatedb to change the database files so that they recognize the new locations:

  DB_NAME=TESTDB
  DB_PATH=/databases/TESTDB,/DB2
  INSTANCE=db2inst1,newinst
  NODENUM=0
  CONT_PATH=/Data/TS5_Cont0,/DB2/TESTDB/TS5_Cont0
  CONT_PATH=/dev/rTS5_Cont1,/dev/rTESTDB_TS5_Cont1

Example 5

The database TESTDB has two database partitions on database partition servers 10 and 20. The instance is servinst and the database path is /home/servinst on both database partition servers. The name of the database is being changed to SERVDB and the database path is being changed to /databases on both database partition servers. In addition, the log directory is being changed on database partition server 20 from /testdb_logdir to /servdb_logdir.

Since changes are being made to both database partitions, a configuration file must be created for each database partition and db2relocatedb must be run on each database partition server with the corresponding configuration file.

On database partition server 10, the following configuration file is used:

  DB_NAME=TESTDB,SERVDB
  DB_PATH=/home/servinst,/databases
  INSTANCE=servinst
  NODENUM=10

On database partition server 20, the following configuration file is used:

  DB_NAME=TESTDB,SERVDB
  DB_PATH=/home/servinst,/databases
  INSTANCE=servinst
  NODENUM=20
  LOG_DIR=/testdb_logdir,/servdb_logdir

Example 6

The database MAINDB exists in the instance maininst on the path /home/maininst. The location of four table space containers needs to be changed as follows:

 
  /maininst_files/allconts/C0 needs to be moved to /MAINDB/C0
  /maininst_files/allconts/C1 needs to be moved to /MAINDB/C1
  /maininst_files/allconts/C2 needs to be moved to /MAINDB/C2
  /maininst_files/allconts/C3 needs to be moved to /MAINDB/C3

A similar change is being made to all of the containers; that is, /maininst_files/allconts/ is being replaced by /MAINDB/ so that a single entry with the wildcard character can be used:

  DB_NAME=MAINDB
  DB_PATH=/home/maininst
  INSTANCE=maininst
  NODENUM=0
  CONT_PATH=/maininst_files/allconts/*, /MAINDB/*

Example 7

The database MULTIDB exists in the instance inst1 on the path /database/MULTIDB . The partitioned storage path '/home/olddbpath $N' needs to be changed to '/home/newdbpath $N'.

To be able to correctly move the partitioned storage path, the parameterized storage path need to be specified in the STORAGE_PATH field with double quotation mark around it. After the physical directories and files are moved to the new locations, the following configuration file can be used with the db2relocatedb command to change the database files so that they recognize the new locations.

  DB_NAME=MULTIDB
  DB_PATH=/database/MULTIDB
  INSTANCE=inst1
  NODENUM=0
  STORAGE_PATH="/home/olddbpath $N" , "/home/newdbpath $N"

Example 8

The database PRD exists in the instance db2prd on the database path /db2/PRD and storage paths /db2/PRD/sapdata1 and /db2/PRD/sapdata2. To generate an unmodified script, use the following command that creates the output file relocate.cfg:


db2relocatedb -g relocate.cfg -d PRD

The contents of the output file relocate.cfg look as follows:


DB_NAME=PRD,PRD
DB_PATH=/db2/PRD,/db2/PRD
INSTANCE=db2prd,db2prd
NODENUM=0
STORAGE_PATH=/db2/PRD/sapdata1,/db2/PRD/sapdata1
STORAGE_PATH=/db2/PRD/sapdata2,/db2/PRD/sapdata2

If you want to relocate this database to change the database name to QAS to use the instance db2qas, and to change the autostorage paths accordingly, you can use the following command:


db2relocatedb -g relocate.cfg -d PRD -r PRD=QAS,db2prd=db2qas

The contents of the output file relocate.cfg look as follows:


DB_NAME=PRD,QAS
DB_PATH=/db2/PRD,/db2/QAS
INSTANCE=db2prd,db2qas
NODENUM=0
STORAGE_PATH=/db2/PRD/sapdata1,/db2/QAS/sapdata1
STORAGE_PATH=/db2/PRD/sapdata2,/db2/QAS/sapdata2

Usage notes

If the instance that a database belongs to is changing, the following must be done before running this command to help ensure that changes to the instance and database support files are made:

If a database is being moved to another instance, create the new instance. The new instance must be at the same release level as the instance where the database currently is in.
If the new instance has a different owner than the current instance, grant access to the new instance owner.
Copy the files and devices belonging to the databases being copied onto the system where the new instance resides. The path names must be changed as necessary. However, if there are already databases in the directory where the database files are moved to, you can mistakenly overwrite the existing sqldbdir file, thereby removing the references to the existing databases. In this scenario, the db2relocatedb utility cannot be used. Instead of db2relocatedb, an alternative is a redirected restore operation.
Change the permission of the files or devices that were copied so that they are owned by the instance owner.

cd
chown -R ./NODE0000 ?

When a database is moved from a database path where more than one database exists, the sqldbdir directory within that database path must be copied and not moved. This directory is still needed in the old location for Db2 to locate the databases that are not moving. After copying the sqldbdir directory to the new location, a LIST DB DIRECTORY ON newPath command lists databases that were not moved. These references cannot be removed and new databases with those names cannot be created on this same path. However, databases can be created with those names on a different path.

The db2relocatedb command cannot be used to move existing user-created containers for a table space that was converted to use automatic storage by using the ALTER TABLESPACE MANAGED BY AUTOMATIC STORAGE statement.

If the instance is changing, the new instance owner must run the command.

In a partitioned database environment, this tool must be run against every database partition that requires changes. A separate configuration file must be supplied for each database partition that includes the NODENUM value of the database partition being changed. For example, if the name of a database is changed, every database partition get affected and the db2relocatedb command must be run with a separate configuration file on each database partition. If containers belonging to a single database partition are being moved, the db2relocatedb command only needs to be run once on that database partition.

You cannot use the db2relocatedb command to relocate a database that has a load in progress or is waiting for the completion of a LOAD RESTART or LOAD TERMINATE command.

After you run the db2relocatedb command, you must recycle the Db2 instance to allow the changes to take effect. To recycle the Db2 instance, perform the following steps:

Issue the db2stop command.
Issue the db2start command.

Limitation: In a partitioned database environment, you cannot relocate an entire node if that node is one of two or more logical partitions that are on the same device.

If the ctrl_file_recov_path configuration parameter was configured, then after the completion of the db2relocatedb operation, the database activation may fail with an SQL1051N error. This error is because the existing control file copies cannot be found within the specified ctrl_file_recov_path (specifically because the path’s subdirectories of instance owner, or partition-global number, or member-specific number (<PATH>/<instance_owner>/NODExxxx/SQLyyyyy/MEMBERzzzz/) may differ on the relocated database). Before the database is activated, help ensure that the ctrl_file_recov_path either specifies a new path (where the control file copies are initialized into), or is disabled all together.