DB2 LOAD相关

load 对原数据按照DB2物理存储方式进行格式化，并将格式化的数据页直接写入到数据库，
记录的日志很少，不检查约束，不触发触发器，适合大量数据导入。
导入前，目标表必须已存在。

注册表变量 DB2_LOAD_COPY_NO_OVERRIDE 

db2set DB2_LOAD_COPY_NO_OVERRIDE='COPY YES TO /backup'

db2set DB2_LOAD_COPY_NO_OVERRIDE=NONRECOVERABLE 

load的动作选项
1) insert
追加数据
2) replace
先删除表数据，再插入数据
3) terminate
终止load状态
4) restart
用于重启被中断的load命令，restart会使用之前load时产生的临时文件，并从最近的一点开始加载。
加载成功，临时文件自动删除。默认下临时文件是在当前路径下产生，
可通过 TEMPFILES PATH 选项指定存放临时文件的目录。

load 的阶段：
1）装载 ：若目标不包含唯一性索引，则只进行装载过程。
     将源数据文件解析成数据物理存储格式，直接装到页中，不通过DB2引擎。
     收集索引键和统计信息，当数据不符合定义时，无效数据转储到dumpfile。
     modified by dumpfile指定转储文件名和路径。check约束和参考完整性约束。
2）构建索引
3）删除重复值
若表上有主键或唯一性索引，则删除重复值。唯一性约束。
4）索引复制
若指定了 allow read access 和 use tablespace 选项，
则会将索引数据从系统临时表空间中复制到索引表空间中。

-- load 各种状态
Normal：正常状态
Load in Progress：
     正在进行 LOAD 。
Load Pending：
     Load 过程发生意外中断，需用 LOAD TERMINATE 、 LOAD RESTART 或者 LOAD REPLACE 来解除该状态 。
Read Access Only：
     LOAD时指定 ALLOW READ ACCESS（只读）。
Set Integrity Pending：
     表上存在约束，Load 时表会处于该状态，必须通过 Set Integrity 解除该状态。
Reorg Pending：
     如果使用 Alter Table 语句，会将表置于该状态，处于这种状态的表无法访问，必须做离线REORG来解除该状态。
Unavailable：
     Load 时指定 NonRecoverable，当前滚到 LOAD 操作结束后的某个点，表就处于该状态。只能重建该表。
Not Load Restartable：
     表处于不完整导入状态，不允许使用 LOAD RESTART 继续执行，只能 LOAD TERMINATE 、 LOAD REPLACE 来解除。
     出现该状态的两种情况：当 LOAD 失败，之后又前滚到 LOAD 之后的某个时刻；表处于 LOAD in Progress 或 Load Pending 时进行了在线备份。
Unknown：
     表处于未知状态，Load Query 无法得到表状态信息。客户端版本与数据库服务器版本不同时出现该状态。

LOAD FROM SALES.DEL OF DEL
  MODIFIED BY COLDEL| CHARDEL*
  SAVECOUNT 100
  MESSAGES MSGS.TXT
  TEMPFILES PATH D:\TEMP
  INSERT INTO SALES FOR EXCEPTION SALES_EXCEPTION
  STATISTICS YES AND INDEXES ALL
  COPY NO
  CPU_PARALLELISM 4
  DISK_PARALLELISM 3
  INDEXING MODE INCREMENTAL

--load 实验
db2 "create table t1(empno int not null primary key , name char(10), seqno int not null ) "
db2 "create table t1_exp like t1"
db2 "alter table t1_exp add column ts timestamp add column msg clob(32K)"

cat t1.del
10,"wang qi",1
20,"zhang san",
30,"xu xin",3
40,"aaaa",x
50,"bbbb",6
50,"ccccc",7
80,"li si",8

db2 "load from t1.del of del
   modified by dumpfile=/data/imp_data/t1.dmp
   messages t1.msg
   insert into t1
   for exception t1_exp
"
--查询表的状态
db2 "load query table t1"

db2 "create table t2_exp like t1"
db2 "alter table t2_exp add column ts timestamp add column msg clob(32K)"

db2 "load from t2.del of del
   modified by dumpfile=/data/imp_data/t2.dmp
   messages t2.msg
   replace into t2
   for exception t2_exp
"

