Db2 文件导入导出常见命令总结
Db2 的数据迁移，最常用的就是导入导出功能，而导入导出的命令貌似简单，实则内含玄机，千变万化，稍不留神，则错误百出，这儿就工作中常用到的命令，总结了一下，分享给大家！欢迎大家踊跃拍砖！?
当然在这以前，我觉得有必要提及一点关于导入导出基础的知识！
DEL：界定的ASCII文件，行分隔符和列分隔符将数据分开。
ASC：定长的ASCII文件，行按照行分割符分开，列定长。
PC/IXF：只能用来在db2之间导数据，根据类型数字值被打包成十进制或者二进制，字符被保存为ASCII，只保存变量已经使用了的长度，文件中包括表的定义和表的数据。
WSF：工作表方式导入导出，这种格式的文件类型用的比较少。
Db2中对不同的数据导入导出方式，支持不同的文件类型，这里个人觉得很有必要注意下的。
文件类型 Import export load
-------------------------------------------------------
定界 支持 支持 支持
非定界 支持 不支持 支持
Ixf 支持 支持 支持
Wsf工作表 支持 支持 不支持
关于3种导入导出操作进行简单的介绍:
export：导出数据，支持IXF，DEL或WSF(ASC格式的文件不支持)
import：导入数据，可以向表中导入数据，支持上面提到的4种文件类型。
load：导入数据，功能和import基本相同。支持以上说的几种文件类型。
关于Export
这个其实比较简单，没啥好说的，一般命令：export to filename of filetype select x from xx where ;就ok了,这里需要注意的是：
1. 关于不同字符集的导出
MODIFIED BY CODEPAGE=
Exprot to filename.del for del MODIFIED BY CODEPAGE=1386 select … from …where …;
这里，在数据从数据库倒出来的时候就会做一个数据库代码页的转换
2.时间字段格式化的
MODIFIED BY TIMESTAMPFORMAT="yyyy-mm-dd hh:mm:ss tt"
例:Exprot to filename.del for del MODIFIED BY TIMESTAMPFORMAT="yyyy-mm-dd hh:mm:ss tt" select … from …where …;
关于Import
1.Import模式的介绍
注：导入的顺序和字段名称无关，和字段的顺序有关。即不是按列名导入，而是按照顺序依次导入。
CREATE/INSERT/INSERT_UPDATE/REPLACE/REPLACE_CREATE
CREATE :首先创建目标表和它的索引，然后将数据导入到新表中。该选项惟一支持的文件格式是 PC/IXF。还可以指定新表所在表空间的名称
INSERT :将导入的数据插入表中。目标表必须已经存在。
INSERT_UPDATE :将数据插入表中，或者更新表中具有匹配主键的行。目标表必须已经存在，并且定义了一个主键。
REPLACE :删除所有已有的数据，并将导入的数据插入到一个已有的目标表中。
REPLACE_CREATE :如果目标表已经存在，则导入实用程序删除已有的数据，并插入新的数据，就像 REPLACE 选项那样。如果目标表还没有定义，那么首先创建这个表以及它的相关索引，然后再导入数据。正如您可能想像的那样，输入文件必须是 PC/IXF 格式的文件，因为那种格式包含对导出表的结构化描述。如果目标表是被一个外键引用的一个父表，那么就不能使用 REPLACE_CREATE。
2. 批量提交
COMMITCOUNT，保证insert的数据在COMMITCOUNT以后进行一次commit，这对于大数据量的导入文件来说是一个不错的方法，
例：Import from filename of del COMMITCOUNT 50000 insert into tabname;
3. 批量插入
MODIFIED BY COMPOUND把文件中的 COMPOUND 行记录作为一组一起导入，这个操作可以和上边的批量提交一起使用，比较理想。
例：Import from filename of del MODIFIED BY COMPOUND =50 insert into tabname;
4. 导入记录限制
ROWCOUNT：只导入rowcount 条数据，有时候，业务逻辑需要只导入部分数据，那么ROWCOUNT是一个不错的选择，只是在我的测试中ROWCOUNT一直没有起过作用，呵呵，谁熟悉这里，帮我完善下。
例：Import from filename of del ROWCOUNT 10000 insert into tabname;
5. 导入起点
注：行数从0开始计数。
RESTARTCOUNT：从导入文件的第RESTARTCOUNT条记录开始导入
例：Import from filename of del RESTARTCOUNT 55 ROWCOUNT 10000 insert into tabname;--从55条开始，导入10000条数据
6. 有警告数据的条数限制
WARNINGCOUNT:当导入的数据中，有警告或错误（例如类型不匹配，列不对应等造成的）并且条数超过WARNINGCOUNT是就会停止import。
例：Import from filename of del WARNINGCOUNT 10 insert into tabname;
7. 禁止发出行警告
MODIFIED BY NOROWWARNINGS
例：Import from filename of del MODIFIED BY NOROWWARNINGS WARNINGCOUNT 10 insert into tabname;
8. LOB 文件
LOBS FROM ：指出LOB的路径
例：Import from filename of del LOBS FROM ‘/home’ MODIFIED BY NOROWWARNINGS WARNINGCOUNT 10 insert into tabname;
9. 对于自增序列(GENERATED ALWAYS）
建议不要对自增序列的表进行import操作，因为import对于自增序列，只有MODIFIED BY IDENTITYIGNORE和MODIFIED BY IDENTITYMISSING的2中操作，这2中操作都会改变自增序列的原值，这样如果导出表和表之间有基于自增序列的关联关系的话，就失去了数据本身的意义，所以建议尽量少用基于import的自增表的操作，那该怎么做？可以用load老代替import，我们下来在load的操作中会讲到！
关于Load
1. 字符串间隔，列间隔，小数点表示
CHARDEL/COLDEL/DECPT
例：LOAD CLIENT FROM 'F:s1.del' OF DEL MODIFIED BY CHARDEL(COLDEL= DECPT? INSERT INTO "DB2ADMIN"."ZXTABLES"
2.数据库记录中存在换行符,导致数据无法装入的情况
MODIFIED BY DELPRIORITYCHAR
Db2默认load优先级策略为，record delimiter, character delimiter, column delimiter,这样record delimiter得优先级最高，所以原始文件如果有换行的话load就认为是新的record，如果用户在某些情况下行里面包含了换行符（比如论坛里面的一条帖子，不可能把换行符删掉的），就必须用delprioritychar改变默认的优先级别，确保""之间的数据不管有没有换行符都被认为是同一条记录
例：LOAD CLIENT FROM 'F:s1.del' OF DEL MODIFIED BY DELPRIORITYCHAR INSERT INTO DB2ADMIN.ZXTABLES
3.load后表空间暂挂的处理
Copy YES/ NONRECOVERABLE
对于DMS表空间，load默认为copy NO 模式，此模式下，load完成后，表空间会处于暂挂状态，这时候只能查表中的数据，需要对表空间进行一次备份，才可以对表进行update、insert等操作，那么我们可以使用如上2个命令，对于Copy YES，load完成以后会自动进行一次备份操作；NONRECOVERABLE 指出load不可恢复，此命令符不会产生表空间暂挂，也不会自动进行表空间备份，但是他有一个缺点就是，不能恢复，当数据库进行回滚的时候，不能还原，危险等级还是比较高一点，不过个人觉得也NONRECOVERABLE比较实用。
例：LOAD CLIENT FROM 'F:s1.del' OF DEL INSERT INTO DB2ADMIN.ZXTABLES NONRECOVERABLE
LOAD CLIENT FROM 'F:s1.del' OF DEL INSERT INTO DB2ADMIN.ZXTABLES COPY YES
4. load IXF类型文件到多分区数据库
partitioned db configmode load_only_verify_part part_file_location
当数据在2个不同数量节点的数据库之间移动，如果还想使用load来进行IXF的数据装载就比较棘手了，当时查遍IBM的官方文档均无所获，正郁郁不安时，狼出现了，给支了一招，现分享给大家。
首先，把ixf文件复制和分区数量相同的文件，并后缀.000(分区号)，
比如，2个分区，那么我需要把原来的a.ixf,拷贝2个，并命名为a.ixf.000以及a.ixf.001
然后，load from staff.ixf of ixf replace into t1 partitioned db configmode load_only_verify_part part_file_location xxxx（part_file_location为a.ixf.000所在的目录），当然如果在unix下的话，可以通过ln –s 来做一个连接也行。
5. 对于自增序列(GENERATED ALWAYS）
MODIFIED BY IDENTITYOVERRIDE：此参数可以理解为，采用文件load文件中的自增值做为表自增序列的值，这样就能保证对于自增序列有业务意义，或者关联逻辑（主外键关联）的情况下保证数据的一致，个人对此命令屡试不爽
例： LOAD CLIENT FROM 'F:s1.del' OF DEL MODIFIED BY IDENTITYOVERRIDE INSERT INTO DB2ADMIN.ztables;
6. 对于自增序列(GENERATED by default）
GENERATED by default可以直接通过一般的导入方式加载数据，不过有时候会有一点点小问题，自增序列没有进行分配，也就是说，原来表有50条记录，自增序列的下一次分配值为50，当你已经导入了1000条记录进去了，然后发现自增序列的下一次分配值还是为50，这种事不经常发生，但是偶尔会发生一次，比较郁闷的是，当表继续插入数据的时候，下一次分配就会发生冲突，尤其是如果自增建为主键的时候，会违反唯一约束。
解决办法：首先找到这个序列分配的最大值，select max(id) from tablea;
然后用下边这个命令，alter table tablea alter column restart with max(id)+1
diekwlwoy
立足江湖
? 博客发送消息
? 状态： 离线
? 积分:1012
? 帖子:682 2009-11-04 11:13| 只看主题帖
树型| 收藏| 小 中 大 2
回复：Db2 文件导入导出常见命令总结
7.
ROWCOUNT/WARNINGCOUNT
这个同IMPORT
8.terminate，restart
如果load的过程中出了点故障，比如连接被force掉了等，这时连接查询表数据会报错SQL0668N，原因码3，这时可以通过带有terminate的load命令来进行处理！
LOAD CLIENT FROM 'F:s1.del' OF DEL terminate INTO DB2ADMIN.ztables;
9. set integrity 命令
如果表上有约束，那么给表load数据以后，一般连接表的时候都会报错，SQL0668N，原因码1，这说明该表处于“设置完整性暂挂无访问”状态，那么需要如下命令来操作。
Set integrity for schema.tablename immediate checked;
10.提高导入导出速度的数据库参数
CONFIGURATION：应用程序堆大小，该参数指示可由Backup，restore，load及装入恢复应用程序同事使用的最大内存量，看帖子上有介绍，这个值可以设置为10000*cpu数量。
db2_all "DB2 UPDATE DATABASE CONFIGURATION USING UTIL_HEAP_SZ 113486"
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:
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /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.
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /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.
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /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
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /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
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $ ls *.ixf
staff.ixf
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $ db2 "create table t1 like staff"
DB20000I The SQL command completed successfully.
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $ db2 "load from staff.ixf of ixf replace into t1"
SQL3004N The filetype parameter is not valid.
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $ cp staff.ixf staff.ixf.000
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $ cp staff.ixf staff.ixf.001
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /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"
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
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $ db2 "select count(*) from t1"
1
-----------
35
1 record(s) selected.
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $
还有，如果数据比较大，你不想copy，直接给个symbolic link:
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $ rm staff.ixf.001
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $ rm staff.ixf.000
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $ ln -s staff.ixf staff.ixf.001
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $ ln -s staff.ixf staff.ixf.000
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /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
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /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
([url=mailto:taoewang@bugdbug]taoewang@bugdbug[/url]) /home/taoewang/temp $
扫一扫
918
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
