装入命令格式与导入类似,命令关键字是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
例十六:出现警告信息时强令装入操作失败 到D盘上打开STAFF.TXT文件,把第一列等于320的行替换为:"abcf","aaa","sdfg" LOAD FROM D:\STAFF.TXT OF DEL WARNINGCOUNT 1 INSERT INTO STAFF1 运行结果包含下面的警告: 此时无法对表STAFF1进行操作,例如 原因是:表处于“装入挂起”状态。对此表的先前的 LOAD 尝试失败。在重新启动或终止 LOAD 操作之前不允许对表进行存取。 解决方法为:通过分别发出带有 RESTART 或 TERMINATER 选项的 LOAD 来重新启动或终止先前失败的对此表的 LOAD 操作。 包含TERMINATER的LOAD命令可以终止装入进程,使目标表恢复正常可用状态: 包含RESTART的LOAD命令可以在源文件修改正确的时候使用,使装入进程重新开始:
例十七:防止产生警告信息 到D盘上打开STAFF.TXT文件,把第一列等于320的行替换为:"abcf","aaa","sdfg" LOAD FROM D:\STAFF.TXT OF DEL MODIFIED BY NOROWWARNINGS INSERT INTO STAFF1 运行完的结果中,第32行出错,该行无法装入,但是不产生警告信息。 例十八:生成统计数据 ① 对表和索引产生最详细的统计数据: ② 对表和索引都产生简略的统计: 其它组合可以参考DB2文档。 注意:STATISTICS选项只能和REPLACE兼容,与INSERT选项不兼容。 另外,通过STATISTICS选项做完统计,我们看不到任何直接的结果,如果想查看其结果,需要到系统表中自己查询。 例十九:解除检查挂起状态 2. 创建一个结构与staff表相同的表: 3. 给该表添加一个检查约束: 4. 到D盘上打开STAFF.TXT文件,把最后一行数据的第三列改为150,这样该条数据就不满足第3步加上的检查约 束条件了,然后用Load命令从文件中装入数据到staff1表中: 5. 此时运行查询命令: 6. 解除表的检查挂起状态,使用:
例二十:性能因素 另外,装入命令还有一些选项可以控制性能因素: ② Copy No(默认选项):在这种情况下,如果表所在数据库的归档日志处于启用状态,则装入完成后,表所在的表空间将处于备份挂起状态,直到数据库或表空间备份完毕,该表空间才成为可写表空间。原因是装入操作造成的变化没有被记录,所以要恢复装入操作完成后发生的故障,备份数据库或表空间是必要的。 ③ Copy Yes:在这种情况下,如果数据库的归档日志启用,装入操作的改变将被保存到磁带、目录或TSM服务器,并且表空间将不再处于备份挂起状态。 2. Fastparse 3. Anyorder 4. Data Buffer 5. CPU_PARALLELISM 6. Disk_Parallelism
在某些情况下,文件中的数据必须全部成功输入到目标表中才算成功,即使有一条记录出错也不行。在这种情况下,可以使用WARNINGCOUNT选项。
SQL3118W 在行 "32" 列 "1" 中的字段值不能转换为 SMALLINT值,但是目标列不可为空。未装入该行。
SQL3185W 当处理输入文件的第 "32" 行中的数据时发生先前的错误。
SQL3502N 实用程序遇到了 "1" 个警告,它超过了允许的最大警告数。
SELECT * FROM STAFF1
会返回:
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- ---------
SQL0668N 由于表 "USER.STAFF1" 上的原因代码 "3",所以不允许操作。
SQLSTATE=57016
LOAD FROM D:\STAFF.TXT OF DEL TERMINATE INTO STAFF1
LOAD FROM D:\STAFF.TXT OF DEL RESTART INTO STAFF1
使用NOROWWARNINGS文件类型修饰符可以禁止产生警告信息,当装入过程可能出现大量警告信息,而用户对此又不感兴趣的时候,可以使用该选项,这样可以大大提高装入的效率
使用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
1. 连接到SAMPLE数据库上:
Connect to sample
CREATE TABLE STAFF1 LIKE STAFF
alter table staff1 add constraint chk check(dept<100)
LOAD FROM D:\STAFF.TXT OF DEL INSERT INTO STAFF1
Select * from staff1
会得到错误信息:
SQL0668N 由于表 "USER.STAFF1" 上的原因代码 "1",所以不允许操作。
SQLSTATE=57016
原因是装入时有数据违反了检查约束,造成表处于检查挂起状态。
set integrity for staff1 check immediate unchecked
再次运行查询命令:
Select * from staff1
发现表可以正常使用了,其中的违反检查规则的数据也存在。
在从文件向表导入数据的时候,当数据量特别大的情况下,装入命令会明显体现出优势,原因是它不像导入命令每次插入一行,并且在每行都要检查是否满足约束条件,装入命令从输入文件读出数据构建页,把这些页直接写入数据库,并且在每一行数据装入时不判断是否满足约束,另外装入命令不写日志,所有这些因素都导致装入的效率高于导入。
1. COPY YES/NO和Nonrecoverable
① Nonrecoverable(不可恢复的):指定装入操作不可恢复,并且不能由后续的前滚操作恢复。前滚操作忽略事务并且标记正在装入数据的表为“无效”。
该文件类型修饰符用于减少数据检查次数。它只能用于在数据已知正确的情况下,尤其适用于DEL和ASC类型的文件。
如果SAVECOUNT选项没有使用,该参数允许不遵照输入文件中的数据顺序进行装入,在SMP(对称多处理机)系统上CPU_PARALLELISM选项大于1的时候,该参数会提高装入的性能。
该参数用于指定从堆栈分配得到的4K大小的内存页面的数目,作为装入的内部缓冲区,指定一个大缓冲区有助于提高装入的性能。
该选项只能用于SMP系统上,可以指示使用多少进程或线程解析、转换、格式化数据。
该选项指定写数据到磁盘的进程或线程的数目。