DB2 导入导出格式分析

在DB2数据库维护中，有项工作可能经常需要处理。如在数据库投入生产使用之前，往往需要在数据库中建立大量的信息。这步工作很多都是通过数据导入来完成的，即将数据直接从某个文件中导入到数据库系统中。不过在数据导入之前，DB2数据库系统往往会对外部文件的格式进行仔细的检查。

　　如果外部文件的格式不符合数据库的强制性要求，数据库就会拒绝导入数据。笔者这次要给大家介绍的就是DB2数据库支持哪些外部文件格式。希望通过这个分析能够帮助数据库管理员减轻数据导入导出过程中的工作量。
导出格式有：del  ixf wsf 
　　一、定界的ASCII文件。 定界ASC (DEL)

　　定界的ASCII文件，在数据库领域内应用的是比较广泛的。基本上任何一个数据库都支持这种格式的外部文件。不过各个数据库对于定界ASCII文件的分隔符的要求是不同的。在DB2数据库中，（提高DB2数据备份与恢复的效率）分隔符好用来标识数据元素的起始位置和结束位置。一般来说，DB2数据库支持如下几种分隔符。一是字符分隔符。这个字符分隔符主要用来界定字符型数据类型的起始位置。默认情况下，在DB2数据库中使用双引号来作为字符分隔符。这个跟其他数据库有所不同。如在Oracle数据库中，利用定界的ASCII文件导入数据的时候，其会自动判断数据是否是字符型的数据类型，即不需要使用这个字符分隔符。二是列分隔符，即用来界定字段的起始位置。大部分情况下，都采用逗号(英文状态)来作为列的分隔符。不过用户可以根据自己的需要来设置不同的列分隔符，如冒号或者分号等等。无论是哪一种符号来做列分隔符，一般情况下都需要满足一个前提条件，即必须是英文输入环节下的符号。如果是中文状态下的逗号或者分号，则会当作普通字符来对带。三是行分隔符。行分隔符是一个比较特殊的分隔符，因为其所采取的操作系统平台不同，其采用的行分隔符是不同的。如在Windows操作系统上，行分隔符就是普通的换行符。而在Linux等操作系统上，虽然也是换行符，但是两个操作系统上换行符是不同的。如在Linux操作系统上打开Windows操作系统上建立的定界ASCII文件，则格式会混乱。如所有记录在同一行中显示，好像没有分行一样。为此在导入数据时，如果这个定界文件是在Windwos操作系统上建立的，而DB2数据库则是不属在Linux操作系统上，就无法正常导入导DB2数据库中。而需要先对格式进行转换，将换行符转换为Linux操作系统所能够识别的换行符。这一点数据库管理员必须要引起足够的重视。（在DB2数据库里面如何更新执行计划）

　　根据上面的规则，如下记录就是DB2数据库所能够支持的格式：

　　1001,”office”,2323,”BeiJing”,-233

　　注意，如果在外部文件中，字符串的值中包含列分隔符时(如逗号列分隔符)，则这个字符串分隔符是必须的。也就是说，如果要导入的数据是字符型数据的话，必须要使用双引号括起来。如果没有列分隔符好的话，则这个字符串分隔符号可有可无。如Bei,Jing这个字符串，如果不用双引号括起来的话，DB2数据库系统就会认为他是两个字段，而不是一个字符串。此时就需要使用字符分隔符将其引用起来，告诉数据库系统这是一个字符串。虽然这个定界的ASCII文件可以用来转换大多数的数据类型，如字符型数据、数字型数据、日期型数据等等。但是如果外部文件中包含二进制数据的话，则采用这个定界的ASCII文件不是很好的选择。另外就是如果文件建立的平台与DB2数据库所在的平台所采用的操作系统平台不同，也不建立采用这个ASCII定界文件。如果遇到这种情况的话，笔者建立采用PC集成交换格式的外部文件。最后需要提醒的是，在定界的ASCII文件中，数据行的最大长度没有限制。但是单独列的值的最大长度不能够超过32KB。则只是从ASCII文件的角度来说的。在将文件导入到数据库的时候，还需要注意DB2数据库表中字段的大小能否容纳这个外部文件中字段的值。

