not logged initially

weiruan85

Any changes made to the table by an Insert, Delete, Update, Create Index, Drop Index, or Alter Table operation in the same unit of work in which the table is created are not logged.
   For other considerations when using this option, see the "Notes" section of this statement.
    All catalog changes and storage related information are logged,
as are all operations that are done on the table in subsequent units of work.





    Note: If non-logged activity occurs against a table that has the NOT LOGGED INITIALLY attribute activated, and if a statement fails (causing a rollback), or a ROLLBACK TO SAVEPOINT is executed,the entire unit of work is rolled back (SQL1476N).
    Furthermore, the table for which the NOT LOGGED INITIALLY attribute was activated is marked inaccessible after the rollback has occurred,and can only be dropped.
    Therefore, the opportunity for errors within the unit of work in which the NOT LOGGED INITIALLY attribute is activated should be minimized.




问题描述:
如何利用表的NOT LOGGED INITIALLY特性清空表中的数据

解答:
可以使用DELETE命令清空表中的数据，但是如果表中的数据量很大，删除数据会记录大量的日志。

如果不希望清空数据时记录大量的日志，可以考虑使用空文件IMPORT(或LOAD)加REPLACE参数来删除表中的数据。

此外，如果表在创建时指定了NOT LOGGED INITIALLY特性，可以考使用ALTER TABLE命令清空数据。

例如，由如下命令创建的表nolog：

db2 => create table nolog(id int, name char(40)) not logged initially
DB20000I  SQL 命令成功完成。

可以用下面的命令删除表中的所有数据：

db2 => alter  table nolog activate not logged initially with empty table
DB20000I  SQL 命令成功完成。

db2 => commit
DB20000I  SQL 命令成功完成。

db2 => select * from nolog

ID          NAME
----------- ----------------------------------------

0 条记录已选择。

注意，使用ALTER命令+ACTIVATE NOT LOGGED INITIALLY，在同一个交易中，如果回滚，这个表将处于不能存取的状态。

db2 => alter table nolog activate not logged initially with empty table
DB20000I  SQL 命令成功完成。   

db2 => rollback
DB20000I  SQL 命令成功完成。
  
db2 => select * from nolog

ID          NAME
----------- ----------------------------------------
SQL1477N  不能存取表 "DB2ADMIN.NOLOG"。  SQLSTATE=55019