db2 数据迁移

1.导入(import)导出(export)以及导入(load)的介绍

DB2中所谓的数据移动，包括：
1. 数据的导入（Import）
2. 数据的导出（Export）
3. 数据的装入（Load）
导入和装入都是利用DB2的相关命令把某种格式的文件中的数据保存到数据库中的表中
导出是指把DB2数据库的表中的数据保存到某种格式的文件当中去
数据移动的作用：
如果要在不同的数据库管理系统之间转移数据，数据移动通常是最实用的一种方法，因为任何一种数据库管理系统都支持常用的几种文件格式，通过这个通用的接口，就很容易实现不同系统间数据的转移。
这三个命令中，Export最简单，因为从表中向文件转移数据，通常不会出现错误，也不会有非法的数据。
在讲解命令之前，首先介绍一下文件的格式，用于DB2数据移动的文件格式有四种：
1. ASC——非定界ASCII文件，是一个ASCII字符流。数据流中的行由行定界符分隔，而行中的每一列则通过起始和结束位置来定义。例如：
10   Head Office     160   Corporate   New York
15   New England  50     Eastern       Boston
20   Mid Atlantic     10     Eastern       Washington
38   South Atlantic 30     Eastern       Atlanta
42   Great Lakes    100   Midwest      Chicago
51   Plains              140   Midwest      Dallas
66   Pacific              270   Western     San Francisco
84   Mountain         290   Western     Denver
2. DEL——定界ASCII文件，也是一个ASCII字符流。数据流中的行由行定界符分隔，行中的列值由列定界符分隔。文件类型修饰符可用于修改这些定界符的默认值。例如：
10,"Head Office",160,"Corporate","New York"
15,"New England",50,"Eastern","Boston"
20,"Mid Atlantic",10,"Eastern","Washington"
38,"South Atlantic",30,"Eastern","Atlanta"
42,"Great Lakes",100,"Midwest","Chicago"
51,"Plains",140,"Midwest","Dallas"
66,"Pacific",270,"Western","San Francisco"
84,"Mountain",290,"Western","Denver"
3. WSF——（work sheet format）为工作表格式，用于与Lotus系列的软件进行数据交换。
4. PC/IXF——是集成交换格式（Integration Exchange Format，IXF）数据交换体系结构的改编版本，由一些列可变长度的记录构成，包括头记录、表记录、表中每列的列描述符记录以及表中每行的一条或多条 数据记录。PC/IXF 文件记录由包含了字符数据的字段组成。
第一部分：数据的导出（Export）
例一：把Org表中的所有数据导出到文件C:\ORG.TXT中。
Export to c:\org.txt of del select * from org
其中，of del表示导出到的文件的类型，在本例中导出到一个非定界文本文件中；后面的select * from org是一个SQL语句，该语句查询出来的结果就是要导出的数据。
例二：改变del格式文件的格式控制符
export to c:\staff.txt of del modified by coldel$ chardel'' decplusblank select * from staff
在 该例中，modified子句用于控制各种符号，coldel表示字段之间的间隔符，默认情况为逗号，现在改为$号；chardel表示字符串字段用什么 符号引用，默认情况下为一对双引号括起来，现在改为用一对单引号括起来；decplusblank表示对于十进制数据类型，用空格代替最前面的加号，因为 默认情况下会在十进制数据前面加上正负号的。
例三：以ASC格式将数据导出到文件
Export命令是不支持ASC格式文件的，所以如果想导出ASC这样规整的格式，需要程序员自己进行转换操作，思路是将各种数据类型都转换成定长字符串，然后把各个要导出的字段合并成为一个字段。
例如创建如下结构的表n：
create table n(a int,b date,c time,d varchar(5),e char(4),f double)
然后插入两条数据：
insert into n values(15,'2004-10-21','23:12:23','abc','hh',35.2)
insert into n values(5,'2004-1-21','3:12:23','bc','hhh',35.672)
要想把这两条数据以规整的格式导出到文件中，进行如下操作：
export to c:\test.txt of del select char(a) || char(b) || char(c) || char(d,5) || e || char(f) as tmp from n
这样导出的结果与ASC格式的文件非常类似，只是每一行的前后多出了一对双引号，对此我们可以使用文本工具（如写字板、记事本等）把双引号删除掉，也可以置之不理，在以后导入的时候直接控制格式（忽略双引号）
在文件中的格式为：
"15         2004-10-2123.12.23abc  hh  3.52E1                  "
"5          2004-01-2103.12.23bc   hhh 3.5672E1                "
例四：大数据的导出
export to d:\myfile.del of del lobs to d:\lob\ lobfile lobs modified by lobsinfile select * from emp_photo
该命令把emp_photo表的数据导出到d:\myfile.del文件中，其结果为：
<pre>;
"000130","bitmap","lobs.001.0.43690/"
"000130","gif","lobs.001.43690.29540/"
"000130","xwd","lobs.001.73230.45800/"
"000140","bitmap","lobs.001.119030.71798/"
"000140","gif","lobs.001.190828.29143/"
"000140","xwd","lobs.001.219971.73908/"
"000150","bitmap","lobs.001.293879.73438/"
"000150","gif","lobs.001.367317.39795/"
"000150","xwd","lobs.001.407112.75547/"
"000190","bitmap","lobs.001.482659.63542/"
"000190","gif","lobs.001.546201.36088/"
"000190","xwd","lobs.001.582289.65650/"
</pre>;
其 中第三个字段是BLOB类型，在该文件中只保存了一个标志，相当于一个指针，真正的LOB数据保存在d:\lob目录下的lobs.001、 lobs.002、......等一系列文件中。命令中lobs to 后面指定大对象数据保存在什么路径下（注意，该路径必须事先已经存在，否则会报错），lobfile 后面指定大对象数据保存在什么文件中，不要指定扩展名，DB2会根据数据量自动追加.001、.002等扩展名，同时不要忘记加上modified by lobsinfile子句。
例五：把导出信息保存在消息文件中。
export to d:\awards.ixf of ixf messages d:\msgs.txt select * from staff where dept = 20
这个例子把staff表中dept=20的数据导出到d:\awards.ixf文件中，所有的导出信息都保存在d:\msgs.txt文件中（无论是成功、警告还是失败信息），这样，管理员可以通过观察信息文件找到问题所在。
例六：给导出数据列重命名。
export to d:\awards.ixf of ixf method n(c1,c2,c3,c4,c5,c6,c7) messages d:\msgs.txt select * from staff where dept=20
在默认情况下，导出的每一列数据以表中对应的字段名自动命名，我们可以通过method n子句给每一列重新命名，需要注意的是，这个子句只在ixf和wsf格式文件中有效，在文本文件中不能使用。
数据的导入
例七：把C盘根目录下的org.txt文件中的数据导入到org表中
import from c:\org.txt of del insert into org
导 入命令和导出命令的格式基本上处于对应的关系，import对应export，from对应to，文件名和文件格式代表的含义相同，但是导入命令支持 ASC格式的文件，而导出命令不支持。另外，在导出命令的最后是一个SQL语句，用于选择要导出的数据，而导入命令最后不是SQL语句，而是插入数据的方 式以及目标表名称。
例八：从ASC格式文件中导入数据
import from  c:\org2.txt of asc method l(1 5,6 19,20 25,26 37,38 50) insert into org
其中 method l 子句用于指定文本文件中每一个字段的起始位置和终止位置，每个起始位置和终止位置间用空格分开，字段之间用逗号分开。
除了l方法之外，还有n方法和p方法，下面会叙述。
例九：利用n方法导入数据，并且创建新表。
首先导出一个用例文件：
export to d:\org.ixf of ixf method n(a,b,c,d,e) select * from org
这样org.ixf文件中有五列数据，对应的列名分别为a、b、c、d、e
然后在从该文件中导入数据到一个新表中
import from d:\org.ixf of ixf method n(d,e,b) replace_create into orgtest
该命令从文件中选取三列导入到表中，顺序可以不按照文件中原有的列的顺序。replace_create方式的叙述见下。
插入方式有：
INSERT 方式——在表中现有数据的基础之上追加新的数据。
INSERT_UPDATE 方式——这种方式只能用于有主键的表，如果插入的数据与原有数据主键不冲突，则直接插入，如果主键冲突，则用新的数据代替原有数据。
REPLACE 方式——先把表中现有的数据都删除，然后向空表中插入数据。
REPLACE_CREATE 方式——表示如果表存在，则先把表中的数据都删除，然后向空表中插入数据；如果表不存在，则先根据文件中的字段创建表，然后再向表中插入数据。这种方式只能把IXF格式的文件中的数据插入到表中。
例十：利用p方法导入数据
import from d:\org.ixf of ixf method p(4,5,2) replace into orgtest
该例子执行的效果和例九类似，只是把n方法换成了p方法，p方法后面的列表中指明列的序号即可，不需要指明列名。另外，此例中使用了replace方式插入数据，这会把表中现有的数据都删除，然后向空表中插入数据。
例十一：关于空值的导入
对于ixf格式的文件，导入空值非常方便，因为里面已经记录了空值的信息。但是，对于ASC格式文件就有一定的难度了，因为DB2会直接插入空格，而不是空值。为此，DB2提供了一个子句进行控制：NULL INDICATORS
import from  c:\org2.txt of asc MODIFIED BY nullindchar=# method l(1 5,6 19,20 25,26 37,38 50) NULL INDICATORS(0,0,0,0,38 ) replace into org
在这个例子中，NULL INDICATORS子句后面是一个列表，表示前面四个字段都不会存在空值，而第五个字段从38列开始，可能存在空值，而 MODIFIED BY nullindchar=# 子句表示在文件中第五个字段如果遇到 # 号，则表示为空值。
就说这些吧，抛砖引玉，希望大家补充，下一次谈一谈Load命令。
装入（Load）
装入命令格式与导入类似，命令关键字是Load，但是后面的参数比导入命令多的多，详细用法可以自行参考DB2文档。
装入与导入类似，都是将输入文件中的数据移入到目标表中，二者的不同点将在实例中逐步解释。
在装入之前，目标表必须已经存在。
装入的性能比导入高，原因在后面结合实例详细解释。
装入操作不记录到日志中，所以不能使用日志文件进行前滚操作。
装入分为4个阶段：
1. 装入阶段
在这个阶段发生两件事：数据存储在表中，收集索引键并排序。在装入时，DBA可以指定多长时间生成一致点。
它是装入工具的检查点。如果装入在执行期间被打断，它可以从最后一个一致点处开始继续重新执行。
2. 构建阶段
在构建阶段，基于在装入阶段收集的索引键信息创建索引。如果在构建阶段发生错误，装入工具就重启，它将从构建阶段开始处重新开始构建。
3. 在删除阶段，所有违反唯一或主键约束的行都被删除并拷贝到一个异常表（如果在语句中指定相应选项）中。当输入行被拒绝，消息文件中就生成消息。
4. 索引拷贝阶段
如果在装入操作期间为索引创建指定了系统临时表空间，并且选择了 READ ACCESS 选项，该索引数据将从系统临时表空间拷贝到原来的表空间。
装入过程的所有四个阶段都是操作的一部分，只有在所有的四个阶段都完成之后，该装入操作才算完成。在每个阶段都将生成消息，一旦其中的某个阶段发生错误，这些消息可以帮助DBA分析并解决问题。
导入操作每次插入一行数据时都要检查是否满足约束条件，并且记入日志文件中。
下面我们看一些LOAD命令特有的功能，IMPORT命令也能做的就不再详细说了。
例十二：从光标类型文件中进行装入
定义一个cursor
declare mycur cursor for select * from org
创建一个新表，结构与cursor相容
create table org2 like org
从cursor中装入
load from mycur of cursor insert into org2
除了可以从cursor中装入，还可以从文件、管道、设备中进行装入。而导入命令只能从文件中导入。
例十三：关于异常表
由用户定义的异常表可以用于存储不遵循唯一约束和主码约束的行。如果装入的时候没有指定异常表，则违反唯一约束的行将被丢弃并且将不再有机会恢复或修改。
用SAMPLE数据库中的STAFF表做实验
1. 创建一个结构与STAFF表相同的表STAFF1
CREATE TABLE STAFF1 LIKE STAFF
2. 把STAFF表中的一部分数据插入到STAFF1中
INSERT INTO STAFF1 SELECT * FROM STAFF WHERE ID<=160
3. 再创建一个结构与STAFF1相同的表STAFFEXP，作为异常表
CREATE TABLE STAFFEXP LIKE STAFF1
4. 给该异常表添加一列，因为异常表和普通表相比，前面的结构都相同，就是最后多出一列或两列（列名任意），第一列是时间戳类型，记录异常记录插入的时间，第二列是大文本类型（至少为32K大小），保存导致该条记录被拒绝的特定约束信息。本例中只添加一个时间戳列。
ALTER TABLE STAFFEXP ADD COLUMN TIME TIMESTAMP
5. 为STAFF1表创建一个唯一索引
CREATE UNIQUE INDEX IDXSTAFF ON STAFF1(ID)
6. 先运行导出命令做出一个文本文件
EXPORT TO D:\STAFF.TXT OF DEL SELECT * FROM STAFF
7. 然后运行装入命令把数据再装入到STAFF1表中
LOAD FROM D:\STAFF.TXT OF DEL INSERT INTO STAFF1 FOR EXCEPTION STAFFEXP
由于表STAFF1中有唯一索引，所以会有一部分数据因为违反这个约束条件而不能插入到STAFF1表中，这些记录就会插入到异常表STAFFEXP中。
注意一点，异常表必须自己先定义好，装入命令不能够自动生成异常表，如果找不到指定的异常表，就会报错。
例十四：关于DUMP文件
格式不正确的行会被拒绝。通过指定DUMPFILE文件类型修饰符可以使这些被拒绝的记录单独放在指定的文件里。
用SAMPLE数据库中的STAFF表做实验
1. 创建一个结构与STAFF表相同的表STAFF1
CREATE TABLE STAFF1 LIKE STAFF
2. 把STAFF表中的一部分数据插入到STAFF1中
INSERT INTO STAFF1 SELECT * FROM STAFF WHERE ID<=160
3. 再创建一个结构与STAFF1相同的表STAFFEXP，作为异常表
CREATE TABLE STAFFEXP LIKE STAFF1
4. 给该异常表添加一列
ALTER TABLE STAFFEXP ADD COLUMN TIME TIMESTAMP
5. 为STAFF1表创建一个唯一索引
CREATE UNIQUE INDEX IDXSTAFF ON STAFF1(ID)
6. 先运行导出命令做出一个文本文件
EXPORT TO D:\STAFF.TXT OF DEL SELECT * FROM STAFF
到D盘上打开STAFF.TXT文件，把第一列等于320的行替换为："abcf","aaa","sdfg"
7. 然后运行装入命令把数据再装入到STAFF1表中
LOAD FROM D:\STAFF.TXT OF DEL MODIFIED BY DUMPFILE=d:\dump INSERT INTO STAFF1 FOR EXCEPTION STAFFEXP
装入的结果报告中会有如下一条：
SQL3118W  在行 "32" 列 "1" 中的字段值不能转换为 SMALLINT 值，但是目标列不可为空。未装入该行。
SQL3185W  当处理输入文件的第 "32" 行中的数据时发生先前的错误。
打开D盘的dump.000文件，会看到造成异常的那一行数据："abcf","aaa","sdfg"
通过这个例子，我们可以理解，如果一行数据的格式不正确，在装入的时候会遭到拒绝，该行记录会放到DUMP文件中；而如果数据格式正确，但是不满足表的约束条件，该行记录会放到异常表中。
例十五：限制装入行数
用ROWCOUNT选项可以指定从文件开始处装入的记录数
LOAD FROM D:\STAFF.TXT OF DEL ROWCOUNT 3 INSERT INTO STAFF1
例十六：出现警告信息时强令装入操作失败
在某些情况下，文件中的数据必须全部成功输入到目标表中才算成功，即使有一条记录出错也不行。在这种情况下，可以使用WARNINGCOUNT选项。
到D盘上打开STAFF.TXT文件，把第一列等于320的行替换为："abcf","aaa","sdfg"
LOAD FROM D:\STAFF.TXT OF DEL WARNINGCOUNT 1 INSERT INTO STAFF1
运行结果包含下面的警告：
SQL3118W  在行 "32" 列 "1" 中的字段值不能转换为 SMALLINT值，但是目标列不可为空。未装入该行。
SQL3185W  当处理输入文件的第 "32" 行中的数据时发生先前的错误。
SQL3502N  实用程序遇到了 "1" 个警告，它超过了允许的最大警告数。
此时无法对表STAFF1进行操作，例如
SELECT * FROM STAFF1
会返回：
ID     NAME      DEPT   JOB   YEARS  SALARY    COMM
------ --------- ------ ----- ------ --------- ---------
SQL0668N  由于表 "USER.STAFF1" 上的原因代码 "3"，所以不允许操作。
SQLSTATE=57016
原因是：表处于“装入挂起”状态。对此表的先前的 LOAD 尝试失败。在重新启动或终止 LOAD 操作之前不允许对表进行存取。
解决方法为：通过分别发出带有 RESTART 或 TERMINATER 选项的 LOAD 来重新启动或终止先前失败的对此表的 LOAD 操作。
包含TERMINATER的LOAD命令可以终止装入进程，使目标表恢复正常可用状态：
LOAD FROM D:\STAFF.TXT OF DEL TERMINATE INTO STAFF1
包含RESTART的LOAD命令可以在源文件修改正确的时候使用，使装入进程重新开始：
LOAD FROM D:\STAFF.TXT OF DEL RESTART INTO STAFF1
例十七：防止产生警告信息
使用NOROWWARNINGS文件类型修饰符可以禁止产生警告信息，当装入过程可能出现大量警告信息，而用户对此又不感兴趣的时候，可以使用该选项，这样可以大大提高装入的效率
到D盘上打开STAFF.TXT文件，把第一列等于320的行替换为："abcf","aaa","sdfg"
LOAD FROM D:\STAFF.TXT OF DEL MODIFIED BY NOROWWARNINGS INSERT INTO STAFF1
运行完的结果中，第32行出错，该行无法装入，但是不产生警告信息。
例十八：生成统计数据
使用STATISTICS选项可以在装入的过程中生成统计数据，这些统计数据可以供优化器确定最有效的执行SQL语句的方式。
可以对表和索引产生不同详细程度的统计数据：
① 对表和索引产生最详细的统计数据：
LOAD FROM D:\STAFF.TXT OF DEL REPLACE INTO STAFF1 STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL
② 对表和索引都产生简略的统计：
LOAD FROM D:\STAFF.TXT OF DEL REPLACE INTO STAFF1 STATISTICS YES AND INDEXES ALL
其它组合可以参考DB2文档。
注意：STATISTICS选项只能和REPLACE兼容，与INSERT选项不兼容。
另外，通过STATISTICS选项做完统计，我们看不到任何直接的结果，如果想查看其结果，需要到系统表中自己查询。
例十九：解除检查挂起状态
1. 连接到SAMPLE数据库上：
Connect to sample
2. 创建一个结构与staff表相同的表：
CREATE TABLE STAFF1 LIKE STAFF
3. 给该表添加一个检查约束：
alter table staff1 add constraint chk check(dept<100)
4. 到D盘上打开STAFF.TXT文件，把最后一行数据的第三列改为150，这样该条数据就不满足第3步加上的检查约
束条件了，然后用Load命令从文件中装入数据到staff1表中：
LOAD FROM D:\STAFF.TXT OF DEL INSERT INTO STAFF1
5. 此时运行查询命令：
Select * from staff1
会得到错误信息：
SQL0668N  由于表 "USER.STAFF1" 上的原因代码 "1"，所以不允许操作。
SQLSTATE=57016
原因是装入时有数据违反了检查约束，造成表处于检查挂起状态。
6. 解除表的检查挂起状态，使用：
set integrity for staff1 check immediate unchecked
再次运行查询命令：
Select * from staff1
发现表可以正常使用了，其中的违反检查规则的数据也存在。
例二十：性能因素
在 从文件向表导入数据的时候，当数据量特别大的情况下，装入命令会明显体现出优势，原因是它不像导入命令每次插入一行，并且在每行都要检查是否满足约束条 件，装入命令从输入文件读出数据构建页，把这些页直接写入数据库，并且在每一行数据装入时不判断是否满足约束，另外装入命令不写日志，所有这些因素都导致 装入的效率高于导入。
另外，装入命令还有一些选项可以控制性能因素：
1. COPY YES/NO和Nonrecoverable
① Nonrecoverable（不可恢复的）：指定装入操作不可恢复，并且不能由后续的前滚操作恢复。前滚操作忽略事务并且标记正在装入数据的表为“无效”。
② Copy No（默认选项）：在这种情况下，如果表所在数据库的归档日志处于启用状态，则装入完成后，表所在的表空间将处于备份挂起状态，直到数据库或表空间备份完 毕，该表空间才成为可写表空间。原因是装入操作造成的变化没有被记录，所以要恢复装入操作完成后发生的故障，备份数据库或表空间是必要的。
③ Copy Yes：在这种情况下，如果数据库的归档日志启用，装入操作的改变将被保存到磁带、目录或TSM服务器，并且表空间将不再处于备份挂起状态。
2. Fastparse
该文件类型修饰符用于减少数据检查次数。它只能用于在数据已知正确的情况下，尤其适用于DEL和ASC类型的文件。
3. Anyorder
如果SAVECOUNT选项没有使用，该参数允许不遵照输入文件中的数据顺序进行装入，在SMP（对称多处理机）系统上CPU_PARALLELISM选项大于1的时候，该参数会提高装入的性能。
4. Data Buffer
该参数用于指定从堆栈分配得到的4K大小的内存页面的数目，作为装入的内部缓冲区，指定一个大缓冲区有助于提高装入的性能。
5. CPU_PARALLELISM
该选项只能用于SMP系统上，可以指示使用多少进程或线程解析、转换、格式化数据。
6. Disk_Parallelism
该选项指定写数据到磁盘的进程或线程的数目。

