谁能搞一个多分区数据库导入IXF文件的例子？

myfriend2010

谁能搞一个多分区数据库导入IXF文件的例子？

我百思不得其解！

今天早上和狼有了共识：多分区数据库不能直接导入IXF文件到数据库中，要想导入IXF的文件，一般只能采用变通的方式。

首先设置环境变量 DB2_PARTITIONEDLOAD_DEFAULT=NO 并使用 LOAD_ONLY_VERIFY_PART 方式，以将该表装入到位于单个数据库分区上的表中。
然后，可以使用 CURSOR 文件类型来执行装入操作，以将该数据移到分布在多个数据库分区中的表中。
您也可以使用装入操作以 LOAD_ONLY_VERIFY_PART 方式将 PC/IXF 文件装入到分布在多个数据库分区中的表中

但是我下来测试了一把，总是不成功！

如下：
1：设置DB2_PARTITIONEDLOAD_DEFAULT=NO
C:Program FilesIBMSQLLIBBIN>db2set DB2_PARTITIONEDLOAD_DEFAULT=NO

C:
2:使用 LOAD_ONLY_VERIFY_PART 方式
load from e:s.ixf of ixf insert into zt2 PARTITIONED DB CONFIG MODE LOAD_ONLY_VERIFY_PART OUTPUT_DBPARTNUMS (0)
SQL27959N  分区数据库配置选项 "PARTITIONED DB CONFIG" 无效。原因码 = "1"。


Statement processed with ERROR.


查看SQL27959N这个错误的意思:
在非分区数据库环境中或当 DB2_PARTITIONEDLOAD_DEFAULT
注册表变量设置为 OFF 时，不能指定分区数据库配置选项。

似乎不能把DB2_PARTITIONEDLOAD_DEFAULT=NO和数据库分区选项一起使用,于是乎我这么做,去掉了分区选项!

load from e:s.ixf of ixf insert into zt2 copy no;
SQL3501W  由于对数据库禁用了正向恢复，因此，表所驻留的表空间将不会处于备份暂挂状
态。

SQL3109N  实用程序开始装入文件 "e:s.ixf" 中的数据。

SQL3500W  在时间 "2008-03-27 15:49:22.525402" 时，实用程序正在开始 "LOAD"
阶段。

SQL6100N  数据文件中的分区映射和数据库的分区映射不相同。

SQL6100N  数据文件中的分区映射和数据库的分区映射不相同。


还是不对,觉得有问题,不会用!

再走第二种路子:
一:改回DB2_PARTITIONEDLOAD_DEFAULT设置:
C:Program FilesIBMSQLLIBBIN>db2set DB2_PARTITIONEDLOAD_DEFAULT=

C:Program FilesIBMSQLLIBBIN>

二:使用 LOAD_ONLY_VERIFY_PART 倒入到分区1中方式

load from e:s.ixf of ixf insert into zt2 PARTITIONED DB CONFIG MODE LOAD_ONLY_VERIFY_PART OUTPUT_DBPARTNUMS (1);
SQL3025N  指定文件名或路径的参数无效。


  代理进程类型     节点     SQL 代码     结果
______________________________________________________________________________
  LOAD           001      -00003025    初始化错误。表未更改。
______________________________________________________________________________
  RESULTS：      1 装入的 0 成功完成。
______________________________________________________________________________

LOAD 代理进程的总结：
读取行数         = 0
跳过行数         = 0
装入行数         = 0
拒绝行数         = 0
删除行数         = 0
落实行数         = 0



Statement processed with ERROR.

谁给看看,哪儿不对,谁有这方面经验!

myfriend2010

不知道怎么编辑的,e:全变成了e:   希望不要引起误解!

myfriend2010

1. 不知道怎么编辑的,e:全变成了e:   希望不要引起误解!

复制代码

myfriend2010

diablo2, wangzhonnew, askgyliu, macrozeng, unixnewbie都来看看!!

wangzhonnew

(taoewang@bugdbug) /home/taoewang/sqllib $ db2start
03/27/2008 14:15:25     1   0   SQL1063N  DB2START processing was successful.
03/27/2008 14:15:25     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/sqllib $ db2 create db sample
db2 "DB20000I  The CREATE DATABASE command completed successfully.
(taoewang@bugdbug) /home/taoewang/sqllib $ db2 "create database partition group part1 on dbpartitionnum (0)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
(taoewang@bugdbug) /home/taoewang/sqllib $ db2 connect to sample

   Database Connection Information

Database server        = DB2/6000 8.2.6
SQL authorization ID   = TAOEWANG
Local database alias   = SAMPLE

(taoewang@bugdbug) /home/taoewang/sqllib $ db2 "create database partition group part1 on dbpartitionnum (0)"
DB20000I  The SQL command completed successfully.
(taoewang@bugdbug) /home/taoewang/sqllib $ db2 "create tablespace tbspcs1 in part1 managed by system using ('/TMP/taoewang/temp/sms1')"
DB20000I  The SQL command completed successfully.
(taoewang@bugdbug) /home/taoewang/sqllib $ db2 "create table t1 (c1 int, c2 char(10)) in tbspcs1"
DB20000I  The SQL command completed successfully.
(taoewang@bugdbug) /home/taoewang/sqllib $ cd /TMP/taoewang/temp
(taoewang@bugdbug) /TMP/taoewang/temp $ ls
sms1      test.ixf
(taoewang@bugdbug) /TMP/taoewang/temp $ db2 "load from test.ixf of ixf insert into t1"
SQL3004N  The filetype parameter is not valid.

