将SAMPLE database的数据移到MYDB1 database上

本文著作权属于我之前的同事 - 罗建峰，非常感谢他之前给我写的这封技术流邮件。 特此整理如下。

---------------------------------------------------------------------------我是可爱的分割线---------------------------------------------------------------------------------------------------------------------

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 NULLON UPDATE NO ACTIONENFORCEDENABLE QUERY OPTIMIZATION;ALTER TABLE "TEST    "."DEPARTMENT" ADD CONSTRAINT "ROD" FOREIGN KEY("ADMRDEPT")REFERENCES "TEST    "."DEPARTMENT"("DEPTNO")ON DELETE CASCADEON UPDATE NO ACTIONENFORCEDENABLE QUERY OPTIMIZATION;-- 表上的外键的 DDL 语句 "TEST    "."ACT"ALTER TABLE "TEST    "."ACT" ADD CONSTRAINT "RPAA" FOREIGN KEY("ACTNO")REFERENCES "TEST    "."ACT"("ACTNO")ON DELETE RESTRICTON UPDATE NO ACTIONENFORCEDENABLE 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 WHEREEP.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的数据库那些关联性主外键，视图之类的不太重视，只重视表中的数据的话，这个用我告诉你的这个方法足够了