本文详细介绍了如何将DB2的SAMPLE数据库中的数据迁移到新创建的MYDB1数据库中，包括使用db2look生成DDL文件复制表结构，以及使用export和load命令迁移数据。在迁移过程中，还提到了可能遇到的问题和解决方案，如主外键的完整性检查。
本文著作权属于我之前的同事 - 罗建峰，非常感谢他之前给我写的这封技术流邮件。 特此整理如下。
---------------------------------------------------------------------------我是可爱的分割线---------------------------------------------------------------------------------------------------------------------
SAMPLE 数据库是安装DB2的时候创建的，里面包括一些表，表空间等等的东西，MYDB1是新建的数据库，是没有任何东西的。SAMPLE是源数据库，MYDB1是目标数据库
一. 复制个表格的表结构，这里是通过备份整个数据库的DDL来实现。
Sample数据库里的表
MYDB1数据库里面的表
1. 创建生成DDL文件
命令：db2look -d <dbname> -e -nofed -o sample.sql （sample.sql是自己取的名字。另外这个可以有很多参数，可以用db2look -h查看各个参数，如果是sever的要加上-i USERID -w PASSWORD) 这个我是猜的。
将<dbname>替换成源数据库的名称，<tableN>替换成具体的表名，多个表之间用空格分开
例子：db2look -d SAMPLE -e -nofed -o sample.sql
运行结果：
2. 我的是window版本的DB2，可能命令和LINUX会有点不同，我刚才遇到一个问题就是找不到生成的DDL文件，要在BIN里面创建一个文件夹DATA，然后cd到DATA里面去输入DB2LOOK的命令，然后sample.sql就在DATA文件夹里面了。
实例截图：
sample.sql里面的内容
-- 此 CLP 文件是使用 DB2LOOK 版本 "10.1" 创建的
-- 时间戳记: 2014/5/30 20:48:08
-- 数据库名称: SAMPLE
-- 数据库管理器版本: DB2/NT64 Version 10.1.0
-- 数据库代码页: 1208
-- 数据库整理顺序为: IDENTITY
--（这里的DDL是链接到SAMPLE数据库上的，所以这里要修改一下链接的数据库，将其改为目标数据库MYDB1，有必要的话还可能要修改tablespace的名字，其实这些DDL就免去了你要创建表一个一个SQL的打，里面的内容按照你自己需求要改一下就可以了，我没有截完所有内容）
CONNECT TO SAMPLE;
------------------------------------------------
-- 表的 DDL 语句 "TEST "."CL_SCHED"
(TEST是我本地数据库的ID名字，你在sever上面导出的DDL可能那个schema会有很多，都将他改为你的ID名字就可以了，然后你就对所有表都有权限了，标红的部分，我因为是本地测试，所以不用改，这个DDL下面有很多内容，我没有复制全给你）
------------------------------------------------
CREATE TABLE "TEST "."CL_SCHED" (
"CLASS_CODE" CHAR(7) ,
"DAY" SMALLINT ,
"STARTING" TIME ,
"ENDING" TIME )
IN "USERSPACE1" ;
------------------------------------------------
-- 表的 DDL 语句 "TEST "."DEPARTMENT"
------------------------------------------------
CREATE TABLE "TEST "."DEPARTMENT" (
"DEPTNO" CHAR(3) NOT NULL ,
"DEPTNAME" VARCHAR(36) NOT NULL ,
"MGRNO" CHAR(6) ,
"ADMRDEPT" CHAR(3) NOT NULL ,
"LOCATION" CHAR(16) )
IN "USERSPACE1" ;
-- 表上主键的 DDL 语句 "TEST "."DEPARTMENT"
ALTER TABLE "TEST "."DEPARTMENT"
ADD CONSTRAINT "PK_DEPARTMENT" PRIMARY KEY
("DEPTNO");
-- 表上的索引的 DDL 语句 "TEST "."DEPARTMENT"
CREATE INDEX "TEST "."XDEPT2" ON "TEST "."DEPARTMENT"
("MGRNO" ASC)
COMPRESS NO ALLOW REVERSE SCANS;
-- 表上的索引的 DDL 语句 "TEST "."DEPARTMENT"
CREATE INDEX "TEST "."XDEPT3" ON "TEST "."DEPARTMENT"
("ADMRDEPT" ASC)
COMPRESS NO ALLOW REVERSE SCANS;
-- 基于表的别名的 DDL 语句 "TEST "."DEPARTMENT"
CREATE ALIAS "TEST "."DEPT" FOR TABLE "TEST "."DEPARTMENT";
------------------------------------------------
-- 表的 DDL 语句 "TEST "."ACT"
------------------------------------------------
CREATE TABLE "TEST "."ACT" (
"ACTNO" SMALLINT NOT NULL ,
"ACTKWD" CHAR(6) NOT NULL ,
"ACTDESC" VARCHAR(20) NOT NULL )
IN "USERSPACE1" ;
-- 表上主键的 DDL 语句 "TEST "."ACT"
ALTER TABLE "TEST "."ACT"
ADD CONSTRAINT "PK_ACT" PRIMARY KEY
("ACTNO");
-- 表上的索引的 DDL 语句 "TEST "."ACT"
CREATE UNIQUE INDEX "TEST "."XACT2" ON "TEST "."ACT"
("ACTNO" ASC,
"ACTKWD" ASC)
COMPRESS NO ALLOW REVERSE SCANS;
------------------------------------------------
-- 表的 DDL 语句 "TEST "."ADEFUSR"
------------------------------------------------
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE SUMMARY TABLE ADEFUSR AS (SELECT workdept, count(*) AS no_of_employees FROM employee GROUP BY workdept) DATA INITIALLY DEFERRED REFRESH IMMEDIATE ENABLE QUERY OPTIMIZATION MAINTAINED BY SYSTEM IN "USERSPACE1" ;
ALTER TABLE "TEST "."ADEFUSR" DEACTIVATE ROW ACCESS CONTROL;
------------------------------------------------
-- 表的 DDL 语句 "TEST "."CUSTOMER"
------------------------------------------------
CREATE TABLE "TEST "."CUSTOMER" (
"CID" BIGINT NOT NULL ,
"INFO" XML ,
"HISTORY" XML )
IN "IBMDB2SAMPLEXML" ;
-- 表上主键的 DDL 语句 "TEST "."CUSTOMER"
ALTER TABLE "TEST "."CUSTOMER"
ADD CONSTRAINT "PK_CUSTOMER" PRIMARY KEY
("CID");
-- 表上的索引的 DDL 语句 "TEST "."CUSTOMER"
CREATE UNIQUE INDEX "TEST "."CUST_CID_XMLIDX" ON "TEST "."CUSTOMER"
("INFO" ASC)
GENERATE KEY USING XMLPATTERN '/customerinfo/@Cid'
AS SQL DOUBLE IGNORE INVALID VALUES
COMPRESS NO ALLOW REVERSE SCANS;
-- 表上的索引的 DDL 语句 "TEST "."CUSTOMER"
CREATE INDEX "TEST "."CUST_NAME_XMLIDX" ON "TEST "."CUSTOMER"
("INFO" ASC)
GENERATE KEY USING XMLPATTERN '/customerinfo/name'
AS SQL VARCHAR ( 50 ) IGNORE INVALID VALUES
COMPRESS NO ALLOW REVERSE SCANS;
-- 表上的索引的 DDL 语句 "TEST "."CUSTOMER"
CREATE INDEX "TEST "."CUST_PHONES_XMLIDX" ON "TEST "."CUSTOMER"
("INFO" ASC)
GENERATE KEY USING XMLPATTERN '/customerinfo/phone'
AS SQL VARCHAR ( 25 ) IGNORE INVALID VALUES
COMPRESS NO ALLOW REVERSE SCANS;
-- 表上的索引的 DDL 语句 "TEST "."CUSTOMER"
CREATE INDEX "TEST "."CUST_PHONET_XMLIDX" ON "TEST "."CUSTOMER"
("INFO" ASC)
GENERATE KEY USING XMLPATTERN '/customerinfo/phone/@type'
AS SQL VARCHAR ( 25 ) IGNORE INVALID VALUES
COMPRESS NO ALLOW REVERSE SCANS;
------------------------------------------------
-- 表的 DDL 语句 "TEST "."CATALOG"
------------------------------------------------
CREATE TABLE "TEST "."CATALOG" (
"NAME" VARCHAR(128) NOT NULL ,
"CATLOG" XML )
COMPRESS YES ADAPTIVE
IN "IBMDB2SAMPLEXML" ;
-- 表上主键的 DDL 语句 "TEST "."CATALOG"
ALTER TABLE "TEST "."CATALOG"
ADD CONSTRAINT "PK_CATALOG" PRIMARY KEY
("NAME");
-- 指定的表名不正确
-- 表上的外键的 DDL 语句 "TEST "."DEPARTMENT"
ALTER TABLE "TEST "."DEPARTMENT"
ADD CONSTRAINT "RDE" FOREIGN KEY
("MGRNO")
REFERENCES "TEST "."EMPLOYEE"
("EMPNO")
ON DELETE SET NULL
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
ALTER TABLE "TEST "."DEPARTMENT"
ADD CONSTRAINT "ROD" FOREIGN KEY
("ADMRDEPT")
REFERENCES "TEST "."DEPARTMENT"
("DEPTNO")
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
-- 表上的外键的 DDL 语句 "TEST "."ACT"
ALTER TABLE "TEST "."ACT"
ADD CONSTRAINT "RPAA" FOREIGN KEY
("ACTNO")
REFERENCES "TEST "."ACT"
("ACTNO")
ON DELETE RESTRICT
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
----------------------------
-- 视图的 DDL 语句
----------------------------
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VDEPT AS SELECT ALL DEPTNO, DEPTNAME, MGRNO, ADMRDEPT
FROM DEPT;
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VHDEPT AS SELECT ALL DEPTNO , DEPTNAME, MGRNO , ADMRDEPT,
LOCATION FROM DEPT;
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VEMP AS SELECT ALL EMPNO , FIRSTNME, MIDINIT , LASTNAME,
WORKDEPT FROM EMP;
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VACT AS SELECT ALL ACTNO , ACTKWD , ACTDESC FROM ACT;
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VDEPMG1 (DEPTNO, DEPTNAME, MGRNO, FIRSTNME, MIDINIT, LASTNAME,
ADMRDEPT) AS SELECT ALL DEPTNO, DEPTNAME, EMPNO, FIRSTNME, MIDINIT, LASTNAME,
ADMRDEPT FROM DEPT LEFT OUTER JOIN EMP ON MGRNO = EMPNO;
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VEMPDPT1 (DEPTNO, DEPTNAME, EMPNO, FRSTINIT, MIDINIT, LASTNAME,
WORKDEPT) AS SELECT ALL DEPTNO, DEPTNAME, EMPNO, SUBSTR(FIRSTNME, 1, 1),
MIDINIT, LASTNAME, WORKDEPT FROM DEPT RIGHT OUTER JOIN EMP ON WORKDEPT
= DEPTNO;
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VASTRDE2 (DEPT1NO,DEPT1NAM,EMP1NO,EMP1FN,EMP1MI,EMP1LN,TYPE2,
DEPT2NO,DEPT2NAM,EMP2NO,EMP2FN,EMP2MI,EMP2LN) AS SELECT ALL D1.DEPTNO,D1.DEPTNAME,D1.MGRNO,D1.FIRSTNME,D1.MIDINIT,
D1.LASTNAME,'2', D1.DEPTNO,D1.DEPTNAME,E2.EMPNO,E2.FIRSTNME,E2.MIDINIT,
E2.LASTNAME FROM VDEPMG1 D1, EMP E2 WHERE D1.DEPTNO = E2.WORKDEPT;
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VPROJRE1 (PROJNO,PROJNAME,PROJDEP,RESPEMP,FIRSTNME,MIDINIT,
LASTNAME,MAJPROJ) AS SELECT ALL PROJNO,PROJNAME,DEPTNO,EMPNO,FIRSTNME,MIDINIT,
LASTNAME,MAJPROJ FROM PROJ, EMP WHERE RESPEMP = EMPNO;
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VSTAFAC1(PROJNO, ACTNO, ACTDESC, EMPNO, FIRSTNME, MIDINIT,
LASTNAME, EMPTIME,STDATE,ENDATE, TYPE) AS SELECT ALL PA.PROJNO, PA.ACTNO,
AC.ACTDESC,' ', ' ', ' ', ' ', PA.ACSTAFF, PA.ACSTDATE, PA.ACENDATE,'1'
FROM PROJACT PA, ACT AC WHERE PA.ACTNO = AC.ACTNO;
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VSTAFAC2 (PROJNO, ACTNO, ACTDESC, EMPNO, FIRSTNME, MIDINIT,
LASTNAME, EMPTIME,STDATE, ENDATE, TYPE) AS SELECT ALL EP.PROJNO, EP.ACTNO,
AC.ACTDESC, EP.EMPNO,EM.FIRSTNME, EM.MIDINIT, EM.LASTNAME, EP.EMPTIME,
EP.EMSTDATE, EP.EMENDATE,'2' FROM EMPPROJACT EP, ACT AC, EMP EM WHERE
EP.ACTNO = AC.ACTNO AND EP.EMPNO = EM.EMPNO;
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VPHONE (LASTNAME, FIRSTNAME, MIDDLEINITIAL, PHONENUMBER,
EMPLOYEENUMBER, DEPTNUMBER, DEPTNAME) AS SELECT ALL LASTNAME, FIRSTNME,
MIDINIT , VALUE(PHONENO,' '), EMPNO, DEPTNO, DEPTNAME FROM EMP,
DEPT WHERE WORKDEPT = DEPTNO;
SET CURRENT SCHEMA = "TEST ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TEST";
CREATE VIEW VEMPLP (EMPLOYEENUMBER, PHONENUMBER) AS SELECT ALL EMPNO
, PHONENO FROM EMP;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
3. 改好了sample.sql里面的相关内容后，然后执行sample.sql文件。
命令：db2 -tvf sample.sql
示例截图：
4. 查看MYDB1里面的表格： db2 list tables
MYDB1运行DDL文件后的表格
SAMPLE数据库里的表格(做对比用)
里面有的表格没有创建成功的举个例子，表CUSTOMER没有创建成功是因为下图，这里可能是做DDL备份的时候没有选择一些参数或者没有去创建储存XML数据的tablespace，你自己可以研究一下）
二. 当所有表格都创建好了之后，就把个表格的数据从sample里面导出，然后导入到MYDB1数据库里面。
SAMPLE数据库上面表ACT的数据
MYDB1数据库中表ACT的数据
1. 导出sample数据库中的表ACT数据。
命令：db2 connect to sample
db2 export to act.ixf of ixf select * from ACT
示例
2. 把数据导入到MYDB1中的表ACT中
命令：db2 connect to MYDB1
db2 load from act.ixf of ixf replace into act
示例
Load完后数据库会放置于暂挂状态，这样是不能查看表里面的数据的。当运行SELECT命令时会有这样的错误
这个可能是因为由于有自增的主外键导致的导入错误，所以要用命令db2 SET INTEGRITY FOR act FOREIGN KEY IMMEDIATE UNCHECKED告诉DB2不用检查主外键了的关系，直接导入数据
然后成功后就可以运行select语句检查一下MYDB1中表ACT中的数据
示例里面也说了一些可能遇到的问题以及解决方法。如果你对你电脑上面local的数据库那些关联性主外键，视图之类的不太重视，只重视表中的数据的话，这个用我告诉你的这个方法足够了
扫一扫
413
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
