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.
Ignore warnings about partitioning errors. Use caution when allocating a dump file. If specified, each row rejected for partitioning error, will be written there.

sample:
(taoewang@bugdbug) /home/taoewang/temp $ db2stop force
db2start
05/30/2008 10:48:48     1   0   SQL1064N  DB2STOP processing was successful.
05/30/2008 10:48:48     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
(taoewang@bugdbug) /home/taoewang/temp $ db2start
05/30/2008 10:48:53     1   0   SQL1063N  DB2START processing was successful.
05/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.
SQL1063N  DB2START processing was successful.
(taoewang@bugdbug) /home/taoewang/temp $ db2 connect to sample

   Database Connection Information

Database server        = DB2/AIX64 8.2.9
SQL authorization ID   = TAOEWANG
Local database alias   = SAMPLE

(taoewang@bugdbug) /home/taoewang/temp $ db2 "export to staff.ixf of ixf select * from staff"
SQL3104N  The Export utility is beginning to export data to file "staff.ixf".

SQL3105N  The Export utility has finished exporting "35" rows.


Number of rows exported: 35

(taoewang@bugdbug) /home/taoewang/temp $ ls *.ixf
staff.ixf
(taoewang@bugdbug) /home/taoewang/temp $ db2 "create table t1 like staff"
DB20000I  The SQL command completed successfully.
(taoewang@bugdbug) /home/taoewang/temp $ db2 "load from staff.ixf of ixf replace into t1"
SQL3004N  The filetype parameter is not valid.

(taoewang@bugdbug) /home/taoewang/temp $ cp staff.ixf staff.ixf.000
(taoewang@bugdbug) /home/taoewang/temp $ cp staff.ixf staff.ixf.001
(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"
SQL3107W  There is at least one warning message in the message file.


  Agent Type     Node     SQL Code     Result
______________________________________________________________________________
  LOAD           000      +00003107    Success.
______________________________________________________________________________
  LOAD           001      +00003107    Success.
______________________________________________________________________________
  RESULTS:       2 of 2 LOADs completed successfully.
______________________________________________________________________________

Summary of LOAD Agents:
Number of rows read         = 70
Number of rows skipped      = 0
Number of rows loaded       = 35
Number of rows rejected     = 35
Number of rows deleted      = 0
Number of rows committed    = 70

(taoewang@bugdbug) /home/taoewang/temp $ db2 "select count(*) from t1"

1
-----------
         35

  1 record(s) selected.

(taoewang@bugdbug) /home/taoewang/temp $

还有，如果数据比较大，你不想copy，直接给个symbolic link:
(taoewang@bugdbug) /home/taoewang/temp $ rm staff.ixf.001
(taoewang@bugdbug) /home/taoewang/temp $ rm staff.ixf.000
(taoewang@bugdbug) /home/taoewang/temp $ ln -s staff.ixf staff.ixf.001
(taoewang@bugdbug) /home/taoewang/temp $ ln -s staff.ixf staff.ixf.000
(taoewang@bugdbug) /home/taoewang/temp $ ls -la
total 32
drwxr-sr-x   5 taoewang build           256 May 30 12:08 .
drwxr-sr-x   9 taoewang build          4096 May 28 23:03 ..
drwxr-sr-x   2 taoewang build           256 May 09 13:27 sms1
drwxr-sr-x   2 taoewang build           256 May 09 13:27 sms2
drwxr-sr-x   2 taoewang build           256 May 09 13:27 sms3
-rw-r-----   1 taoewang build          9763 May 30 10:49 staff.ixf
lrwxrwxrwx   1 taoewang build             9 May 30 12:08 staff.ixf.000 -> staff.ixf
lrwxrwxrwx   1 taoewang build             9 May 30 12:08 staff.ixf.001 -> staff.ixf
(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"
SQL3107W  There is at least one warning message in the message file.


  Agent Type     Node     SQL Code     Result
______________________________________________________________________________
  LOAD           000      +00003107    Success.
______________________________________________________________________________
  LOAD           001      +00003107    Success.
______________________________________________________________________________
  RESULTS:       2 of 2 LOADs completed successfully.
______________________________________________________________________________

Summary of LOAD Agents:
Number of rows read         = 70
Number of rows skipped      = 0
Number of rows loaded       = 35
Number of rows rejected     = 35
Number of rows deleted      = 0
Number of rows committed    = 70

(taoewang@bugdbug) /home/taoewang/temp $

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/694276/viewspace-324591/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/694276/viewspace-324591/