load在可恢复数据库中的测试

--可恢复数据库

#####未指定copy yes 测试

db2 connect to testdb

drop table st

create table st (id int primary key not null,name varchar(50),sex int not null) in testdb_data_8k_01 long in testdb_long_32k_01

--创建exception表

create table st_exp like st in testdb_data_8k_01  long in testdb_long_32k_01

alter table st_exp add column ts timestamp add column msg clob(32k)

--student.del

1,"xiao wang",1

2,"xiao chao",

3,"xiao zhao",1

3,"xiao qin",1

4,"xiao zhi",

5,"xiao cheng",1

5,"xiao ming",1

6,"xiao liu",1

7,"xiao li",1

load from student.del of del modified by dumpfile=/data/testdb/load/student.dmp messages student.msg insert into st for exception st_exp

select * from st

1 xiao wang 1

3 xiao zhao 1

5 xiao cheng 1

6 xiao liu 1

7 xiao li 1

--主键冲突

select * from st_exp

3 xiao qin 1 2014-01-07 03:10:06 00001I0000500001

5 xiao ming 1 2014-01-07 03:10:06 00001I0000500001

--违反非空约束

more /data/testdb/load/student.dmp.load.000 

2,"xiao chao",

4,"xiao zhi",

--load结束，可恢复数据库未指定copy yes; 表空间处于0x0020 backup pending

db2 list tablespaces show detail|grep State

--表空间不可写，影响该表空间内所有表

select * from T_TABLE_1

--可读

insert into  T_TABLE_1 select * from T_TABLE_1 where tableid=4 and tbspaceid=3

--不可写；报错db2 ? sql290

db2 backup db testdb tablespace TESTDB_DATA_8K_01 online to /data/backup compress include logs

db2 connect to testdb

db2 list tablespaces show detail|grep State

--normal

insert into T_TABLE_1 select * from T_TABLE_1 where tableid=4 and tbspaceid=3

--备份后可写 

#####copy yes 测试

delete from st

select * from st 

--null

backup db testdb online to /data/backup compress include logs

connect to testdb

load from student.del of del modified by dumpfile=/data/testdb/load/student.dmp messages student.msg insert into st  for exception st_exp copy yes to /data/backup/load/copy

--load结束

--copy出备份文件 4.db2inst1

ll /data/backup/load/copy

-rw-r----- 1 db2inst1 db2iadm1 1871872 Jan  7 03:25 TESTDB.4.db2inst1.NODE0000.CATN0000.20140107034727.001

db2 list tablespaces show detail|grep State

--normal

--恢复copy yes时load数据; 无需指定copy yes 所在位置

force application all

restore db testdb from /data/backup  taken at 20140107034727

rollforward db testdb to end of logs and stop

ROLLFORWARD db testdb  query status

connect to testdb

select * from st 

1 xiao wang 1

3 xiao zhao 1

5 xiao cheng 1

6 xiao liu 1

7 xiao li 1

PS:

使用load的原因是导数据速度快，原因是仅记录少量日志;

但是由于日志不全会导致数据无法回复;

所以load 时使用 copy yes 在归档的数据库中，实现数据的可恢复；否则会导致表空间不可改、写