Prayer

1、導(dǎo)出用戶必須有SYSADM或DBADM授權(quán)，或者在表格上擁有CONTROL或SELECT權(quán)限；
2、不支持帶結(jié)構(gòu)列的表格數(shù)據(jù)導(dǎo)出；
3、可以導(dǎo)出帶identity列的表格為ixf格式，導(dǎo)入時(shí)用REPLACE_CREATE、CREATE重建identity列，如果該列定義是GENERATED ALWAYS,導(dǎo)入時(shí)則必須指定identityignore選項(xiàng)。
4、指定導(dǎo)出格式為ixf，ixf文件中含有表格和索引定義，用import可以重建表格和索引，但是下面情況ixf文件會(huì)丟失相關(guān)信息：
* index column names contain hexadecimal values of 0×2B or 0×2D
* table contains XML columns
* table contains LOB(超過1G的定義) columns
* table is multidimensional clustered
* table contains a table partitioning key
* index name that is longer than 128 bytes due to codepage conversion
* table is a protected table
* contains action strings other than SELECT * FROM
* method N is specified
5、如果導(dǎo)出文件太大，可以使用where條件限制導(dǎo)出的數(shù)據(jù)量，分批導(dǎo)出；
6、導(dǎo)出文件格式：
* DEL (delimited ASCII format)
* WSF (work sheet format)
* IXF (integrated exchange format, PC version)
7、執(zhí)行導(dǎo)出命令前，確認(rèn)完成了commit或rollback；
8、導(dǎo)出格式為DEL時(shí)，如果字符列長(zhǎng)度超過254時(shí)會(huì)有警告提示；
9、導(dǎo)出包是與DATETIME ISO格式綁定的，所以在sql語句中需要使用iso格式：
查詢數(shù)據(jù)庫（返回3行數(shù)據(jù)）
db2 select col2 from tab1 where char(col2)=’05/10/2005’;
3 record(s) selected.
導(dǎo)出時(shí)使用locale格式不能選擇到記錄
db2 export to test.del of del select col2 from test where char(col2)=’05/10/2005’;
Number of rows exported: 0
由locale格式改為iso格式
db2 export to test.del of del select col2 from test where char(col2)=’2005-05-10’;
Number of rows exported: 3
10、導(dǎo)出執(zhí)行的三種方式
* CLP命令：export
* ADMIN_CMD存儲(chǔ)過程的export過程
* db2export的API接口，使用c語言實(shí)現(xiàn)
11、del文件格式指定參數(shù)舉例：db2 “export to myfile.del of del modified by chardel’’ coldel; decpt, select * from staff”
chardelx: 指定字符串的分隔符，缺省是雙引號(hào)，可以指定為單引號(hào)modified by chardel'’
coldelx：指定列分隔符，缺省是逗號(hào)，可以改為分號(hào)modified by coldel;
decplusblank：指定對(duì)正數(shù)是否用+符號(hào)，缺省帶+,可以指定不加正數(shù)符號(hào)
striplzeros：指定移走數(shù)據(jù)前導(dǎo)的0，如：+00001.8–>+1.8
測(cè)試如下
db2 “export to tb1-1.del of del select * from tb1″
+00000002.10,”abc”
db2 “export to tb1-2.del of del modified by chardel'’ coldel; decplusblank striplzeros select * from tb1″
2.10;’abc’
timestampformat=″x″時(shí)間戳格式設(shè)置，如：”YYYY/MM/DD HH:MM:SS.UUUUUU”、”yyyy.mm.dd hh:mm tt”
YYYY - Year (four digits ranging from 0000 - 9999)
M - Month (one or two digits ranging from 1 - 12)
MM - Month (two digits ranging from 01 - 12)
D - Day (one or two digits ranging from 1 - 31)
DD - Day (two digits ranging from 1 - 31)
H - Hour (one or two digits ranging from 0 - 12 for a 12 hour system, and 0 - 24 for a 24 hour system)
HH - Hour (two digits ranging from 0 - 12 for a 12 hour system, and 0 - 24 for a 24 hour system)
M - Minute (one or two digits ranging from 0 - 59)
MM - Minute (two digits ranging from 0 - 59)
S - Second (one or two digits ranging from 0 - 59)
SS - Second (two digits ranging from 0 - 59)
UUUUUU - Microsecond (6 digits ranging from 000000 - 999999)
UUUUU - Microsecond (5 digits ranging from 00000 - 99999)
UUUU - Microsecond (4 digits ranging from 0000 - 9999)
UUU - Microsecond (3 digits ranging from 000 - 999)
UU - Microsecond (2 digits ranging from 00 - 99)
U - Microsecond (1 digit ranging from 0 - 9)
TT - Meridian indicator (AM or PM)
如果指定了TT，HH就會(huì)在0－12之間顯示