Cloning a Db2 database within a single instance

Question

I need to create an exact copy of a Db2 database (V11.1+) on the same server within the same instance. (This is a development server that does not run production databases.)

This is how I do it now.

1. Dump tables using a hand-written script
2. Create a new database
3. Import service functions specific to my application to the database
4. Create a schema in the new database
5. Import data from a dump into the new database
6. Set database configuration parameters for the new database

I read that it is possible to copy a database scheme with db2move. Is there a simple approach that could be used to perform a full copy?

• mustaccio: Any reason you don't use backup and restore?
• Alexander Pozdneev: @mustaccio, I believe, that could work for me. Could you please post the needed commands as an answer, as I am not sure I fully understand the semantics of the db2 backup and db2 restore command options.
• mustaccio: The commands are described in the manual in great detail. You probably need to pay particular attention to the redirected restore process. If you encounter problems, please post details of the commands you run and error messages you get.
• Lennart - Slava Ukraini: Backup / restore is easiesr and faster than the method you describe since you are on the same architecture and version. Is the database you are trying to copy created in V11? If so you probably have automatic storage, which will make it real easy to restore a backup into a new database.

Answer 1

This is a minimial example of copying a database (test) to a into a new database (test2)

db2 create database test
db2 backup database test to /tmp
db2 restore database test from /tmp into test2
db2 connect to test2
 Database Connection Information
 Database server = DB2/LINUXX8664 11.1.3.3
 SQL authorization ID = DB2INST1
 Local database alias = TEST2

• Alexander Pozdneev: If you get SQL1035N The operation failed because the specified database cannot be connected to in the mode requested. SQLSTATE=57019, you might need to db2 force applications all.
• Alexander Pozdneev: Keep in mind, that a new database is created by the db2 restore database command; you do not need to db2 create db explicitly.

Answer 2

This is another example of copying a database, this time using named pipes to avoid landing the backup image onto the file-system

$ db2 create database test
$ mkfifo /tmp/pipe1 /tmp/pipe2
$ db2 restore database test from /tmp/pipe1, /tmp/pipe2 into test2 &
[1] 20744
$ bash
$ db2 backup database test to /tmp/pipe1, /tmp/pipe2
Backup successful. The timestamp for this backup image is : 20180815161749
DB20000I The RESTORE DATABASE command completed successfully.
[1]+ Done db2 restore database test from /tmp/pipe1, /tmp/pipe2 into test2

Note that the backup needs to run in a different shell session as the Db2 CLP can't run two things using the same CLP backend process. Also note that if you are not the instance owner, you will need to chmod o+w the pipes Also, you could of course ssh the piped data to another machine! And it works for online backups too (above is an offline backup)

You can use as many, or as few pipes as you wish. See this for an discussion of performance advantages of using more than one https://www.ibm.com/developerworks/community/blogs/IMSupport/entry/DB2_Backup_performance_compression_tablespace_imbalance?lang=en

Answer 3

An example using ssh to send the backup image to a remote machine

mkfifo /tmp/localpipe
chmod o+w /tmp/localpipe
ssh remotehost "mkfifo /tmp/remotepipe;chmod o+w /tmp/remotepipe"
db2 backup database test to /tmp/localpipe &
ssh remotehost "cat > /tmp/remotepipe" < /tmp/localpipe &
ssh remotehost "db2 restore database test from /tmp/remotepipe into test2"