2.导入导出全解

DB2数据导入导出 1 Export 数据导出 语法：export to filename of filetype modified by filetype-mod messages message-file select-statement filetype:支持del|wsf|ixf等格式，del为ASCII字符隔离格式，wsf工作表格格式(不知道用什么打开)，ixf夸平台的二进制格式 filetype-mod:文件类型模式(下面列举的只针对del格式，其中codepage可用于ixf格式) chardelx : 分隔符，默认为(")，这里指定x为分隔符 codepage=x : 设置代码页，1208为UTF-8编码格式 coldelx  : 列分隔符，默认为(,)，这里指定为x nochardel: 列没有包裹字符，默认为("") timestampformat="x" : 设置日期数据格式，YYYY/MM/DD HH:MM:SS.UUUUUU 2 Import数据导入 格式：import from filename of filetype modified by filetype-mod allow no access|allow write access commitcount n|automatic restartcount n|skipcount rowcount n warningcount n notimeout message message-file insert|insert_update|replace|replace_create into table-name filetype:同export中的filetype filetype-mod:同export中的filetype 访问权限:allow no acces默认，不允许访问，导入加X排它锁，脱机模式(目标表脱机)运行import。    allow write access  允许写，导入加IX锁，在线模式运行import，允许并发(concurrent)读写。 commitcount:提交行数，多少行记录做一次提交动作，使用该参数可以加快import的速度，automatic自动。 restartcount:重启import从多少行记录开始 n+1 skipcount:跳过的行数,n+1开始，与restartcount互斥 warningcount:有多少警告之后停止import操作 notimeout:开始import操作等待x锁时不会超时 insert|insert_update|replace: insert : 单纯插入数据 insert_update : 插入或更新数据，根据主键pk来判断 replace : 先删除存在的表数据(truncate方式，快速)再导入新数据，不改变表和索引的定义，前提是表必须存在。 3 Load 数据加载 语法：load client from filename of filetype modified by file-type-mode savecount n rowcount n warningcount n messages message-file insert|replace keepdictionary|resetdictionary|restart|terminate into table-name  copy no|copy yes to directory|nonrecoverable without prompting data buffer buffer-size allow no access|allow read access set integrity pending cascade immediate|deferred lock with force  client : 指定从远程客户端调用 filetype:同import中的filetype filetype-mode:同import中的filetype-mode savecount : 指定Load建立一致点的页数，默认为0，建立后在Load query时能查询到 db2 "load query table table-name"会提示"SQL3519W  开始装入一致点。输入记录数=" rowcount : 指定Load多少条记录 warningcount: 指定多少警告后结束load messages : load的消息输出 insert:load只插入数据，不改变存在的数据 replace:插入的同时也替换 keepdictionary  terminate:结束load操作 restart:重新启动load操作 copy no : load结束后表空间标记为备份暂挂状态,此时数据可以读，DML操作无效。归档模式下的默认值。 copy yes: 指定load的数据拷贝将被保存到image中 nonrecoverable:load事务标记为不可恢复，做操作的表只能drop或从备份中恢复，在循环日志模式下的默认值。 data buffer:指定4K的页数用于数据转换的缓冲区 lock with force:不需等待，可以强制获取锁。 查询表LOAD操作的状态oad query table table-name to message-file  load：导入数据，功能和import基本相同。支持以上说的4种格式。Load最大的缺点是变化没有记录日志，不能前滚恢复。  Load的工作步骤：Load(装载数据到表，收集信息、索引key集合和保存一致点)-->Build(根据Load阶段收集的信息和索引key集合建立索引)-->Delete(删除表中违反主键约束和唯一性约束的数据，把这些数据放到Exception表中)-->Index Copy(将索引从系统临时表空间拷贝到目标表空间) db2 "list utilities show detail" 查看Load的具体情况 在测试的过程中我只看到setup和load两个阶段，可能是load太快了吧。 Load各阶段的状态: 1)载入挂起load pendding,在Load阶段发生错误，导致表空间处于载入挂起 解决：1)纠正错误后重新载入，将replace或insert换成restart。将从失败处重新开始装载操作。  2)如果前面调用用了replace，则再使用replace做load。 3)用terminate选项终止load操作。 4)DBA撤消并重新建立表空间  5)从一个备份中恢复表空间（归档日志下才能）。 2)删除挂起delete pendding，在Delete阶段发生错误 3)备份挂起backup pendding，使用copy no后表空间处于此状态，做一下全备或者备份该表空间即可解除 4)检查挂起check pendding，当表上存在主键或唯一键之外的约束时，Load完成后该表处于此状态 解除装入数据时，发生的检查挂起: SET INTEGRITY FOR TABLE-NAME CHECK IMMEDIATE UNCHECKED;  命令只对数据通过约束检查的表有效，如果执行还不能解除，有必要检查数据的完整性，是否不符合约束条件，并试图重新整理数据，再执行装入操作。 load和import的不同：  1、load的目标表必须存在，而import不必  2、load并不创建索引，只是在原来索引的基础上追加数据  3、最大差别在于性能差别，import每次插入一条，每行都要进行一次限制检查，日志用来记录变化，而load插入则快的多，每次读取数据构造页，然后直接输入到数据库。 插入完页后，索引将重新建立，违反主键和唯一键限制的记录将被删除并保存到另一个扩展表中。  注：load技巧使用：大数据量删除。  load from a.del of del replace into table IMPORT导入以记录(data record)为单位，对数据进行合法检验，数据修改记日志。LOAD载入是以数据页(data page)为单位，不对数据进行合法检验。所以LOAD在导入性能上优于IMPORT。 DB2使用export导出的del文本中，由于有字段有换行符，load装入数据时，默认以一行为一条记录，这样数据就有问题了。 解决方法如下： MODIFIED BY DELPRIORITYCHAR DB2默认load优先级策略为：record delimiter, character delimiter, column delimiter,这样record delimiter得优先级最高，所以原始文件如果有换行的话load就认为是新的record，如果用户在某些情况下行里面包含了换行符（比如论坛里面的一条帖子，不可能把换行符删掉的），就必须用delprioritychar改变默认的优先级别，确保""之间的数据不管有没有换行符都被认为是同一条记录 例：LOAD FROM TAB.DEL OF DEL MODIFIED BY DELPRIORITYCHAR  INSERT INTO TAB 另：用IXF不会出现此问题。 4 SET INTEGRITY 设置完整性暂挂 C:\>db2 "create table newstaff like staff in ts_user_data index in ts_user_index" C:\>db2 "alter table newstaff add constraint chk_newstaff_dept check(dept<84)" C:\>db2 "export to d:\540\staff.del of del select * from staff" C:\>db2 "load from d:\540\staff.del of del insert into newstaff" C:\>db2 "select count(1) from newstaff" SQL0668N  不允许对表 "540.NEWSTAFF" 执行操作，原因码为 "1"。  SQLSTATE=57016 C:\>db2 "list tablespaces show detail" 发现此时表newstaff所在的表空间ts_user_data处理备份暂挂状态 C:\>db2 ? 57016 SQLSTATE 57016: 因为表不活动，所以不能对其进行访问。 C:\>db2 "set integrity for newstaff immediate checked" SQL3603N  通过 SET INTEGRITY 语句来检查数据处理时，发现涉及名为"540.NEWSTAFF.CHK_NEWSTAFF_DEPT"  的约束或唯一索引的完整性违例。  SQLSTATE=23514 从这里可以看出是LOAD进了违反约束的数据 C:\>db2 "backup db sample tablesapce ts_user_data to d:\540" 解除表空间的备份暂挂状态 C:\>db2 "connect to sample" 备份后需要重新连接 C:\>db2 "list tablespaces show detail" 再次查看表空间是正常状态 C:\>db2 "set integrity for newstaff check immediate unchecked" 解除表的检查挂起状态 C:\>db2 "select count(1) from newstaff" 到此表newstaff可以访问了。 SQL0668N，错误代码1：可能是由于有自增的主外键导致的导入错误 解决方案： C:\>db2 "set integrity for newstaff immediate checked force generated" 有些情况可以使用下列语句恢复 C:\>db2 "set integrity for newstaff foreign key immediate unchecked" C:\>db2 "set integrity for newstaff check immediate unchecked" SQL0668N，错误代码3：装入暂挂  1)备份该表所在的表空间，然后重新恢复表空间 backup db sample tablespace ts_user_data ; restore db sample tablespace ts_user_data online taken at  rollforward db sample to end of logs and complete tablespace ts_user_data online 2)重新load数据，或者load一张空表，暂挂状态就会解除 建立一个空文件，这样不用删除表就可以把锁的表清空了 load from blank.del of del terminate into newstaff; 3)备份一下数据库，用于解除表空间的backup pending状态，此状态下不能查询或更新表 backup db sample user db2admin using db2admin tablespace (ts_user_data) to .\ restore db sample tablespace(ts_user_data) online from  rollforward db sample to end of logs and complete tablespace (ts_user_data) online 5 db2move  语法：db2move dbname action option action:export 导出|import 导入|load 装载|copy 数据库间数据复制 option: -tc table-definers : 只适用export，指定表定义者 -tn table-names : 只适用export|copy，指定表名,默认是所有表名 -sn schema-names : 只适用于export，指定模式名，默认为所有模式名 -ts tablespace-names : 只适用于export，指定表空间名称 -tf filename : 只适用于export，采用外部文件列表的方式指定导出数据的表名，表之间换行 -io import-option : 导入选项,insert|insert_update|replace|create|replace_create -lo load-options : 装载选项，insert|replace，默认为insert -l lobpaths : 只适用于export|import，工作目录，默认为当前目录  -u userid : 指定用户名 -p password : 指定密码 -aw : 允许警告 -co copy : 数据复制 target_db dbname user userid using password : 指定目标数据库名和连接账号 mode : ddl_and_load|ddl_only|load_only schema_map : 允许用户更改模式名,使用如:"schema_map ((s1,t1),(s2,t2))" tablespace_map : 更换表空间，使用如："tablespace_map ((TS1, TS2),(TS3, TS4))" 例： db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt USER myuser1 USING mypass1 db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt USER myuser1 USING mypass1 SCHEMA_MAP ((schema1,newschema1)) TABLESPACE_MAP ((ts1,ts2), SYS_ANY)) 注意事例：表中含generated always identity column，在插入数据时插不进去 db2move dbname export db2move dbname import 会生成db2move.lst、EXPORT.out、tabn.ixf和tabn.msg db2move.lst : 成功导出表列表，包含表名列表与tabn的关系描述 EXPORT.out : 不用说就是导出日志 tabn.ixf : 采用PC/IXF格式的数据库表数据，包含表结构、索引和表数据 tabn.msg : 某个表的导出情况 举例：保留最近10天的数据 db2move_data.bat  [批处理] ---------------------------------- cd /d F:\Dispatch\540\db2move rd /s/q data10 rename data09 data10 rename data08 data09 rename data07 data08 rename data06 data07 rename data05 data06 rename data04 data05 rename data03 data04 rename data02 data03 rename data01 data02 mkdir data01 cd data01 db2move infodms export -aw 在192.168.3.247的590张表使用export和db2move导出数据比较： db2move.bat  15:16->15:22 用时6分钟，3.36GB大小，使用winrar压缩成rar格式191MB export.bat   15:42->15:48 用时不到6分钟，2.47GB大小，使用winrar压缩成rar格式，使用winrar压缩成rar格式是187MB 6 实验专题 6.1 实验：Load是否影响数据库恢复 1. 在20120401110939时做了online全备 include logs 使用日志S0000003.LOG~S0000003.LOG 2. 做load操作时日志使用S0000004.LOG，做完load操作后，手工切换到S0000005.LOG 3. 后面做一些操作，日志切换到S0000006.LOG 4. 删除数据库，使用20120401110939恢复，并使用S0000003.LOG~S0000006.LOG来前滚 最终结果是：复原暂挂 0x0100 6.2 实验：不记日志DML操作影响数据库恢复 1. 在20120401110939时做了online全备 include logs 使用日志S0000003.LOG~S0000003.LOG 2. 使用上面备份恢复，当前日志为S0000004.LOG tab1:insert+手工切换，活动日志为S0000006.LOG tab2:insert+手工切换，活动日志为S0000008.LOG tab3:insert+手工切换，活动日志为S0000010.LOG 这样表tab1、tab2和tab3都有记录 3. 不记日志删除 db2 -t +c ---------------------- alter table tab1 activate not logged initially; delete from tab1; alter table tab3 activate not logged initially; delete from tab3; 操作之后，当前活动日志为S0000012.LOG 4. 删除数据库利用20120401110939的全备和数据库日志S0000003.LOG~S0000012.LOG，发现做过不记日志方式的表状态为不可用。 不清楚是否能通过800电话提供密码之后强制将表置为可用。 最终结果：做不记日志DML操作的表不可用。

3.db2导入实用程序

DB2 IMPORT 实用程序 IMPORT 实用程序概述 IMPORT 实用程序用一个输入文件将数据填充到一个表中，输入文件的文件类型可以是  ASC、DEL、IXF 或 WSF。目标是一个表、一个类型化表（typed table）或者一个视图。但是，不能导入到系统表、临时表和物化查询表。建议使用  MESSAGES 子句，以便记录错误、警告和包含有用信息的消息。 要想成功地导入数据，必须拥有 SYSADM 或 DBADM  权限，或者目标表或数据库上的底层特权（SELECT、INSERT、CONTROL  或 CREATETAB），这取决于使用什么选项。为了将数据导入到一个包含受保护的行和列的表中，必须拥有允许对表中所有受保护数据进行写访问的 LBAC 凭证。此外，将数据导入包含受保护行的表时，要求您的 LBAC 凭证是保护表的安全策略的一部分。 下面显示的 IMPORT 命令具有五个不同的选项： IMPORT FROM file_name OF file_type     MESSAGES message_file     [ INSERT | INSERT_UPDATE | REPLACE | REPLACE_CREATE | CREATE ]     INTO target_table_name                  INSERT 选项将导入的数据插入表中。目标表必须已经存在。  INSERT_UPDATE 将数据插入表中，或者更新表中具有匹配主键的行。目标表必须已经存在，并且定义了一个主键。  REPLACE 选项删除所有已有的数据，并将导入的数据插入到一个已有的目标表中。  使用 REPLACE_CREATE 选项时，如果目标表已经存在，则导入实用程序删除已有的数据，并插入新的数据，就像 REPLACE 选项那样。如果目标表还没有定义，那么首先创建这个表以及它的相关索引，然后再导入数据。正如您可能想像的那样，输入文件必须是  PC/IXF 格式的文件，因为那种格式包含对导出表的结构化描述。如果目标表是被一个外键引用的一个父表，那么就不能使用 REPLACE_CREATE。  CREATE 选项首先创建目标表和它的索引，然后将数据导入到新表中。该选项惟一支持的文件格式是 PC/IXF。还可以指定新表所在表空间的名称。  例子： IMPORT FROM emp.ixf OF IXF     MESSAGES msg.out     CREATE INTO employee IN datatbsp INDEX IN indtbsp 回页首  IMPORT 选项 IMPORT 基本上是用于成批插入数据的一个实用程序。这种成批插入操作就像一般的插入语句一样，也涉及到活动的日志记录、索引的更新、参照完整性检查和表约束检查。默认情况下，IMPORT  只在操作结束时提交一次。如果将大量的行一次性导入或插入到表中，那么需要有足够的事务记录用于回滚和恢复。此外也可以采用周期性的提交，以防日志写满。通过定期地提交插入，还可以减少导入操作期间出现失败时丢失的行数。COMMITCOUNT 选项规定在导入一组记录后强制执行 COMMIT。还可以指定 AUTOMATIC 选项，该选项允许导入实用程序在内部决定何时需要执行提交。该实用程序将考虑发出一个提交命令，以避免日志写满或者避免锁升级。下面是关于如何使用  COMMITCOUNT 选项的一个例子： IMPORT FROM myfile.ixf OF IXF     COMMITCOUNT 500      MESSAGES msg.out     INSERT INTO newtable 如果由于某种原因导致以上命令在执行期间遭到失败，那么可以使用消息文件来确定成功导入且已提交的最后一行。然后，可以使用 RESTARTCOUNT 选项重新开始导入。注意，SKIPCOUNT 选项的行为与 RESTARTCOUNT 是相同的。在下面的命令中，该实用程序在开始 IMPORT 操作之前，将忽略前 30,000 条记录。 IMPORT FROM myfile.ixf OF IXF     COMMITCOUNT 500 RESTARTCOUNT 30000 ROWCOUNT 100000      MESSAGES msg.out     INSERT INTO newtable 注意，这个例子中还使用了 ROWCOUNT 选项。该选项指定要导入的物理记录的条数。由于使用了 RESTARTCOUNT 选项，导入实用程序将忽略前 30,000 条记录，并且将剩下的 100,000 条记录导入到表中。 默认情况下，在插入任何行之前，导入实用程序将获得目标表上的一个排它锁。一旦导入完成，这个排它锁将被释放。这是 ALLOW NO ACCESS 选项的行为。为了允许并发应用程序访问表数据，可以使用  ALLOW WRITE ACCESS 选项。注意，该选项与 REPLACE、CREATE 或 REPLACE_CREATE 导入选项不兼容。下面是关于 ALLOW WRITE ACCESS 选项的一个例子。 IMPORT FROM myfile.ixf OF IXF     ALLOW WRITE ACCESS     MESSAGES msg.out     INSERT INTO newtable 回页首  导入 XML 数据 为了导入 XML 文件，可以使用 XML FROM 选项指定 XML 文件所在的一个或多个路径。否则，导入实用程序将在当前目录中查找 XML 文件。您可以选择如何解析 XML 文档；是去掉空白还是保留空白。如果没有指定  XMLPARSE 选项，那么将根据 CURRENT XMLPARSE OPTION 专用寄存器来决定对 XML 文档的解析行为。下面是关于 XML FROM 和 XMLPARSE 选项的一个例子。 IMPORT FROM myfile.ixf OF IXF     XML FROM d:\xmlpath     XMLPARSE PRESERVE WHITESPACE     MESSAGES msg.out     INSERT INTO newtable 当插入或更新一个 XML 文档时，您可能想确定 XML 文档的结构、内容和数据类型是否有效。导入实用程序还通过 XMLVALIDATE 选项提供了对 XML 验证的支持。下面是可用的三种方法。 USING XDS —— 回想一下，您可以导出 XML 模式信息并将它存储在 XML Data Specifier  （XDS） 的 SCH 属性中。SCH 属性的值将用于执行验证。如果在 XDS 中没有 SCH 属性，则考虑 DEFAULT、IGNORE 或 MAP 三者之中的一个值。  USING SCHEMA schema-sqlid ——  使用这个子句中指定的 XML 模式。  USING SCHEMALOCATION HINTS —— 根据源 XML 文档中 XML 模式位置提示所标识的模式来验证 XML 文档。  IMPORT FROM myfile.ixf OF IXF     XML FROM d:\xmlpath     XMLPARSE PRESERVE WHITESPACE     XMLVALIDATE USING XDS          DEFAULT S1.SCHEMA_A         IGNORE (S1.SCHEMA_X, S1.SCHEMA_Y, S1.SCHEMA_Z)         MAP (S1.SCHEMA_A, S1.SCHEMA_B)     COMMITCOUNT 500 RESTARTCOUNT 30000      MESSAGES msg.out     INSERT INTO newtable 前面的 IMPORT 命令将： 插入 myfile.ixf 和 d:\xmlpath 下的 XML 文件中的数据。  当解析 XML 文档时，保留空白。  使用 XDS 的 SCH 属性标识的模式信息对每个 XML 文档进行验证。但是，如果用于任何特定行的 XDS 没有包含 SCH 属性，那么使用 S1.SCHEMA_A。  如果 SCH 属性被指定为 S1.SCHEMA_X 或 S1.SCHEMA_Y 或 S1.SCHEMA_Z，那么对于导入的 XML 文档不执行验证。  如果 SCH 属性被指定为 S1.SCHEMA_A，它将被映射到 S1.SCHEMA_B。注意，尽管 DEFAULT 子句指定了 S1.SCHEMA_A，但是后面的任何映射都将不会执行。  每导入 500 行之后，导入实用程序将发出一次提交命令。  导入操作从第 30,001 条记录开始。前 30,000 条记录被忽略。  任何错误、警告和包含信息的消息都写到 msg.out 文件中。  将新数据插入（或附加）到 newtable 中。  这个例子只是让您对如何验证导入的 XML 文档有一些认识。DB2 Information Center 中还有更多的例子来演示 XMLVALIDATE 选项的威力。 回页首  文件类型修饰符 IMPORT 实用程序还支持五种文件类型修饰符来定制导入操作。在 DB2 Command Reference 的 IMPORT 小节下有这些修饰符的一个完整的列表。下面列出了其中一些修饰符： compound=x  使用非原子复合 SQL 插入数据。x 是每次将尝试的语句数量。  indexschema=schema  使用索引创建期间指定的用于索引的模式。  striptblanks  将数据装载到可变长度字段时，截去结尾空白。  lobsinfile  表明将导入 LOB 数据。导入实用程序将检查 LOBS FROM 子句，以获得输入 LOB 文件的路径。  下面是使用这些文件类型修饰符的一个例子： IMPORT FOR inputfile.asc OF ASC     LOBS FROM /u/db2load/lob1, /u/db2load/lob2     MODIFIED BY compount=5 lobinsfile     INSERT INTO newtable from IBM developeworks