【DB2】数据迁移

时间:2024-02-23 13:37:23

数据迁移概述

在日常生活中常有数据的导入导出,为此db2提出了很多工具可以选择,export、import、load、db2look、db2move、db2dart,如下图所示:

a.最上面虚线框部分是工具支持的导入导出的文件格式

b.中间虚线框是db2导入导出的虚线框工具

c.最下面是db2的数据表

image

1.文件格式

1.1 DEL格式

  • 字符分隔符:界定字符字段的起始,在默认情况下,用双引号(“”)作为字符分隔符
  • 列分隔符:界定列的结束。默认使用逗号(,)作为列分隔符
  • 行分隔符:用来标识一行或一个记录的结束。默认使用换行符作为分隔符
  • 以下是DEL例子:

    image

    1.2 ACS 格式

    定长ACSII(ASC)格式,顾名思义,这种文件类型包含定长ASCII数据,每个数据长度与列定义相同,不足的用空格补齐,行与行之间通过换行符间隔

    例子如下:


    1.3 PC/IXF

    PC/IXF(IXF)是IBM特定的二进制格式,适用于异构平台间进行数据迁移,IXF的优势是占用空间小,而且包含表结构的定义,可以通过IXF重建表


    1.4 Cursor

    如果两张表进行数据迁移,最容易的办法是先将一张表导出来,存到一个文件中,然后将这个表的数据导入到另外一张表。而采用游标,则不需要落地,效率比较高,适合表间数据迁移。

    db2 CONNECT TO sample USING db2inst1 USING db2inst1
    db2 "DECLARE myCursor CURSOR FOR SELECT ID,NAME FROM TMP_001"
    db2 "LOAD FROM myCursor OF CURSOR INSERT INTO TMP_002"

    第一行:登录数据库

    第二行:将数据获取存储到游标

    第三行:从游标将数据读取并装载到表

    image


    2.export

    export用于将表里的数据导出到文件

    2.1 将TMP_002的数据导出到文件TMP_002.DAT,并记录消息

    db2 export to d:\TMP_002.DAT OF DEL messages D:\TMP_002.msg select * from TMP_002


    导出截图:

    image


    2.2 导出TMP_002表数据,导出字符串用”’’”分隔,而不是默认的双引号

    db2 export to d:\TMP_002.DAT OF DEL modified by CHARDEL\'\' messages D:\TMP_002.msg select * from TMP_002

    导出截图:

    image

    2.3 导出表TMP_003的数据,并将大对象的数据存储在lobs to指定的目录下,通过指定modified by lobsinfile,大对象字段的所有值都保存在一个文件中

    db2 export to D:\TMP_003.DAT OF DEL lobs to d:\tmp\lobs modified by lobsinfile select * from TMP_003

    导出截图:

    image

    image

    注意:导出时,必须先存在路径d:/tmp/lobs


    2.3 导出表TMP_003的数据,并将大对象的数据存储在lobs to指定的目录下,通过指定modified by lobsinfile,大对象字段的每个值都保存在一个文件中

    db2 export to D:\TMP_003.DAT OF DEL lobs to d:\tmp\lobs modified by lobsinsepfiles select * from TMP_003


    表中存在2条记录,现在导出截图如下:

    image

    3. import

    • insert 用于追加,不改变表中现有数据
    • insert_update用于表中有主键的情况,如果导入数据与表中主键匹配,则update,否则insert追加
    • replace首先删除表数据,然后插入数据文件数据,由于replace会清空,所以先备份好数据

    3.1 import参数

    commitcount(N automatic):主要目的是避免事务日志满和锁升级,表示每导入N行数据就提交一次,而不是等所有数据都导入才提交。默认情况下,db2会自动使用automatic选项

    restartcount/skipcount N:表示跳过前N行数据,而从第N+1行数据开始导入,该选项在一般出现导入错误的情况下,有些数据已经入库了,需要重新导入时候使用。

    allow wirite access:在默认情况下,import导入数据时候会自动在目标表添加X锁(排它锁),不允许其他应用程序访问,如果import允许其他应用读和写,可以使用该参数,但该选项只能用于insert或insert_update操作

    3.1 将数据TMP_002.DAT导入到数据表TMP_002中,并记录信息

    db2 import from D:\TMP_002.DAT OF DEL messages tmp_002.msg insert into TMP_002


    3.2 将数据TMP_002.DAT导入到数据表TMP_002中,字符串用”’’”分隔,而不是默认的双引号

    db2 import from D:\TMP_002.DAT OF DEL modified by CHARDEL\'\' allow write access commitcount automatic messages tmp_002.msg insert into TMP_002


    3.3 将大对象数据导入到表中lobs from 指定大对象位置

    db2 import from d:\TMP_003.DAT of del lobs from d:\tmp\lobs modified by lobsinfile insert TMP_003

    4.load(大量数据导入)


    load不是一行一行地处理数据,而是对输入数据按照DB2物理存储方式进行格式化,并将格式化的数据页直接写到数据库中,记录的日志很少,也不会检查check约束和参照完整性约束,不触发触发器,因此特别适合大数据量的导入

    load支持以下4种动作:

    • insert 用于追加,不改变表中已有数据
    • replace首先删除表数据,然后插入输入文件数据,由于replace会先删除数据,所以提前备份很重要
    • terminate将终止load操作,并将数据恢复到load开始时的状态
    • restart用于重启被中断的load命令,restart会使之前load时产生的临时文件,并从最近的一点开始加载。因此,千万不要删除load所产生的临时文件,一旦load成功完成,这些临时文件将自动删除

    load操作的4个阶段

    • 装载阶段

    将文件解析为数据物理存储的格式,直接载入到页中,而不通过DB2引擎

    • 构建索引阶段

    如果加载的表上有索引的话,构建阶段基于装载阶段收集到的键创建索引

    • 删除重复值阶段

    如果表上有主键或唯一性索引,此阶段将删除违反唯一键的行,此阶段只检查违背唯一约束的行,而不会检查check约束和参考完整性约束。可以创建一个异常表来存储被删除的行,这样load完成后可以对删除的行选择性处理。

    • 索引复制阶段

    如果load指定了allow read access和use tablespace选项,那么此阶段会将索引数据从系统临时表空间中复制到索引表空间中

    4.1 异常表的定义

    复制原表结构,并且新增2列数据,一个适用于记录行何时被插入到的时间戳列,另一个与用于存储一个行之所以被当做异常的原因

    CREATE TABLE TMP_002EXP LIKE TMP_002
    ALTER TABLE TMP_002EXP ADD COLUMN TS TIMESTAMP ADD COLUMN MSG CLOB(32K);


    具体案例实现:

    环境描述:

    表TMP_002

    CREATE TABLE THINKPAD.TMP_002
    	(
    	ID   INTEGER NOT NULL PRIMARY KEY,
    	NAME VARCHAR (20)
    	)

    1)建立异常表

    CREATE TABLE TMP_002EXP LIKE TMP_002
    ALTER TABLE TMP_002EXP ADD COLUMN TS TIMESTAMP ADD COLUMN MSG CLOB(32K);


    2)构建数据文件

    image

    3)将TMP_002.DAT的数据加载到表TMP_002

    db2 load from d:\TMP_002.DAT OF DEL MODIFIED BY DUMPFILE=d:/TMP_002.dmp messages tmp_002.msg INSERT INTO TMP_002 FOR EXCEPTION TMP_002EXP


    加载完成后,结果如下:

    image

    我们可以看到插入7行数据,拒绝了1行,删除了2行

    首先检查目标表TMP_002,表数据如下:

    image

    然后检查转储文件TMP_002.dmp

    image

    该记录被拒绝是由于ID不必须是int类型

    然后查看异常表和消息文件

    image

    这两条记录被拒绝是由于重复,违反主键约束