数据移动手段
DB2的数据移动手段,可以分为逻辑结构层面的数据移动和物理结构层面的数据移动:逻辑结构层面的数据移动主要是指数据库对象的变化,和业务关联性很大;物理结构层面的数据移动主要在于数据底层存储位置的变化,比如表空间路径的变化,或数据库整体被物理地搬到另一台机器上。
逻辑结构层面的数据移动方法
级别
名称
方式
单表级别
导出(EXPORT)
使用SELECT语句或XQuery语句抽取数据,并将其放到文件中
单表级别
导入(IMPORT)
使用INSERT语句向表、类型表(使用用户自定义类型而建立的表)或试图 填充数据
单表级别
LOAD导入
能够高效地将大量数据导入到表中。LOAD导入速度快于IMPORT
单表级别
表移动存储过程(ADMIN_MOVE_TABLE)
DB2 V9.7中新出现的存储过程。它能够在不影响系 统可用性的情况下把表从一个空间移动到另一个表空间
多表级别
DB2MOVE
通常用于跨平台迁移数据库
复制模式存储过程(ADMIN_COPY_SCHEMA)
将同一个数据库中某模式(SCHEMA)中的队形和数据复制到另外一个模式中
物理层面的数据移动方法
名称
方式
数据库备份与恢复
如果两个平台是二进制兼容的,那么可以使用一个平台的备份,在另外一个平台恢复,从而实现数据库在平台间的移动。另外,可以将低版本的数据库备份恢复到高版本实例中,比如可以将DB2 V9.1的数据库备份恢复到DB2 V9.5的实例中,这实现了恢复过程中数据库的升级
重定向恢复
在使用数据库备份恢复的时候,可以改变目标数据库的物理存储位置
重定位数据库(db2relocatedb)
通过修改数据库控制文件,来重命名数据库或者改变数据库的存储路径,从而实现数据移动的目标。不过,数据库对象的变化需要手动完成。执行这个实用程序时,数据库实例必须处于停止状态
数据移动手段多种多样,刚接触,目前先从EXPORT,IMPORT,LOAD这三种最常用的方法说走。
数据准备
建立一张测试表test,并向其中插入一组数据
create table test(
c1 int,
c2 int,
c3 char(10)
);
insert into test values(100,200,'xin ');
select * from test;
建立一张测试表mytab1
create table mytab1(
c1 INT,
c2 INT,
c3 char(32),
c4 char(32)
);
select * from mytab1;
效果如图
建立一个asc格式的文件,并向其中输入如图测试数据
导出(EXPORT)
在使用EXPROT命令时,常用的三种类型的数据:常规类型数据、LOB数据和XML数据。这次只简单介绍常规类型数据的使用。
常用的使用格式如下:
EXPORT TO file_name OF file_type
MODIFIED BY file_type_modifiers
MESSAGES message_file
selet_statement
其中
file_type 包含的格式有:DEL、IXF、WSF等
message_file用于保存export过程中输出的信息
file_type_modifiers是指文件类型修饰符,常见的文件类型修饰符如下:
CHARDELx:x表示用来指定的字符串定界符。默认值是双引号(“”)。
COLDELx :x表示的列定界符。默认值是双引号(,)。
CODEPAGE=x:x用来表示将字符串导入文本数据时使用的编码。
Timestampformat=”x”:x是源表中时间戳记的格式。(YYYY/MM/DD HH:MM:SS.UUUUUU、YYYY/MM/DD HH、YYYY-MM-DD HH:MM:SS TT、MMM DD YYYY HH:MM:SS:UUUTT、MMM DD YYYY HH:MM:SSTT)
在EXPORT中使用文件修饰符的方法如下:
MODIFIED BY chardel! Coldel# codepage=1208 timestampformat=\"yyyy.mm.dd hh:mm\"
示例
连接到测试数据库
db2 connect to database
db2 "EXPORT TO /file_path/test.del OF DEL MESSAGES msg.out SELECT * from test"
使用cat命令查看数据
注意
select后面是可以加各种条件的,如select c3 from test where c1='100'
EXPORT不支持ASC文件格式
file_name所在文件夹应该具有写和读的权限
file_name不用事先建立,会自动生成
file _name的格式 由 of del 选项决定,而不是file_name的后缀名。如,可以写成:test.txt of del、test.csv of del、test.ixf ofixf等
导入(IMPORT)
IMPORT命令导入常规类型数据的基本格式:
IMPORT FORM file_name OF { IXF | ASC | DEL | WSF}
MODIFIED BY file_type_modifiers
[ METHOD {
L (col-start col-end ) [null indicators (col-position ] |
N (col-name ) |
p (col-position)
}]
ALLOW { NO | WRITE } ACCESS
COMMITCOUNT { n | AUTOMATIC}
RESTARTCOUNT | SKIPCONT
ROWCONT n
MESSAGES message_file
[ INSERT | INSERT_UPDATE | REPLACE | REPLACE_CREATE | CREATE]
INTO target_table_name
字段过滤方式
在导入的时候可以选择只导入部分字段的数据,这需要在IMPORT中使用METHOD选项。METHOD选项有三种:METHOD L、METHOD N、METHOD P。下表是三种方式的区别
名称
适用的文件格式
带的参数
METHOD L
ASC文件
起始位置和终止位置
METHOD N
IXF文件
字段名称
METHOD P
DEL文件和IXF文件
字段位置(从1开始)
下面将通过三个小例子来对这三种方式加以区分。
MOTHOD L过滤方式
只能用于从ASC文件里导入数据,可以实现导入指定字段的一部分或全部。
用户需要指定每个字段在每行对应的起始位置(col-start)和终止位置(col-end),起始位置和终止位置之间用空格分隔。
示例
* 使用METHOD L 进行导入
db2 "IMPORT from /data/xin/loadtest/test.asc of ASC METHOD L(1 5,10 12,20 30) messages msg.out insert into mytab1(c1,c2,c4)"
* 查看效果
METHOD N过滤方式
通过名称过滤导入文件中的字段,支持IXF文件类型。
示例
* 执行如下命令
db2 "load from /data/xin/loadtest/test.ixf of ixf method N(C2,C1,C3) insert into mytab1(c1,c2,c4)"
查看效果
1481702957(1).jpg
METHOD P过滤方式
通过字段位置(从1开始)过滤数据文件中要加载的字段。
示例
* 执行如下命令
db2 "load from /data/xin/loadtest/test.del of del method P(2,1,3) insert into mytab1(c2,c1,c4)"
查看效果
导入方式选项对比情况
|导入方式| 详情|
|--------|--------|--------|
| INSERT | 在表中现有数据的基础之上追加新的数据,如果导入的行与已存在行有主键冲突,则本行不导入|
|INSERT_UPDATE |此选项只针对有主键的表,在导入数据时需要对比主键,主键重复的话就update(用新数据替换原来数据),否则就insert(直接插入)|
|REPLACE |把表中原有的数据都删除,并导入新的数据。由于进行了清空表操作,有风险,选择需谨慎。|
| REPLACE_CREATE |目标表存在,则和REPLACE选项一样。如果目标表没有定义,则建立目标表及索引,使用这个选项的掐你是导入文件为PC/IXF格式|
| CREATE|建立目标表及索引,并导入数据,使用这个选项的前提是导入文件为PC/IXF格式|
LOAD导入
LOAD