How to use db2move and db2look to duplicate IBM DB2 database

Objective: use db2 control center to create another testing database and tablespace, then use db2look and db2move to export then import the production database data into this testing database.
Environment: RHEL5, db2 Informational tokens are "DB2 v9.1.0.3", "s070719", "MI00202", and Fix Pack "3". Database name is DB1, the os and schema user name is user1


1. Install same version of IBM db2 database on testing server

note: Setup database runtime client side parameters, such as db2 node directory and db directory

db2 ? catalog node
db2 ? catalog db

db2 catalog tcpip node db1 remote server 50000
db2 catalog db db1 at node db1

db2 list node directory
db2 list db directory

2. Create a new database called DB1 also then create tablespace tb_user1 and OS user user1, please refer to the article

3. Use db2look to generate DDL statements for later use
db2look -d db1 -e -z user1 -o user1.sql

Note: for db2look, you might need to separate the file into 2 files called db2look-1.sql and db2look-2.sql. db2look-2.sql contains those ‘adding constrain’ statements.
Also take note the schema name and tablespace name might need to be changed also.

4. Preparing table and view granting script

cat db2look.sql | grep 'CREATE TABLE' | awk -F\" '{print $4}' > tablelist
cat db2look.sql | grep 'CREATE VIEW' | awk '{print $3}' | sed -e 's#(.*##g' > viewlist

after that, generate the 2 files which content such as this:
grant select,insert,update,delete,alter,index,references on table STUDENT to user1 ;
and this:
grant select,insert,update,delete on to user1 ;

5. use db2move to export the schema
Run the following db2move command on production database server:

db2move db1 export -sn user1

6. Run the first part of db2look-1.sql to create tables and views first
7. Use command db2move to import/load the content:

db2move db1 import

1. You can modify db2move.lst file to exclude certain unnecessary big tables.
2. Actually db2move is a wrapper of db2 “import xxxx” or db2 “load xxxx”, you just don’t have to type so many times for importing/loading each table, use db2move instead.

When you use db2move to import db2 version 9 Linux db2move dump to solaris db2 version 8, you might encounter codepage error like this:

Code page option is incompatible with the lobsinfile option

This is because you use db2move ‘import’ option, if you use db2move ‘load’ option, then the problem will be solved.

Search: db2move load import lobsinfile incompatible

Problem encountered during using db2move load to load tables

Search: db2move load –lo replace ,insert

Due to tablespace has no free pages left. During db2move load operation period, it failed with the following errors:

You can use the following command to verify that:
# db2 “list tablespaces show detail”

LOAD: table " ".""
*** ERROR -289. Check message file tab365.msg!
*** SQLCODE: -289 - SQLSTATE: 57011
*** SQL0289N Unable to allocate new pages in table space "". SQLSTATE=57011

Note: this error might also happen during db2look period also:

After failed to load, then you cannot load again, you have to clear it using the following command:
Otherwise, the error message for accessing those fail-loaded tables are :

SQL0668N operation not allowed for reason code “3” on table “xxxx.yyyy”. SQLSTATE=57016

# db2 “load from /dev/null of ixf replace into .tablename [nonrecoverable]”

Regarding how to recover from a failed LOAD operation in DB2, you can refer to article

Recovering from a failed LOAD operation in DB2 for Linux, UNIX and Windows

8. After importing data, import the second db2look-2.sql script to enable constrain

9. Test. You can test the connection from client to server from runtime client application server.
db2 connect to db1 user user1