-- copy no （load 默认选项）， load后表空间处于 backup pending 状态（表无法更新， 所以生产千万不要用该选项），
需要一次表空间备份或全备份，否则出了问题无法恢复。
db2 "create tablespace ts1"
db2 "create table t3 (id int, name char(20) ) in ts1"
db2 update db cfg for testdb using logarchmeth1 "disk :/home/db2inst1/archivelog/testdb"
db2 backup db testdb to /data/backup/testdb compress
more t3.del
1,'aaa'
2,'bbb'
3,'ccc'
db2 "load from t3.del of del insert into t3"
db2 "select * from t3"
db2 "update t3 set name='newccc' where id=3"
db2 list tablespaces show detail
db2 backup db testdb to /data/backup/testdb compress

--copy yes ， load后会自动进行备份。load 时间会长点。
more t3.del
1,'aaa'
2,'bbb'
3,'ccc'
db2 "load from t3.del of del insert into t3 copy yes to /data/backup/testdb "
ls -l /data/backup/testdb
db2 restore db testdb taken at 20131014210541
db2 rollforward db testdb to end of logs and stop
db2 connect to testdb
db2 "select * from t3"

--nonrecoverable 表不可恢复，load后不备份，也不阻止查询和修改。load 后需要进行全备份。
以后要前滚到 nonrecoverable load选项之后的某个时间点，这个表不可恢复，所有与该表有关的日志被忽略，只能重建该表。
more t3.del
1,'aaa'
2,'bbb'
3,'ccc'
db2 "load from t3.del of del insert into t3 nonrecoverable"
db2 "select * from t3"
db2 list tablespaces show detail
db2 restore db testdb taken at   20131014210541
db2 rollforward db testdb to end of logs and stop
db2 connect to testdb
db2 "select * from t3"

--set integrity pending 完整性检查 SQL0668N
db2  "create table t2 (col1 char(10), col2 char(10) ) "
db2 "alter table t2 add constraint check1 check(col2 in ('A','B','C') )"
cat t2.del
"AAA","A"
"BBB","B"
"CCC","CCC"
"DDD","BBB"
db2 "load from t2.del of del insert into t2"
db2 "select * from t2"   查询表时发现：SQL0668N，set integrity pending
db2 set integrity for db2inst1.t2 immediate checked
db2 "create table t2_exp like t2"
db2 "set integrity for db2inst1.t2 immediate checked for exception in t2 use t2_exp"
db2 "select * from t2_exp"


--检查表的主外键约束、检查约束，处于 set integrity pending 等
db2 "select tabname,status,access_mode, substr(const_checked,1,1) as fk_checked, substr(const_checked,2,1) as cc_checked
from syscat.tables
where status='C'
"

--对主表做完整性检查，依赖表会处于 check pending
create table a (x int not null primary key)
create table b (x int not null primary key)
create table c (x int not null primary key)
create table d (x int not null primary key)
create table e (x int not null primary key)
alter table a  add foreign key(x) references b
alter table b  add foreign key(x) references c
alter table c  add foreign key(x) references d
alter table d  add foreign key(x) references e

load from /dev/null of ixf replace into c; -- c 表处于 check pending status
set integrity for c immediate checked; -- 对 c 表做完整性检查，c 表的依赖表会处于 check pending
select tabname from syscat.tables where status='C' --查看处于 check pending 的表，发现 a 和 b 表都处于 check pending

--将表 t1 置于 set integrity pending 状态、不允许访问，同时将其依赖表置于 set integrity pending 状态
db2 "set integrity for t1 no access cascade immediate"

--不对 manger 表的外键约束和 employee 的检查约束做检查，并表脱离set integrity pending状态
db2 " set integrity for manager foreign key, employee check immediate unchecked

--表已有数据且存在违背约束的数据，通过set integrity 将约束检查关闭，待创建约束完后再进行数据完整性检查
db2 "create table emp1(empno int not null primary key, empname char(20), deptno int)"
db2 "create table dept1( deptno int not null primary key, deptname char(20))"
db2 "insert into emp1 values(111,'zhangming',1)"
db2 "insert into emp1 values(222,'zhangming22',2)"
db2 "insert into dept1 values(1,'dev')"
db2 "create table emp1_exp like emp1"
db2 "set integrity for emp1 off"
db2 "alter table emp1 add foreign key (deptno) references dept1(deptno) on delete cascade"
db2 "set integrity for emp1 immediate checked for exception in emp1 use emp1_exp"


--出现 load pending 怎么办
db2 "create bufferpool bp8k size automatic pagesize 8k"
db2 "create tablespace ts2 pagesize 8k managed by database 

 using  ( file '/data/i1_testdb/tbs_data/ts2' 256 ) bufferpool bp8k

"
db2 "create table t2 (id int ,name char(50) , desc char(50) ) in ts2"

