【未解决】DB2数据库数据的导出导入

DB2数据库数据的导出导入

在百度上看到这个文章讲解DB2的导出导入：http://wenku.baidu.com/view/1f763dc78bd63186bcebbcb0.html

因为我本地的数据库是auto increment的，所以使用这句：

在上一部分，我有提到，若表中存在自增长的IDENTITY列，需要使用相应的参数才能导入，比如：
db2 => import from /home/movedata/mytbl2.ixf of ixf modified by identityignore insert into mytbl2

但是一直遇到以下出错：

[db2inst1@oc0644314035 ~]$ db2 "import from /home/elsa/Desktop/dataBackup/holiday.ixf of ixf modified by identityignore insert into 
SR.HOLIDAY"
SQL3030C  An I/O error (reason = "sqlofopn -2079391743") occurred while 
opening the input file.

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

在网上看了一些资料，提到的普遍观点是：

一、权限问题。来源：http://bbs.chinaunix.net/archiver/tid-917817.html

二、创建一个新的folder，问题就没有了。

三、重装data studio的。

IBM官网的Knowledge Center只是把错误提示再给贴一遍，也是醉了：http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql03030c.html

第三种情况除非是万不得已的情况下，不然我是不会采取的。

所以我采取的解决方案是：在/home/db2inst1里创建一个folder叫做databaseBackup，然后把之前那个放有ixf文件的folder移到这个db2inst1的权限下的folder里，再在命令行里切换到su - db2inst1的权限，执行以下命令进行导入：

db2 "import from holiday.ixf of ixf modified by identityignore insert into SR.HOLIDAY"

报错的时候，我看到很多都是跟其他表格相关的，按理说应该是没问题的。

今天再次实验的时候，出现以下这种情况，说我的ixf文件不正确：

SQL3054N  The input file is not a valid PC/IXF file.  The file is too short to 
contain a valid H record.

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

换了一个ixf文件，导入成功：

[db2inst1@oc0515384300 localbackup]$ db2 "import from tab8.ixf of ixf modified by identityignore insert into SR.POOL"
SQL3150N  The H record in the PC/IXF file has product "DB2    02.00", date 
"20150831", and time "154119".

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

SQL3109N  The utility is beginning to load data from file "tab8.ixf".

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

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "3".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "3" rows were processed from the input file.  "3" rows were 
successfully inserted into the table.  "0" rows were rejected.


Number of rows read         = 3
Number of rows skipped      = 0
Number of rows inserted     = 3
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 3

但是查看了一下导入后的数据，依然是按照本地顺序去插入的。所以还是不能符合我的需求……

所以我想在不drop table的情况下，清空我本地的这些数据。然后导入我从服务器上备份下来的数据。

清空表格

在查看清空表格的时候：http://www.educity.cn/shujuku/1448361.html

讲到以下几种方法：

ALTER TABLE SR.HOLIDAY ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE (这个我试过了，不能阻止auto increment继续计数)

IMPORT FROM /dev/null OF DEL REPLACE INTO SR.HOLIDAY (导入一个空表，这个我试过了，也不能阻止auto increment继续计数)

LOAD FROM /dev/null OF DEL REPLACE INTO SR.HOLIDAY NONRECOVERABLE (这句非常危险！看到最后的NONRECOVERABLE了没，不可恢复！reorg命令都救不了我的table。这个命令会导致这个table被挂起，只能通过回滚的方式到之前的备份，如果像我一样没有备份，就等着呵呵呵然后drop table吧) 来源：http://www.itpub.net/thread-639894-1-1.html