(taoewang@bugdbug) /TMP/taoewang/temp $ db2set DB2_PARTITIONEDLOAD_DEFAULT=NO
(taoewang@bugdbug) /TMP/taoewang/temp $ db2stop force
db2start
03/27/2008 15:13:01     0   0   SQL1064N  DB2STOP processing was successful.
03/27/2008 15:13:02     1   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
(taoewang@bugdbug) /TMP/taoewang/temp $ db2start
03/27/2008 15:13:11     1   0   SQL1063N  DB2START processing was successful.
03/27/2008 15:13:12     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) /TMP/taoewang/temp $ export DB2NODE=1
(taoewang@bugdbug) /TMP/taoewang/temp $ db2 terminate
dDB20000I  The TERMINATE command completed successfully.
(taoewang@bugdbug) /TMP/taoewang/temp $ db2 connect to sample

   Database Connection Information

Database server        = DB2/6000 8.2.6
SQL authorization ID   = TAOEWANG
Local database alias   = SAMPLE

(taoewang@bugdbug) /TMP/taoewang/temp $ db2 list tablespaces

           Tablespaces for Current Database

Tablespace ID                        = 1
Name                                 = TEMPSPACE1
Type                                 = System managed space
Contents                             = System Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 2
Name                                 = USERSPACE1
Type                                 = System managed space
Contents                             = Any data
State                                = 0x0000
   Detailed explanation:
     Normal

DB21011I  In a partitioned database server environment, only the table spaces
on the current node are listed.

(taoewang@bugdbug) /TMP/taoewang/temp $ db2 terminate
DB20000I  The TERMINATE command completed successfully.
(taoewang@bugdbug) /TMP/taoewang/temp $ export DB2NODE=0
(taoewang@bugdbug) /TMP/taoewang/temp $ db2 connect to sample
db2 list
   Database Connection Information

Database server        = DB2/6000 8.2.6
SQL authorization ID   = TAOEWANG
Local database alias   = SAMPLE

(taoewang@bugdbug) /TMP/taoewang/temp $ db2 list tablespace
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following
"TABLESPACE".  Expected tokens may include:  "CONTAINERS".  SQLSTATE=42601
(taoewang@bugdbug) /TMP/taoewang/temp $ db2 list tablespaces

           Tablespaces for Current Database

Tablespace ID                        = 0
Name                                 = SYSCATSPACE
Type                                 = System managed space
Contents                             = Any data
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 1
Name                                 = TEMPSPACE1
Type                                 = System managed space
Contents                             = System Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 2
Name                                 = USERSPACE1
Type                                 = System managed space
Contents                             = Any data
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 3
Name                                 = TBSPCS1
Type                                 = System managed space
Contents                             = Any data
State                                = 0x0000
   Detailed explanation:
     Normal

DB21011I  In a partitioned database server environment, only the table spaces
on the current node are listed.

(taoewang@bugdbug) /TMP/taoewang/temp $ db2 "load from test.ixf of ixf modified by noheader insert into t1"
SQL3501W  The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL3109N  The utility is beginning to load data from file
"/net2/d_TMP/TMP/taoewang/temp/test.ixf".

SQL3500W  The utility is beginning the "LOAD" phase at time "03/27/2008
15:56:22.804502".

SQL3150N  The H record in the PC/IXF file has product "DB2    02.00", date
"20080327", and time "140956".

SQL3153N  The T record in the PC/IXF file has name "test.ixf", qualifier "",
and source "            ".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3110N  The utility has completed processing.  "18" rows were read from the
input file.

SQL3519W  Begin Load Consistency Point. Input record count = "18".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "03/27/2008
15:56:22.847239".


Number of rows read         = 18
Number of rows skipped      = 0
Number of rows loaded       = 18
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 18

wangzhonnew

(taoewang@bugdbug) /TMP/taoewang/temp $ db2level
DB21085I  Instance "taoewang" uses "32" bits and DB2 code release "SQL08026"
with level identifier "03070106".
Informational tokens are "DB2 v8.1.0.120", "s060801", "U808887", and FixPak
"13".
Product is installed at "/usr/opt/db2_08_01".

myfriend2010

没有用到LOAD_ONLY_VERIFY_PART阿？

myfriend2010

还有就是是不是只有这么一种方法？

diablo2

[tj02@red01 /home/tj02]$  db2 "load from t1.ixf of ixf  MESSAGES t1.txt insert into t1 PARTITIONED DB CONFIG MODE LOAD_ONLY_VERIFY_PART PART_FILE_LOCATION /home/tj02 "


  Agent Type     Node     SQL Code     Result
______________________________________________________________________________
  LOAD           000      +00000000    Success.
______________________________________________________________________________
  LOAD           001      +00000000    Success.
______________________________________________________________________________
  LOAD           002      +00000000    Success.
______________________________________________________________________________
  LOAD           003      +00000000    Success.
______________________________________________________________________________
  RESULTS:       4 of 4 LOADs completed successfully.
______________________________________________________________________________

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

myfriend2010

大菠萝这个操作有没有详细步骤?