DB2数据的导入Import 导出ExportLoad.docx

DB2数据的导入Import 导出ExportLoad.docx

《DB2数据的导入Import 导出ExportLoad.docx》由会员分享，可在线阅读，更多相关《DB2数据的导入Import 导出ExportLoad.docx（13页珍藏版）》请在冰点文库上搜索。

DB2数据的导入Import导出ExportLoad

DB2中所谓的数据移动，包括：

1.数据的导入（Import）

2.数据的导出（Export）

3.数据的装入（Load）

导入和装入都是利用DB2的相关命令把某种格式的文件中的数据保存到数据库中的表中

导出是指把DB2数据库的表中的数据保存到某种格式的文件当中去

数据移动的作用：

如果要在不同的数据库管理系统之间转移数据，数据移动通常是最实用的一种方法，因为任何一种数据库管理系统都支持常用的几种文件格式，通过这个通用的接口，就很容易实现不同系统间数据的转移。

这三个命令中，Export最简单，因为从表中向文件转移数据，通常不会出现错误，也不会有非法的数据。

1.ASC——非定界ASCII文件，是一个ASCII字符流。

数据流中的行由行定界符分隔，而行中的每一列则通过起始和结束位置来定义。

例如：

10HeadOffice    160CorporateNewYork

15NewEngland  50    Eastern   Boston

20MidAtlantic    10    Eastern   Washington

38SouthAtlantic30    Eastern   Atlanta

42GreatLakes100Midwest   Chicago

51Plains             140Midwest   Dallas

66Pacific             270Western    SanFrancisco

84Mountain      290Western    Denver

2.DEL——定界ASCII文件，也是一个ASCII字符流。

数据流中的行由行定界符分隔，行中的列值由列定界符分隔。

文件类型修饰符可用于修改这些定界符的默认值。

例如：

10,"HeadOffice",160,"Corporate","NewYork"

15,"NewEngland",50,"Eastern","Boston"

20,"MidAtlantic",10,"Eastern","Washington"

38,"SouthAtlantic",30,"Eastern","Atlanta"

42,"GreatLakes",100,"Midwest","Chicago"

51,"Plains",140,"Midwest","Dallas"

66,"Pacific",270,"Western","SanFrancisco"

84,"Mountain",290,"Western","Denver"

3.WSF——（worksheetformat）为工作表格式，用于与Lotus系列的软件进行数据交换。

4.PC/IXF——是集成交换格式（IntegrationExchangeFormat，IXF）数据交换体系结构的改编版本，由一些列可变长度的记录构成，包括头记录、表记录、表中每列的列描述符记录以及表中每行的一条或多条数据记录。

PC/IXF文件记录由包含了字符数据的字段组成。

第一部分：

数据的导出（Export）

例一：

把Org表中的所有数据导出到文件C:

\ORG.TXT中。

Exporttoc:

\org.txtofdelselect*fromorg

其中，ofdel表示导出到的文件的类型，在本例中导出到一个非定界文本文件中；后面的select*fromorg是一个SQL语句，该语句查询出来的结果就是要导出的数据。

例二：

改变del格式文件的格式控制符

exporttoc:

\staff.txtofdelmodifiedbycoldel$chardel''decplusblankselect*fromstaff

在该例中，modified子句用于控制各种符号，coldel表示字段之间的间隔符，默认情况为逗号，现在改为$号；chardel表示字符串字段用什么符号引用，默认情况下为一对双引号括起来，现在改为用一对单引号括起来；decplusblank表示对于十进制数据类型，用空格代替最前面的加号，因为默认情况下会在十进制数据前面加上正负号的。

例三：

以ASC格式将数据导出到文件

Export命令是不支持ASC格式文件的，所以如果想导出ASC这样规整的格式，需要程序员自己进行转换操作，思路是将各种数据类型都转换成定长字符串，然后把各个要导出的字段合并成为一个字段。

例如创建如下结构的表n：

createtablen（aint,bdate,ctime,dvarchar（5）,echar（4）,fdouble）

然后插入两条数据：

insertintonvalues（15,'2004-10-21','23:

12:

23','abc','hh',35.2）

insertintonvalues（5,'2004-1-21','3:

12:

23','bc','hhh',35.672）

要想把这两条数据以规整的格式导出到文件中，进行如下操作：

exporttoc:

\test.txtofdelselectchar（a）||char（b）||char（c）||char（d,5）||e||char（f）astmpfromn

这样导出的结果与ASC格式的文件非常类似，只是每一行的前后多出了一对双引号，对此我们可以使用文本工具（如写字板、记事本等）把双引号删除掉，也可以置之不理，在以后导入的时候直接控制格式（忽略双引号）

在文件中的格式为：

"15      2004-10-2123.12.23abc  hh  3.52E1               "

