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/