0

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?

  • Any reason you don't use backup and restore? – mustaccio Aug 3 '18 at 14:30
  • @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. – Alexander Pozdneev Aug 4 '18 at 12:31
  • 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. – mustaccio Aug 4 '18 at 13:07
  • 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. – Lennart Aug 6 '18 at 19:05
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
  • 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 Aug 15 '18 at 12:38
  • Keep in mind, that a new database is created by the db2 restore database command; you do not need to db2 create db explicitly. – Alexander Pozdneev Aug 15 '18 at 12:40
1

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

0

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" 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.