Not Logged Initially Tables

Not Logged Initially Tables 意思知道，翻译的话感觉有点别扭，就这么着了。

The NOT LOGGED INITIALLY clause of the CREATE TABLE statement allows you to create a
table that will not be logged when an INSERT, UPDATE, DELETE, CREATE INDEX,
ALTER TABLE, or DROP INDEX operation is performed in the same unit of work in which the
CREATE TABLE statement was issued.

db2inst2@sles11:~> db2 get db cfg for zzdb2 |grep -i "log"         
 Log retain for recovery status                          = NO
 User exit for logging status                            = YES
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 300
 Log buffer size (4KB)                        (LOGBUFSZ) = 256
 Log file size (4KB)                         (LOGFILSIZ) = 128
 Number of primary log files                (LOGPRIMARY) = 13
 Number of secondary log files               (LOGSECOND) = -1

--SecureCRT session 1
db2 +c -td@
db2 => create table t1 (c1 varchar(100), c2 varchar(100)) NOT LOGGED INITIALLY @

--SecureCRT session 2
db2inst2@sles11:/db2log2> db2 get db cfg for zzdb2 |grep -i "first active"       
 First active log file                                   = S0000045.LOG

--创建表时指定 NOT LOGGED INITIALLY ，commit前不产生日志
--SecureCRT session 1
db2 => begin
declare i int default 0;
while (i<100000) do
insert into t1 values('aaaaaaaaa' || char(i),'bbbbbb'||char(i) );
set i=i+1;
end while;
end @
db2 => COMMIT @
--SecureCRT session 2
db2inst2@sles11:/db2log2> db2 get db cfg for zzdb2 |grep -i "first active"       
 First active log file                                   = S0000045.LOG
 
--commit后，新的插入语句会产生日志
--SecureCRT session 1
db2 => begin
declare i int default 100000;
while (i<200000) do
insert into t1 values('aaaaaaaaa' || char(i),'bbbbbb'||char(i) );
set i=i+1;
end while;
end @
db2 => COMMIT @

--SecureCRT session 2
db2inst2@sles11:/db2log2> db2 get db cfg for zzdb2 |grep -i "first active"       
 First active log file                                   = S0000058.LOG
 
--手动使 NOT LOGGED INITIALLY 生效，在 commti 之前，不产生日志
--SecureCRT session 1
db2 => ALTER TABLE t1 ACTIVATE NOT LOGGED INITIALLY @
db2 => begin
declare i int default 200000;
while (i<300000) do
insert into t1 values('aaaaaaaaa' || char(i),'bbbbbb'||char(i) );
set i=i+1;
end while;
end@
db2 => commit@

--SecureCRT session 2
db2inst2@sles11:/db2log2> db2 get db cfg for zzdb2 |grep -i "first active"       
 First active log file                                   = S0000059.LOG
 
Any operation from the CREATE TABLE statement until the first COMMIT is not logged. Once
the COMMIT is issued, any subsequent operation is logged. For this example, the INSERT and
UPDATE statements after the first COMMIT are logged.

After creating the table as NOT LOGGED INITIALLY, if you would like to turn off logging
temporarily again, you can use the ALTER TABLE statement with the ACTIVATE NOT
LOGGED INITIALLY clause, as shown in the example. Any operations between the ALTER
TABLE and the second COMMIT are not logged.

--清空表
ALTER TABLE t1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE