DB2导入与导出

 

 db2导出与导入说明

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.        导入大对象