某次群里有讨论起一个不记日志操作,平时认识可能存在些误区,简单做下测试说明。
1.数据库版本 DB2 v9.7.0.8
会话1:执行SQL脚本,会话2:通过db2top 监控执行时,日志使用情况
notlogged.sql
update command options using C off;
alter table staff_test activate not logged initially;
insert into staff_test select * from staff; --不记日志
update staff_test set id=1000 where id=10; --不记日志
上面的notlogged.sql包括4条SQL语句,这样执行db2 -tvf notlogged.sql。并记录了日志记录情况。
下面的按照这个逻辑进行测试和记录结果。
notlogged2.sql
alter table staff_test activate not logged initially;
insert into staff_test select * from staff; --记录日志
insert into staff_test select * from staff; --记录日志
notlogged3.sql
update command options using C off;
alter table staff_test activate not logged initially;
insert into staff_test select * from staff; --不记日志
commit;
insert into staff_test select * from staff; --记录日志
notlogged2.sh
db2 "alter table staff_test activate not logged initially"
echo "step 1"
db2 "insert into staff_test select * from staff" --记录日志
echo "step 2"
db2 "insert into staff_test select * from staff" --记录日志
t1.sh
db2 "alter table t2 activate not logged initially"
db2 "begin atomic declare i int default 0;
while (i<1000000) do insert into t2 values(i);set i=i+1;end while;end" --记录日志
t2.sh
db2 +c "alter table t2 activate not logged initially"
db2 "begin atomic declare i int default 0;
while (i<1000000) do insert into t2 values(i);set i=i+1;end while;end" --不记录日志
t3.sh
db2 +c "alter table t2 activate not logged initially"
db2 "begin atomic declare i int default 0;while (i<100000) do insert into t2 values(i);
set i=i+1;end while;end" --不记日志
db2 "begin atomic declare i int default 0;while (i<100000) do insert into t2 values(i);
set i=i+1;end while;end" --记录日志
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞2
添加新评论3 条评论
2014-07-22 17:49
db2 +c "alter table staff_test activate not logged initially"
db2 +c "insert into staff_test select * from staff"
db2 +c "insert into staff_test select * from staff"
遇到第一个没有使用+c的事务自动提交。
2014-07-22 11:29
db2 "alter table staff_test activate not logged initially"
echo "step 1"
db2 "insert into staff_test select * from staff" --记录日志
echo "step 2"
db2 "insert into staff_test select * from staff" --记录日志
2014-07-22 10:43