db2导入导出数据.docx
- 文档编号:16895537
- 上传时间:2023-07-19
- 格式:DOCX
- 页数:15
- 大小:22.69KB
db2导入导出数据.docx
《db2导入导出数据.docx》由会员分享,可在线阅读,更多相关《db2导入导出数据.docx(15页珍藏版)》请在冰点文库上搜索。
db2导入导出数据
DB2数据的导入&导出
DB2数据的导入(Import)导出(Export)(Load)DB2中所谓的数据移动,包括:
1.数据的导入(Import)
2.数据的导出(Export)
3.数据的装入(Load)
导入和装入都是利用DB2的相关命令把某种格式的文件中的数据保存到数据库中的表中
导出是指把DB2数据库的表中的数据保存到某种格式的文件当中去
数据移动的作用:
如果要在不同的数据库管理系统之间转移数据,数据移动通常是最实用的一种方法,因为任何一种数据库管理系统都支持常用的几种文件格式,通过这个通用的接口,就很容易实现不同系统间数据的转移。
这三个命令中,Export最简单,因为从表中向文件转移数据,通常不会出现错误,也不会有非法的数据。
在讲解命令之前,首先介绍一下文件的格式,用于DB2数据移动的文件格式有四种:
1.ASC——非定界ASCII文件,是一个ASCII字符流。
数据流中的行由行定界符分隔,而行中的每一列则通过起始和结束位置来定义。
例如:
10HeadOffice160CorporateNewYork
15NewEngland50EasternBoston
20MidAtlantic10EasternWashington
38SouthAtlantic30EasternAtlanta
42GreatLakes100MidwestChicago
51Plains140MidwestDallas
66Pacific270WesternSanFrancisco
84Mountain290WesternDenver
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格式的文件非常类似,只是每一行的前后多出了一对双引号,对此我们可以使用文本工具(如写字板、记事本等)把双引号删除掉,也可以置之不理,在以后导入的时候直接控制格式(忽略双引号)
在文件中的格式为:
"152004-10-2123.12.23abchh3.52E1"
"52004-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格式文件中导入数据
importfromc:
\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
importfromc:
\org2.txtofascMODIFIEDBYnullindchar=#methodl(15,619,2025,2637,3850)NULLINDICATORS(0,0,0,0,38)replaceintoorg
在这个例子中,NULLINDICATORS子句后面是一个列表,表示前面四个字段都不会存在空值,而第五个字段从38列开始,可能存在空值,而MODIFIEDBYnullindchar=#子句表示在文件中第五个字段如果遇到#号,则表示为空值。
就说这些吧,抛砖引玉,希望大家补充,下一次谈一谈Load命令。
装入(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
会返回:
IDNAMEDEPTJOBYEARSSALARYCOMM
--------------------------------------------------
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选项做完统计,我们看不到任何直接的结
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- db2 导入 导出 数据