"5      2004-01-2103.12.23bchhh3.5672E1            "

例四：

大数据的导出

exporttod:

\myfile.delofdellobstod:

\lob\lobfilelobsmodifiedbylobsinfileselect*fromemp_photo

该命令把emp_photo表的数据导出到d:

\myfile.del文件中，其结果为：

;
"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/"

其中第三个字段是BLOB类型，在该文件中只保存了一个标志，相当于一个指针，真正的LOB数据保存在d:

\lob目录下的lobs.001、lobs.002、......等一系列文件中。

命令中lobsto后面指定大对象数据保存在什么路径下（注意，该路径必须事先已经存在，否则会报错），lobfile后面指定大对象数据保存在什么文件中，不要指定扩展名，DB2会根据数据量自动追加.001、.002等扩展名，同时不要忘记加上modifiedbylobsinfile子句

例五：

把导出信息保存在消息文件中。

exporttod:

\awards.ixfofixfmessagesd:

\msgs.txtselect*fromstaffwheredept=20

这个例子把staff表中dept=20的数据导出到d:

\awards.ixf文件中，所有的导出信息都保存在d:

\msgs.txt文件中（无论是成功、警告还是失败信息），这样，管理员可以通过观察信息文件找到问题所在。

例六：

给导出数据列重命名。

exporttod:

\awards.ixfofixfmethodn（c1,c2,c3,c4,c5,c6,c7）messagesd:

\msgs.txtselect*fromstaffwheredept=20

在默认情况下，导出的每一列数据以表中对应的字段名自动命名，我们可以通过methodn子句给每一列重新命名，需要注意的是，这个子句只在ixf和wsf格式文件中有效，在文本文件中不能使用。

数据的导入

例七：

把C盘根目录下的org.txt文件中的数据导入到org表中

importfromc:

\org.txtofdelinsertintoorg

导入命令和导出命令的格式基本上处于对应的关系，import对应export，from对应to，文件名和文件格式代表的含义相同，但是导入命令支持ASC格式的文件，而导出命令不支持。

另外，在导出命令的最后是一个SQL语句，用于选择要导出的数据，而导入命令最后不是SQL语句，而是插入数据的方式以及目标表名称。

例八：

从ASC格式文件中导入数据

importfrom  c:

\org2.txtofascmethodl（15,619,2025,2637,3850）insertintoorg

其中methodl子句用于指定文本文件中每一个字段的起始位置和终止位置，每个起始位置和终止位置间用空格分开，字段之间用逗号分开。

除了l方法之外，还有n方法和p方法，下面会叙述。

例九：

利用n方法导入数据，并且创建新表。

首先导出一个用例文件：

exporttod:

\org.ixfofixfmethodn（a,b,c,d,e）select*fromorg

这样org.ixf文件中有五列数据，对应的列名分别为a、b、c、d、e

然后在从该文件中导入数据到一个新表中

importfromd:

\org.ixfofixfmethodn（d,e,b）replace_createintoorgtest

该命令从文件中选取三列导入到表中，顺序可以不按照文件中原有的列的顺序。

replace_create方式的叙述见下。

插入方式有：

INSERT方式——在表中现有数据的基础之上追加新的数据。

INSERT_UPDATE方式——这种方式只能用于有主键的表，如果插入的数据与原有数据主键不冲突，则直接插入，如果主键冲突，则用新的数据代替原有数据。

REPLACE方式——先把表中现有的数据都删除，然后向空表中插入数据。

REPLACE_CREATE方式——表示如果表存在，则先把表中的数据都删除，然后向空表中插入数据；如果表不存在，则先根据文件中的字段创建表，然后再向表中插入数据。

这种方式只能把IXF格式的文件中的数据插入到表中。

例十：

利用p方法导入数据

importfromd:

\org.ixfofixfmethodp（4,5,2）replaceintoorgtest

该例子执行的效果和例九类似，只是把n方法换成了p方法，p方法后面的列表中指明列的序号即可，不需要指明列名。

另外，此例中使用了replace方式插入数据，这会把表中现有的数据都删除，然后向空表中插入数据。

例十一：

关于空值的导入

对于ixf格式的文件，导入空值非常方便，因为里面已经记录了空值的信息。

但是，对于ASC格式文件就有一定的难度了，因为DB2会直接插入空格，而不是空值。

为此，DB2提供了一个子句进行控制：

NULLINDICATORS

importfrom  c:

\org2.txtofascMODIFIEDBYnullindchar=#methodl（15,619,2025,2637,3850）NULLINDICATORS（0,0,0,0,38）replaceintoorg

在这个例子中，NULLINDICATORS子句后面是一个列表，表示前面四个字段都不会存在空值，而第五个字段从38列开始，可能存在空值，而MODIFIEDBYnullindchar=#子句表示在文件中第五个字段如果遇到#号，则表示为空值。

