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

Steps:

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 10.0.0.10 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 http://linuxtechres.blogspot.com/2010/01/how-to-setup-new-schema-in-db2-database.html

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

note:
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
At http://www.ibm.com/developerworks/data/library/techarticle/0202kline.html

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