　　二、固定记录长度的ASCII文件。 非定界或定长ASCII （ASC）


　　DB2数据库还支持固定长度的ASCII外部文件格式，有时候也将这种格式的文件叫做位置ASCII文件。固定记录长度位置的ASCII文件格式使用固定的字节偏移量来替代记录分隔符以决定各个字段的界限。简单的说，在这种文件格式下，文件中的每一个记录(记录中的每一个字段)都有固定的长度。如第N条记录的起始位置就是记录的固定长度乘以N的乘积决定。所计算出来的值就是从文件起点算起的字节偏移量。不仅记录行的长度是固定的，而且每条记录中每个列的值的起始位置和终止位置也是相同的。即每个字段的长度是相同的。不过字段实际内容没有达到这个固定宽度的话，则往往是使用空格符来代替。

　　不过这个文件格式比起比起上面所讲的定界的ASCII文件有很大的缺陷。如制作这个文件非常的麻烦，而且很容易出错。多输入一个空格与少输入一个空格都会导致数据库导入错误。如中文字符与英文字符所占的位置是不同的。如果数据文件中同时包含中文与英文字符的话，那么这个固定长度就很难控制。所以说，笔者跟很多数据库管理员一样，并不建立采用这个固定长度的ASCII文件格式。如果一定要使用的话，那么也要采用专业的工具来转换这个文件。如在Linux操作系统中，可以利用重定向符号来制作固定宽度的ASCII文件。总之，如果手工建立这个文件的话，则出错的可能性很大。现在的DB2数据库系统，虽然某些导入工具还支持这种格式的外部文件。但是在数据导出的时候，已经放弃了这个格式。可见这种格式的文件正在退出历史舞台。

　　三、二进制格式的文件。

　　以上两种文件有一个共同的特点，即都不是以二进制形式存储的。为此他们在应用的时候会遇到一个兼容性的文件。如在Linux操作系统平台上将某张表导出为ASCII文件。在Windows操作系统平台上，无法将这个文件导入到DB2数据库系统中。主要是因为同样是ASCII文件，在不同的操作系统中(如Linux操作系统或者Windows操作系统)，其存储的格式是不同的。为此通过ASCII文件是无法在不同的操作系统上进行数据互导的。其实不光光是DB2 数据库遇到这种困扰，在Oracle数据库也是如此。

　　为了解决问题，在DB2数据库中涉及了二进制格式的外部文件。这个二进制格式的数据文件主要有两种，分别为PC集成交换格式和工作表单格式。PC集成交换格式文件主要用于在不同的操作系统平台上，如Linux、Windows操作系统平台上的DB2数据库表之间进行数据传递。即通过这种格式的二进制文件，可以将数据从Linux操作系统上的数据库中到出来，然后导入导Windows操作系统上的DB2数据库中。这个文件主要的特点就是，其存储的不光光是记录的内容，还存储了表结构的定义。而且因为是二进制格式的文件，所以在不同操作系统上可以容易的转移，而不用担心兼容性的问题。

　　另外一种二进制文件的格式是工作表单格式。这也是DB2数据库产品的一种特殊的格式文件。这个工作表单格式文件的用途与PC集成交换格式的文件用途不同。PC集成交换格式文件主要用来解决的是不同操作系统平台上数据迁移时的兼容性问题。而这个工作表单格式的文件其主要用途则是用来解决DB2不同产品之间的数据迁移，如在DB2数据库与Lotus产品之间的数据迁移。虽然他们同样都是二进制文件，但是他们用途是不同的。为此数据库管理员需要根据不同用途来选择合适的文件格式。

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