cat sp_insert.sql
create procedure sp_insert (in count int)
language sql
begin
  declare i integer default 0;
  while i   do
    insert into t2 values(i,'abcdefghixxxxxxx'|| char(i),'bbbbbbbbccccccccccc'|| char(i) );
    set i=i+1;
  end while;
end
@

db2 -td@ -f sp_insert.sql
db2 "call sp_insert(7000)"
db2 "export to t2.del of del select * from t2"
db2 "drop tablespace ts2"
db2 "create tablespace ts2 pagesize 8k managed by database using (file '/data/i1_testdb/tbs_data/ts2' 200) bufferpool bp8k"
db2 "create table t2 (id int, name char(50),desc char(50) ) in ts2 "
db2 "load from t2.del of del insert into t2"
db2 load query table t2
db2 "select * from t2"
db2 "load from /dev/null of del terminate into t2"
db2 "select * from t2"
db2 "select substr(tabschema,1,10) as tabschema,substr(tabname,1,20) as tabname
from sysibmadm.admintabinfo
where load_status='PENDING'
"  

--数据分隔符不是逗号/双引号 怎么办
cat t1.del
10;'wang qi';1
20;'zhang san';2

db2 truncate table t2 immediate
db2 "create table t2 (id int, name char(20),id2 int)"
db2 "import from t1.del of del modified by coldel; chardel'' insert into t2"

--分隔符的限制
用户要确保数据中不能包含分隔符
分隔符不能是换行符、回车符、0x00或空格
在DBCS环境下（如中文系统），不能用“|”符号作为分隔符
如果 DEL 文件通过某个特殊字符分隔，而import、load、export 命令无法通过键盘敲入，可转换为十六进制，如0x7c代表“|”

对于decimal值，默认的导出格式是在前面有+号，如果位数不够定义的长度，则用0补齐。

db2 "create table t2(c1 char(10),c2 decimal (7,2))"
db2 "insert into t2 values('aaa',23.4)"
db2 "insert into t2 values('bbb',1.2)"
db2 "export to t2.del of del select * from t2"
cat t2.del
"aaa       ",+00023.40
"bbb       ",+00001.20

用decplusblank去掉+号(+号转换为空格),striplzeros去掉前面的0
db2 "export to t2_1.del of del modified by decplusblank striplzeros select * from t2"
cat t2_1.del
"aaa       ", 23.40
"bbb       ", 1.20

日期格式转换
db2 "create table t3 (c1 char(10),c2 timestamp)"
db2 "insert into t3 values('aaa',current timestamp)"
db2 "export to t3.del of del modified by timestampformat=\"yyyy/mm/dd hh:mm:ss.uuuuuu\"  select * from t3"
cat t3.del
"aaa       ","2013/10/16 18:48:23.036031"

-------------------------------------------------------
DEL  method  P  选项
ASC method  L  选项
IXF   method  N,P 选项

--文件中的列比导入的表的字段多 怎么办
db2 "create table t4(c1 char(10),c2 int)"
cat t4.del
"aaaa","bbbb",3,"adbc",3
"bbbb","bbbb",4,"abfc",4
"cccc","bdd",5,"abcd",5
db2 "import from t4.del of del method P(1,3) insert into t4"

--文件中的列比导入的表的字段少 怎么办
db2 "create table t4 (c1 char(10),c2 int, c3 int)"
cat  t4.del
"aaaa",3
"bbbb",4
"cccc",5
db2 "import from t4.del of del method P(1,2,999) insert into t4"
文件中不存在999列，所以插入空值
db2 "select * from t4"

C1         C2          C3        
---------- ----------- -----------
aaaa                 3           -
bbbb                 4           -
cccc                 5           -

  3 record(s) selected.


--sequence数据 怎么办
selecct max(id) as counter from tabname
alter sequence seq_name restart with counter+1

db2 "create sequence seq1 as integer start with 1 increment by 1 minvalue 1 maxvalue 99999999 cycle cache 10"
cat seq.del
1,"AAAA"
2,"BBBB"
3,"CCCC"
db2 "create table t2 (id int ,name char(20))"
db2 "load from seq.del of del insert into t2 copy yes to /data/backup/testdb"
db2 "select * from t2"
db2 "select max(id) maxid from t2"
db2 "alter sequence seq1 restart with 4"
db2 "insert into t2 values(nextval for seq1,'dddd')"

--导入identity 数据 怎么办
1）identity 修饰符
identityoverride 使用数据文件的值，该修饰符只适用于generated always,且在load中适用
identityignore   忽略数据文件的值
identitymissing  数据文件不含自增值
字段：generated always as identity 或 generated by default as identity
2）generated修饰符
generatedoverride
generatedignore
generatedmissing
字段：generated always as

