load IXF类型文件到多分区数据库

db2 load IXF 文件到多分区数据库中一直是我头痛的一件事！最近问过好几个人，得到如下的一个例子，现在帖上！

LOAD_ONLY_VERIFY_PART, can be used as a work around to load IXF data in the partitioned database environment.The IXF file to be loaded must be available to each partition, either by NFS mounting, or by physically copying the file to every partition to be loaded. By specifying the loading mode LOAD_ONLY_VERIFY_PART, each partition will pick out only those records that belong to that partition.ITPUB个人空间"Gm,i+G {c5g1B_y
Ignore warnings about partitioning errors.Use caution when allocating a dump file.If specified, each row rejected for partitioning error, will be written there.ITPUB个人空间$ETI6O+Ub ?1d
ITPUB个人空间/E,A@&o4N;YbUZ
sample:
&I-K6\n"A$Ud _'W^0(taoewang@bugdbug) /home/taoewang/temp $ db2stop force
tog4y,uK(f u0db2startITPUB个人空间4Cq{;IQ9JX:B
05/30/2008 10:48:48     1   0   SQL1064N  DB2STOP processing was successful.
y[fi'T0C3A(xk005/30/2008 10:48:48     0   0   SQL1064N  DB2STOP processing was successful.
2},zD`8b+z:S0SQL1064N  DB2STOP processing was successful.ITPUB个人空间}-L)\3J"d/GP%\'hPU
(taoewang@bugdbug) /home/taoewang/temp $ db2startITPUB个人空间b1ZA sGY3ehI
05/30/2008 10:48:53     1   0   SQL1063N  DB2START processing was successful.
tqO)ZY8{"B005/30/2008 10:48:54     0   0   SQL5043N  Support for one or more communications protocols failed to start successfully. However, core database manager functionality started successfully.ITPUB个人空间$?!w5I:c QeA1F,E$J7}6F
SQL1063N  DB2START processing was successful.ITPUB个人空间eQy5MM%r m(G,^2af8K
(taoewang@bugdbug) /home/taoewang/temp $ db2 connect to sample
&g(h7] I,_x;[3_6J0
3U'A7P.mPsg0   Database Connection InformationITPUB个人空间/U4d:IJs H.C"G-m

*To T Z)ySJ"l;I@ _0Database server        = DB2/AIX64 8.2.9ITPUB个人空间Z]'o9f6pF-U~1A9P4z4j
SQL authorization ID   = TAOEWANGITPUB个人空间Od3Q;h)|*n
Local database alias   = SAMPLE
9XD6QC6Y h&AS)P$M0ITPUB个人空间/E.b
?%PH^
(taoewang@bugdbug) /home/taoewang/temp $db2 "export to staff.ixf of ixf select * from staff"
2H;h4P[U }0SQL3104N  The Export utility is beginning to export data to file "staff.ixf".ITPUB个人空间^0v`Y
P W @r

}A8[\t1xU0SQL3105N  The Export utility has finished exporting "35" rows.ITPUB个人空间M4jC;b;TPU6a

8L8M&l!hK-`5B*?Z0
D,BPC;FE'?0Number of rows exported: 35
)dc{&g7|0^nL0
.L|+T&zMds9P,F8`0(taoewang@bugdbug) /home/taoewang/temp $ ls *.ixfITPUB个人空间*|N0hJ8v
staff.ixf
3rHM5} T"Q~0(taoewang@bugdbug) /home/taoewang/temp $ db2 "create table t1 like staff"ITPUB个人空间ZP{Ql,\-K u
DB20000I  The SQL command completed successfully.
6^(RSZ xMwmG0(taoewang@bugdbug) /home/taoewang/temp $ db2 "load from staff.ixf of ixf replace into t1"ITPUB个人空间9t lf7g` l
SQL3004N  The filetype parameter is not valid.ITPUB个人空间3UG&hKj2|r1SFu

P9_/Y8Xn8l[Uq0(taoewang@bugdbug) /home/taoewang/temp $ cp staff.ixf staff.ixf.000ITPUB个人空间
]$^'t2u(?,Du;P
(taoewang@bugdbug) /home/taoewang/temp $ cp staff.ixf staff.ixf.001ITPUB个人空间\2o[!K bZ{D
(taoewang@bugdbug) /home/taoewang/temp $db2 "load from staff.ixf of ixf replace into t1 partitioned db configmode load_only_verify_part part_file_location /home/taoewang/temp"
7sZ?d%M0SQL3107W  There is at least one warning message in the message file.
W"W'l9b j7H+i0
7gB*W{u,U2dP
X M tr0ITPUB个人空间.^ed kP'Pr6h
  Agent Type     Node     SQL Code     Result
.N*Ig!F+Mpv0______________________________________________________________________________ITPUB个人空间j E [:Dy
  LOAD           000      +00003107    Success.
M)o5^jsvR0______________________________________________________________________________
}(|d-Z$S_ M.L&Q0  LOAD           001      +00003107    Success.ITPUB个人空间Uu_BT AQ$Z2]^2r
______________________________________________________________________________ITPUB个人空间Y l1ME_&x%~{]x
  RESULTS:       2 of 2 LOADs completed successfully.
F_"}o3Z8pE0______________________________________________________________________________
YHmN#U
T
p^m0
OR'ap2B7n0Summary of LOAD Agents:
'NW3K m_8k6Qa6x2gl F0Number of rows read         = 70ITPUB个人空间 in3G-o*@0o0o
Number of rows skipped      = 0
n/i(l2O*dr^r%MX0Number of rows loaded       = 35
6absJ5Q0Number of rows rejected     = 35
*[j^A2v#w5Mj0Number of rows deleted      = 0
L Qj}D8f0Number of rows committed    = 70ITPUB个人空间fy*f8zomQ
ITPUB个人空间&NZ)fV |Ss.pg*lk
(taoewang@bugdbug) /home/taoewang/temp $ db2 "select count(*) from t1"ITPUB个人空间YcgM-x"ll6b)N#@{ w
ITPUB个人空间!sx q&?:^P8e w
1
;z(qo,\4[zR w0-----------ITPUB个人空间Bd%GDE?|O
         35ITPUB个人空间 xgIF8z%ao
ITPUB个人空间#G,NaP(?;\/t2i'{
  1 record(s) selected.ITPUB个人空间no`dOA?Qa^r

*~:@:k9xrI&}0(taoewang@bugdbug) /home/taoewang/temp $ITPUB个人空间D G:])G `A+U$f

"P'Tsa;M
~7_*g0

还有，如果数据比较大，你不想copy，直接给个symbolic link:ITPUB个人空间W4sOp
k
(taoewang@bugdbug) /home/taoewang/temp $ rm staff.ixf.001ITPUB个人空间6U|Nw3[R!OO
(taoewang@bugdbug) /home/taoewang/temp $ rm staff.ixf.000ITPUB个人空间Y/Uj8^]O2P
(taoewang@bugdbug) /home/taoewang/temp $ ln -s staff.ixf staff.ixf.001ITPUB个人空间 `;X0K+b&tM
(taoewang@bugdbug) /home/taoewang/temp $ ln -s staff.ixf staff.ixf.000ITPUB个人空间$`.z9n3O9P6s*yF+[;v;I
(taoewang@bugdbug) /home/taoewang/temp $ ls -la
s1bR6_HK1^0total 32
8r/U$a{m0drwxr-sr-x   5 taoewang build           256 May 30 12:08 .
H/PO O,zSC4Z0drwxr-sr-x   9 taoewang build          4096 May 28 23:03 ..
3Z!Md%EZ:OLz0drwxr-sr-x   2 taoewang build           256 May 09 13:27 sms1ITPUB个人空间-P U;yf5hA
drwxr-sr-x   2 taoewang build           256 May 09 13:27 sms2ITPUB个人空间h2S9pR4C3P ];\Y
drwxr-sr-x   2 taoewang build           256 May 09 13:27 sms3
@GU0se0-rw-r-----   1 taoewang build          9763 May 30 10:49 staff.ixfITPUB个人空间NNQj:k U+V9?;a
lrwxrwxrwx   1 taoewang build             9 May 30 12:08 staff.ixf.000 -> staff.ixf
1g }'Id,?.vd0lrwxrwxrwx   1 taoewang build             9 May 30 12:08 staff.ixf.001 -> staff.ixfITPUB个人空间 Z^V"["\]2``
(taoewang@bugdbug) /home/taoewang/temp $ db2 "load from staff.ixf of ixf replace into t1 partitioned db config mode load_only_verify_part part_file_location /home/taoewang/temp"ITPUB个人空间)y/H~wW2j
SQL3107W  There is at least one warning message in the message file.ITPUB个人空间4BJ9lM)O;{9h}^
ITPUB个人空间H\_7y q
ITPUB个人空间.n/@ h QO~^/p5H5s
  Agent Type     Node     SQL Code     Result
{8e!Uy1ft K#@0______________________________________________________________________________
BEu t
pl4r |0  LOAD           000      +00003107    Success.
3diS(c:G5L-~8{e0______________________________________________________________________________
o5a D*c |b#?.]'pg0  LOAD           001      +00003107    Success.ITPUB个人空间gD!P8DC'R]P
______________________________________________________________________________
9C3v]Ft!q+n a
}i0  RESULTS:       2 of 2 LOADs completed successfully.
,s%GR[7aoC`%H"^5N0______________________________________________________________________________ITPUB个人空间'vKI)jD.D

)Q_4[]w0Summary of LOAD Agents:
cjR:o Lk%NWW0Number of rows read         = 70ITPUB个人空间0l*vc;|#N6b!vg ~
Number of rows skipped      = 0
9?sAa9jM8}v#^'@]![I0Number of rows loaded       = 35
&QigR]a&{1S0Number of rows rejected     = 35
%x|N%LK&g5}0Number of rows deleted      = 0ITPUB个人空间 L G
O~"Nqn.S
Number of rows committed    = 70
#NKp1ybW_}0XL0

`|9Z&s9RuM$bT0(taoewang@bugdbug) /home/taoewang/temp $ITPUB个人空间;a+vS7d6b)~~