Export导出过程
db2 ? export
EXPORT TO filename OF {IXF | DEL | WSF}
[LOBS TO lob-path [ {,lob-path} ... ] ]
[LOBFILE lob-file [ {,lob-file} ... ] [MODIFIED BY {filetype-mod ...}]
[METHOD N ( column-name [ {,column-name} ... ] )] [MESSAGES message-file]
{select-statement | HIERARCHY {STARTING sub-table-name |
(sub-table-name [{, sub-table-name} ...])} [WHERE ...] }
filetype-mod：
NODOUBLEDEL、LOBSINFILE、CHARDELx、COLDELx、DLDELx、DECPLUSBLANK、
DECPTx、DATESISO、1、2、3、4、CODEPAGE=x、STRIPLZEROS 和 NOCHARDEL

o 以空白作为正十进制值的前缀（DECPLUSBLANK） 
o 使用 ISO 日期格式（DATESISO） 
o 不识别双字符定界符（NODOUBLEDEL）

文件类型修饰符
CHARDELx：指定x为新的单字符串定界符。默认值是双引号（“”）
COLDELx ：指定x为新的单字符列定界符。默认值是双引号（，）
DLDELx：十进制小数位字符（" % & ( ) * . / : ; < = > ? | , ' _
CODEPAGE=x
指定x这个ASCII字符串为输出数据的新代码页，在导出操作期间，将字符数据从应用程序代码页转换成这一代码页：
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 to myfile.del of del modified by chardel! coldel@ codepage=1208 timestampformat=”yyyy.mm.dd hh:mm tt” select_statment
捕捉错误或警告信息
Messages x:\文件名

1. 非定界或定长ASCII （ASC）
2. 定界ASC (DEL)
最主要的分隔符有以下几种：
字符分隔符
列分隔符
行分隔符：标识一行或一个记录的结束。DB2用新行符XOA（UNIX）界定分隔符：db2用换行符XODOA作为界定分隔符。
3. PC/IXF文件
4. 工作表格式WSF

例一：Export to myname.del of del select * from myname
格式：export to x.ixf of ixf … select * from X…
例二
db2 export to org.del of del modified by chardel! coldel@ codepage=1208 select * from org
字符串由感叹号！括起来，列由@号定界，字符串被转换成代码页1208
例二
db2 export to org.del of del modified by chardel! coldel@ codepage=1208 messages msg.out select * from org
字符串由感叹号！括起来，列由@号定界，字符串被转换成代码页1208,添加message参数后，产生一个msg.out文件，捕获导出期间错误，警告和信息性消息。
例三
导出大对象
Export to file_name of file_type lobs to lobfile_directory,lobfile_directory_2,….
Lobfile lobfilename
Modified by lobsinfile select_statment
有了lobsinfile修饰符，export实用程序就查找lobs to 子句中指定的目录，然后将lob数据放在那里。如果没有找到lobs to 子句，就将lob数据发送到当前工作目录。


例四
CONNECT TO SAMPLE;
EXPORT TO "E:\db2log\org.ixf" OF IXF METHOD N (DEPTNUMB, DEPTNAME) MESSAGES "E:\db2log\msg.out" SELECT * FROM ADMINISTRATOR.ORG;
CONNECT RESET;
例五
CONNECT TO SAMPLE;
EXPORT TO "E:\db2log\org1.ixf" OF IXF MESSAGES "E:\db2log\msg1.out" SELECT * FROM ADMINISTRATOR.ORG;
CONNECT RESET;

Export Sessions - CLP Examples 
The following example shows how to export information from the STAFF table in the SAMPLE database (to which the user must be connected) to myfile.ixf, with the output in IXF format. If the database connection is not through DB2 Connect, the index definitions (if any) will be stored in the output file; otherwise, only the data will be stored: 
db2 export to myfile.ixf of ixf messages msgs.txt select * from staff 
The following example shows how to export the information about employees in Department 20 from the STAFF table in the SAMPLE database (to which the user must be connected) to awards.ixf, with the output in IXF format: 
db2 export to awards.ixf of ixf messages msgs.txt select * from staff
where dept = 20 
The following example shows how to export LOBs to a DEL file: 
db2 export to myfile.del of del lobs to mylobs/
lobfile lobs1, lobs2 modified by lobsinfile
select * from emp_photo 
The following example shows how to export LOBs to a DEL file, specifying a second directory for files that might not fit into the first directory: 
db2 export to myfile.del of del
lobs to /db2exp1/, /db2exp2/ modified by lobsinfile
select * from emp_photo 
The following example shows how to export data to a DEL file, using a single quotation mark as the string delimiter, a semicolon as the column delimiter, and a comma as the decimal point. The same convention should be used when importing data back into the database: 
db2 export to myfile.del of del
modified by chardel'' coldel; decpt,
select * from staff 


db2 import导入过程
db2 ? import

IMPORT FROM filename OF {IXF | ASC | DEL | WSF}
[LOBS FROM lob-path [ {,lob-path} ... ] ] [MODIFIED BY filetype-mod ...]
[METHOD {L ( col-start col-end [ {,col-start col-end} ... ] )
[NULL INDICATORS (col-position [ {,col-position} ... ] )] |
N ( col-name [ {,col-name} ... ] ) |
P ( col-position [ {,col-position} ... ] )}]
[ALLOW {NO | WRITE} ACCESS]
[COMMITCOUNT {n | AUTOMATIC}] [{RESTARTCOUNT | SKIPCOUNT} n]
[ROWCOUNT n] [WARNINGCOUNT n] [NOTIMEOUT] [MESSAGES message-file]
{{INSERT | INSERT_UPDATE | REPLACE | REPLACE_CREATE}
INTO {table-name [( insert-column , ... )] | hierarchy-description}
| CREATE INTO {table-name [( insert-column , ... )] |
hierarchy-description {AS ROOT TABLE | UNDER sub-table-name}
[IN tablespace-name [INDEX IN tablespace-name] [LONG IN tablespace-name]]}
[datalink-specification]
filetype-mod：
COMPOUND=x、INDEXSCHEMA=schema、FORCEIN、INDEXIXF、IMPLIEDDECIMAL、
NOCHECKLENGTHS、NOEOFCHAR、NULLINDCHAR、RECLEN=x、STRIPTBLANKS、
STRIPTNULLS、NO_TYPE_ID、NODOUBLEDEL、LOBSINFILE、USEDEFAULTS、
CHARDELx、COLDELx、DLDELx、DECPLUSBLANK、DECPTx、DATESISO、
DELPRIORITYCHAR、IDENTITYMISSING、IDENTITYIGNORE、
GENERATEDMISSING、GENERATEDIGNORE、DATEFORMAT=x、TIMEFORMAT=x、
TIMESTAMPFORMAT=x、KEEPBLANKS、CODEPAGE=x、NOROWWARNINGS、
NOCHARDEL 和 USEGRAPHICCODEPAGE
hierarchy-description：
{ALL TABLES | (sub-table-name [(insert-column, ...)], ...)} [IN]
HIERARCHY {STARTING sub-table-name | (sub-table-name, ...)}
datalink-specification：
([DL_LINKTYPE URL][{DL_URL_REPLACE_PREFIX prefix |
DL_URL_DEFAULT_PREFIX prefix}] [DL_URL_SUFFIX suffix], ...)

1. 增加数据
Import from aaa.del of del messages msg.out insert into product
Import from aaa.del of del messages msgout insert into product(price,prod_no,description)
2. 更新已经存在的数据及加入新数据
Import from aaa.del of del messages msg.out replace into product


3. 替换数据
Import from aaa.del of del messages msg.out replace_create into product
4. 创建表
Import from aaa.ixf of ixf messages msg.out replace_create into product

5. commitcount与restartcount选项
import from myfile.ixf of ixf commitcount 500 messages msg.out insert into newtable
import from myfile.ixf of ixf commitcount 50 restartcount 2000 messages msg.out insert into newtable
6. 导入大对象