cat  t6.del
3,"shrek"

db2 "create table t6 (custno smallint not null generated always  as identity(start with 500,increment by 1), custname varchar(20)) "
--
db2 "load from t6.del of del modified by identityoverride  messages t6.msg insert into t6 copy yes to /data/backup/testdb "
--
db2 "select * from t6"

CUSTNO CUSTNAME           
------ --------------------
     3 shrek 

db2 "load from t6.del of del modified by identityignore  messages t6.msg insert into t6 copy yes to /data/backup/testdb "
db2 "select * from t6"

CUSTNO   CUSTNAME           
------        --------------------
     3             shrek              
   500          shrek

db2 "alter table t6 alter custno restart with 550"
db2 "insert into t6(custname) values('hello')"
db2 "select * from t6 order by 1"

CUSTNO CUSTNAME           
------ --------------------
     3   shrek              
   500 shrek                        
   550 hello              

--
cat t7.del
MinWei
db2 "create table t7(custno int not null generated always as identity (start with 500,increment by 1), custname varchar(20))"
db2 "load from t7.del of del modified by identitymissing messages t7.msg insert into t7 copy yes to /data/backup/testdb "

--
cat t10.del             
"aaaaaaa",102000.00
"bbbbbbb",1302200.12
"ccccccc",123456.32
"ddddddd",-1234567890.234
"eeeeeee",123456789.234

db2 "create table t10 (name varchar(20) not null,salary decimal(9,2),bonus decimal(9,2) generated always as (salary/100) )"

db2 "load from t10.del of del modified by generatedmissing insert into t10 copy yes  to /data/backup/testdb"

SQL0406N  A numeric value in the UPDATE or INSERT statement is not within the
range of its target column.  SQLSTATE=22003

SQL3185W  The previous error occurred while processing data from row "F0-4" of
the input file.

db2 "select * from t10" 

NAME                 SALARY      BONUS     
-------------------- ----------- -----------
aaaaaaa                102000.00     1020.00
bbbbbbb               1302200.12    13022.00
ccccccc                123456.32     1234.56

--导入数据有换号符 怎么办
db2 "create table test1 (c1 char(20))"

--两条记录
cat test.del
"a  
b    "
"c   
d    "

db2 "load from test.del of del insert into test1 copy yes to /data/backup/testdb "

db2 "select * from  test1"   

C1                 
--------------------
"a                 
b    "             
"c                 
d    "             

  4 record(s) selected.

--delprioritychar 双引号之间不管有没有换行符都视作一条记录
db2 "load from test.del of del modified by delprioritychar  insert into test1 copy yes to /data/backup/testdb "

db2 "select  '*' || c1 || '*' as c1 from test1" 

C1                   
----------------------
*a  
b              *
*c   
d             *

  2 record(s) selected.

db2 "export to test2.del of del select replace(c1,chr(10),'') from test1"
SQL3105N  The Export utility has finished exporting "2" rows.

cat test2.del
"a   b              "
"c    d             "

默认的import/load优先级从高到低：行分隔符、字符分隔符、列分隔符
行优先级最高，换行符是默认的行分隔符，有换行，load就认为是新的记录。



--迁移出现乱码 怎么办

对于IXF格式，一般不会出现编码问题，因为自动编码转换。
对于DEL格式，数据的编码不同，遇到汉字乱码。

--load
默认情况，
load假定数据文件为数据库codepage编码，并将转换为数据库的编码来导入。
所以数据文件的编码与数据库的编码不同时，导入要加数据文件的编码
参数 modified by codepage=

--import
默认情况，import认为输入文件的数据是用当前系统的代码页编码
（通过db2set db2codepage=设置，如1386,1208）
若数据文件编码不是当前系统代码页编码，则修改。

db2 get db cfg | grep -i code      
Database code page                                  = 1208
Database code set                                      = UTF-8
Database country/region code                 = 86

