Saturday, June 22, 2013

0 DB2 NOT LOGGED INITIALLY

“NOT LOGGED INITIALLY” is quite interesting feature in DB2. Essentially, it change the behavior of logging for the table.

By default, all the changes will be log within the transaction, however when turn on this feature on the table level. INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE operation on the table would not be log in the same unit of the work.  The primary usage for this is when the T1 table is staging area and can be easily created from other source, therefore to avoid the overhead of the logging during the creation time, we can turn off the logging for the table.

To turn on this feature:

CREATE TABLE T1(C1 INT,C_DESC CHAR(20)) NOT LOGGED INITIALLY;

or

ALTER TABLE T1 ACTIVATE NOT LOGGED INITIALLY;

In Oracle, the alternative is use /*+ APPEND */

INSERT /*+ APPEND */ INTO t select c2,desc2 from t2;

But this would only affect the insert statement and the table would not be accessible until commit.

 

Truncate table

To truncate the table in db2:

ALTER TABLE t1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;

In other DBMS: Sybase, Oracle and SQL server.

truncate table t1;

 

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates