DB2的db2look和db2move的使用

我工作时碰到了一个需要把一个老的DB2数据库给拆分，并数据也得移植，我首先想到的是将数据库进行备份，并重定向恢复成另一个数据库，但是尝试了多次也为成功，于是就只能想想最简单的方法了export导出import导入。

一、[root@localhost ~]# su - db2inst1
在当前用户目录下创建backup目录
[db2inst1@localhost ~]$ mkdir backup
[db2inst1@localhost ~]$ cd backup/
[db2inst1@localhost ~]$ pwd
/home/db2inst1
[db2inst1@localhost ~]$ db2 connect to db_name
导出数据结构
[db2inst1@localhost ~]$ db2look -d db_name -a -e -o /home/db2inst1/backup/script.sql


二、创建一个新数据库
[db2inst1@localhost ~]$ db2 create database NewDataBase on '/home/db2inst1' using codeset UTF-8 territory CN
将script.sql文件取到桌面
将script.sql文件上方连接数据库改为NewDataBase
将script.sql文件最下方与外键相关的SQL单独摘出来另存foreign.sql文件夹中
-- DDL Statements for foreign keys on Table "DB2INST1"."ROLE_PRIVILEGE_INFO"
ALTER TABLE "DB2INST1"."ROLE_PRIVILEGE_INFO" 
ADD CONSTRAINT "F_REFERENCE_15" FOREIGN KEY
("PRIVILEGE_ID")
REFERENCES "DB2INST1"."PRIVILEGE_INFO"
("PRIVILEGE_ID")
ON DELETE RESTRICT
ON UPDATE RESTRICT
ENFORCED
ENABLE QUERY OPTIMIZATION;
-- DDL Statements for foreign keys on Table "DB2INST1"."ROLE_PRIVILEGE_INFO"
ALTER TABLE "DB2INST1"."ROLE_PRIVILEGE_INFO" 
ADD CONSTRAINT "F_REFERENCE_15" FOREIGN KEY
("PRIVILEGE_ID")
REFERENCES "DB2INST1"."PRIVILEGE_INFO"
("PRIVILEGE_ID")
ON DELETE RESTRICT
ON UPDATE RESTRICT
ENFORCED
ENABLE QUERY OPTIMIZATION;
......
使用dbvis连接工具连接NewDataBas数据库执行script.sql脚本中的sql


三、导出数据
[db2inst1@localhost backup]$ mkdir data
导出需要的表的数据
[db2inst1@localhost backup]$ db2 "export to  /home/db2inst1/backup/data/tab1.ixf of ixf lobs to  /home/db2inst1/backup/data/ lobfile lobs modified  by lobsinsepfiles  messages /home/db2inst1/backup/data/tab1.msg select * from  DB2INST1.CONFIG_INFO" 
[db2inst1@localhost backup]$ db2 "export to  /home/db2inst1/backup/data/tab2.ixf of ixf lobs to  /home/db2inst1/backup/data/ lobfile lobs modified  by lobsinsepfiles  messages /home/db2inst1/backup/data/tab2.msg select * from  DB2INST1.DICT_INFO" 
.....
四、导入表数据
[db2inst1@localhost ~]$ db2 connect to NewDataBase
导入对应的表的表数据
[db2inst1@localhost backup]$ db2 "import from /home/db2inst1/backup/data/tab1.ixf of ixf messages /home/db2inst1/backup/data/tab1.msg insert into DB2INST1.CONFIG_INFO"
[db2inst1@localhost backup]$ db2 "import from /home/db2inst1/backup/data/tab2.ixf of ixf messages /home/db2inst1/backup/data/tab2.msg insert into DB2INST1.DICT_INFO"
.....
五、数据导完后执行外键的SQL
使用dbvis连接工具连接NewDataBas数据库执行foreign.sql脚本中的sql


另外：
用db2move导出所有表的数据       db2move db_name export -u user_name -p password

执行sql脚本      db2 -tvf d:\script.sql -z d:\script.log        

用db2move导入所有表的数据       db2move db_name import -io replace -u user_name -p password