--
1)准备数据
db2 "create table t1(id int,name varchar(20))"
db2 "insert into t1 values(100,'许明文' )"
db2 "select * from t1"
2)以不同编码导出
db2 "export to t1 of del select * from t1"
db2 "export to t1_1208 of del modified by codepage=1208 select * from t1"
db2 "export to t1_1386 of del modified by codepage=1386 select * from t1"
db2 "export to t1_819 of del modified by codepage=819 select * from t1"
3)load 导入测试 通过 modified by codepage 修改为数据文件的编码即可
db2 delete from t1
db2 "load from t1_819 of del modified by codepage=819 insert into t1"     --不支持中文
db2 "load from t1_1208 of del modified by codepage=1208 insert into t1"  --正常导入
db2 "load from t1_1386 of del modified by codepage=1386 insert into t1"  --正常导入
db2 "load from t1_1386 of del insert into t1"                                              --编码不对
4)import 导入 db2codepage一定要设置为数据文件的编码
db2set db2codepage=1386
db2 terminate
db2 connect to testdb
db2 "import from t1_1386 of del insert into t1"  --正常导入
db2 "import from t1_1208 of del insert into t1"  --乱码




--表迁移其他表空间
1） db2 create tablespace ts3
2）db2 "create table t4 (c1 char(100), c2 int ) in ts3"
3）db2 "select substr(TABNAME,1,20) as tabname,
  substr(TABSCHEMA,1,20) as tabschema,TBSPACEID,
  substr(TBSPACE,1,20) as tbspace 
from syscat.tables
where tabname='T4'
"
TABNAME              TABSCHEMA            TBSPACEID TBSPACE            
-------------------- -------------------- --------- --------------------
T4                   DB2INST1                    12 TS3    

4）db2 create bufferpool bp32k size automatic pagesize 32k
5）db2 create tablespace tbs32k pagesize 32k bufferpool bp32k
6）db2 "call sysproc.admin_move_table('DB2INST1','T4','TBS32K','TBS32K','TBS32K','','','','','','MOVE') "

  Result set 1
  --------------

  KEY                              VALUE                                                                                                                          
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1                                                                                                                        
  CLEANUP_END                      2013-10-17-23.03.54.353431                                                                                                      
  CLEANUP_START                    2013-10-17-23.03.54.215935                                                                                                      
  COPY_END                         2013-10-17-23.03.53.472986                                                                                                      
  COPY_OPTS                        ARRAY_INSERT                                                                                                                    
  COPY_START                       2013-10-17-23.03.53.349348                                                                                                      
  COPY_TOTAL_ROWS                  2                                                                                                                               
  INDEXNAME                                                                                                                                                        
  INDEXSCHEMA                                                                                                                                                      
  INIT_END                         2013-10-17-23.03.53.263126                                                                                                      
  INIT_START                       2013-10-17-23.03.51.852221                                                                                                      
  REPLAY_END                       2013-10-17-23.03.54.043163                                                                                                      
  REPLAY_START                     2013-10-17-23.03.53.473723                                                                                                      
  REPLAY_TOTAL_ROWS                0                                                                                                                               
  REPLAY_TOTAL_TIME                0                                                                                                                               
  STATUS                           COMPLETE                                                                                                                        
  SWAP_END                         2013-10-17-23.03.54.158329                                                                                                      
  SWAP_RETRIES                     0                                                                                                                               
  SWAP_START                       2013-10-17-23.03.54.076932                                                                                                      
  VERSION                          09.07.0000                                                                                                                      

  20 record(s) selected.

  Return Status = 0
7）
db2 "select substr(TABNAME,1,20) as tabname,
  substr(TABSCHEMA,1,20) as tabschema,TBSPACEID,
  substr(TBSPACE,1,20) as tbspace 
from syscat.tables
where tabname='T4'
"

TABNAME              TABSCHEMA            TBSPACEID TBSPACE            
-------------------- -------------------- --------- --------------------
T4                   DB2INST1                    12 TS3    

--表空间查询
db2 "select TBSPACEID, substr(TBSPACE,1,20) as tbspace, PAGESIZE , EXTENTSIZE
from syscat.tablespaces
order by TBSPACEID
"
TBSPACEID   TBSPACE              PAGESIZE    EXTENTSIZE
----------- -------------------- ----------- -----------
          0 SYSCATSPACE                 4096           4
          1 TEMPSPACE1                  4096          32
          2 USERSPACE1                  4096          32
          3 TBS_DATA                   32768          32
          4 TBS_TEMP                   32768          32
          5 TBS_USER_TEMP              32768          32
          6 TBS_INDEX                  32768          32
          7 TBS_DATA2                   4096          32
          8 SYSTOOLSPACE                4096           4
          9 SYSTOOLSTMPSPACE            4096           4
         10 TS1                         4096          32
         11 TS2                         4096          32
         12 TS3                         4096          32
         13 TBS32K                     32768          32

  14 record(s) selected.

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

转载于:http://blog.itpub.net/22661144/viewspace-1477200/