装入（Load）

装入命令格式与导入类似，命令关键字是Load，但是后面的参数比导入命令多的多，详细用法可以自行参考DB2文档。

装入与导入类似，都是将输入文件中的数据移入到目标表中，二者的不同点将在实例中逐步解释。

在装入之前，目标表必须已经存在。

装入的性能比导入高，原因在后面结合实例详细解释。

装入操作不记录到日志中，所以不能使用日志文件进行前滚操作。

装入分为4个阶段：

1.装入阶段

在这个阶段发生两件事：

数据存储在表中，收集索引键并排序。

在装入时，DBA可以指定多长时间生成一致点。

它是装入工具的检查点。

如果装入在执行期间被打断，它可以从最后一个一致点处开始继续重新执行。

2.构建阶段

在构建阶段，基于在装入阶段收集的索引键信息创建索引。

如果在构建阶段发生错误，装入工具就重启，它将从构建阶段开始处重新开始构建。

3.在删除阶段，所有违反唯一或主键约束的行都被删除并拷贝到一个异常表（如果在语句中指定相应选项）中。

当输入行被拒绝，消息文件中就生成消息。

4.索引拷贝阶段

如果在装入操作期间为索引创建指定了系统临时表空间，并且选择了READACCESS选项，该索引数据将从系统临时表空间拷贝到原来的表空间。

装入过程的所有四个阶段都是操作的一部分，只有在所有的四个阶段都完成之后，该装入操作才算完成。

在每个阶段都将生成消息，一旦其中的某个阶段发生错误，这些消息可以帮助DBA分析并解决问题。

导入操作每次插入一行数据时都要检查是否满足约束条件，并且记入日志文件中。

下面我们看一些LOAD命令特有的功能，IMPORT命令也能做的就不再详细说了。

例十二：

从光标类型文件中进行装入

定义一个cursor

declaremycurcursorforselect*fromorg

创建一个新表，结构与cursor相容

createtableorg2likeorg

从cursor中装入

loadfrommycurofcursorinsertintoorg2

除了可以从cursor中装入，还可以从文件、管道、设备中进行装入。

而导入命令只能从文件中导入。

例十三：

关于异常表

由用户定义的异常表可以用于存储不遵循唯一约束和主码约束的行。

如果装入的时候没有指定异常表，则违反唯一约束的行将被丢弃并且将不再有机会恢复或修改。

用SAMPLE数据库中的STAFF表做实验

1.创建一个结构与STAFF表相同的表STAFF1

CREATETABLESTAFF1LIKESTAFF

2.把STAFF表中的一部分数据插入到STAFF1中

INSERTINTOSTAFF1SELECT*FROMSTAFFWHEREID<=160

3.再创建一个结构与STAFF1相同的表STAFFEXP，作为异常表

CREATETABLESTAFFEXPLIKESTAFF1

4.给该异常表添加一列，因为异常表和普通表相比，前面的结构都相同，就是最后多出一列或两列（列名任意），第一列是时间戳类型，记录异常记录插入的时间，第二列是大文本类型（至少为32K大小），保存导致该条记录被拒绝的特定约束信息。

本例中只添加一个时间戳列。

ALTERTABLESTAFFEXPADDCOLUMNTIMETIMESTAMP

5.为STAFF1表创建一个唯一索引

CREATEUNIQUEINDEXIDXSTAFFONSTAFF1（ID）

6.先运行导出命令做出一个文本文件

EXPORTTOD:

\STAFF.TXTOFDELSELECT*FROMSTAFF

7.然后运行装入命令把数据再装入到STAFF1表中

LOADFROMD:

\STAFF.TXTOFDELINSERTINTOSTAFF1FOREXCEPTIONSTAFFEXP

由于表STAFF1中有唯一索引，所以会有一部分数据因为违反这个约束条件而不能插入到STAFF1表中，这些记录就会插入到异常表STAFFEXP中。

注意一点，异常表必须自己先定义好，装入命令不能够自动生成异常表，如果找不到指定的异常表，就会报错。

例十四：

关于DUMP文件

格式不正确的行会被拒绝。

通过指定DUMPFILE文件类型修饰符可以使这些被拒绝的记录单独放在指定的文件里。

用SAMPLE数据库中的STAFF表做实验

1.创建一个结构与STAFF表相同的表STAFF1

CREATETABLESTAFF1LIKESTAFF

2.把STAFF表中的一部分数据插入到STAFF1中

INSERTINTOSTAFF1SELECT*FROMSTAFFWHEREID<=160

3.再创建一个结构与STAFF1相同的表STAFFEXP，作为异常表

CREATETABLESTAFFEXPLIKESTAFF1

4.给该异常表添加一列

ALTERTABLESTAFFEXPADDCOLUMNTIMETIMESTAMP

5.为STAFF1表创建一个唯一索引

CREATEUNIQUEINDEXIDXSTAFFONSTAFF1（ID）

6.先运行导出命令做出一个文本文件

EXPORTTOD:

\STAFF.TXTOFDELSELECT*FROMSTAFF

到D盘上打开STAFF.TXT文件，把第一列等于320的行替换为：

"abcf","aaa","sdfg"

7.然后运行装入命令把数据再装入到STAFF1表中

LOADFROMD:

\STAFF.TXTOFDELMODIFIEDBYDUMPFILE=d:

\dumpINSERTINTOSTAFF1FOREXCEPTIONSTAFFEXP

装入的结果报告中会有如下一条：

SQL3118W  在行"32"列"1"中的字段值不能转换为SMALLINT值，但是目标列不可为空。

未装入该行。

SQL3185W  当处理输入文件的第"32"行中的数据时发生先前的错误。

打开D盘的dump.000文件，会看到造成异常的那一行数据：

"abcf","aaa","sdfg"

通过这个例子，我们可以理解，如果一行数据的格式不正确，在装入的时候会遭到拒绝，该行记录会放到DUMP文件中；而如果数据格式正确，但是不满足表的约束条件，该行记录会放到异常表中。

例十五：

限制装入行数

用ROWCOUNT选项可以指定从文件开始处装入的记录数

LOADFROMD:

\STAFF.TXTOFDELROWCOUNT3INSERTINTOSTAFF1

例十六：

出现警告信息时强令装入操作失败

在某些情况下，文件中的数据必须全部成功输入到目标表中才算成功，即使有一条记录出错也不行。

在这种情况下，可以使用WARNINGCOUNT选项。

到D盘上打开STAFF.TXT文件，把第一列等于320的行替换为：

"abcf","aaa","sdfg"

LOADFROMD:

\STAFF.TXTOFDELWARNINGCOUNT1INSERTINTOSTAFF1

运行结果包含下面的警告：

SQL3118W  在行"32"列"1"中的字段值不能转换为SMALLINT值，但是目标列不可为空。

未装入该行。

SQL3185W  当处理输入文件的第"32"行中的数据时发生先前的错误。

SQL3502N  实用程序遇到了"1"个警告，它超过了允许的最大警告数。

此时无法对表STAFF1进行操作，例如

SELECT*FROMSTAFF1

会返回：

ID    NAME   DEPTJOBYEARS  SALARYCOMM

--------------------------------------------------

SQL0668N  由于表"USER.STAFF1"上的原因代码"3"，所以不允许操作。

SQLSTATE=57016

原因是：

表处于“装入挂起”状态。

对此表的先前的LOAD尝试失败。

在重新启动或终止LOAD操作之前不允许对表进行存取。

解决方法为：

通过分别发出带有RESTART或TERMINATER选项的LOAD来重新启动或终止先前失败的对此表的LOAD操作。

包含TERMINATER的LOAD命令可以终止装入进程，使目标表恢复正常可用状态：

LOADFROMD:

\STAFF.TXTOFDELTERMINATEINTOSTAFF1

包含RESTART的LOAD命令可以在源文件修改正确的时候使用，使装入进程重新开始：

LOADFROMD:

\STAFF.TXTOFDELRESTARTINTOSTAFF1

例十七：

防止产生警告信息

使用NOROWWARNINGS文件类型修饰符可以禁止产生警告信息，当装入过程可能出现大量警告信息，而用户对此又不感兴趣的时候，可以使用该选项，这样可以大大提高装入的效率

到D盘上打开STAFF.TXT文件，把第一列等于320的行替换为：

"abcf","aaa","sdfg"

LOADFROMD:

\STAFF.TXTOFDELMODIFIEDBYNOROWWARNINGSINSERTINTOSTAFF1

运行完的结果中，第32行出错，该行无法装入，但是不产生警告信息。

例十八：

生成统计数据

使用STATISTICS选项可以在装入的过程中生成统计数据，这些统计数据可以供优化器确定最有效的执行SQL语句的方式。

可以对表和索引产生不同详细程度的统计数据：

①对表和索引产生最详细的统计数据：

LOADFROMD:

\STAFF.TXTOFDELREPLACEINTOSTAFF1STATISTICSYESWITHDISTRIBUTIONANDDETAILEDINDEXESALL

②对表和索引都产生简略的统计：

LOADFROMD:

\STAFF.TXTOFDELREPLACEINTOSTAFF1STATISTICSYESANDINDEXESALL

其它组合可以参考DB2文档。

注意：

STATISTICS选项只能和REPLACE兼容，与INSERT选项不兼容。

另外，通过STATISTICS选项做完统计，我们看不到任何直接的结果，如果想查看其结果，需要到系统表中自己查询