Hi!

I'm trying to use ALTER TABLE ACTIVATE NOT LOGGED INITIALLY to reduce logging activity (as exposed in the manual http://publib.boulder.ibm.com/infoce...n/c0006079.htm) for the following query on UDB 8.2:

insert into events (loanno, evtyp, amtyp, amt)
select loanno, evtyp, 'CAPTL' as AMTYP, captl
from loanevt where captl > 0
union all
select loanno, evtyp, 'TAX' as AMTYP, tax
from loanevt
where tax > 0
union all
select loanno, evtyp, 'EXPNS' as AMTYP, expns
from loanevt where expns > 0
union all select loanno, evtyp, 'BONUS' as AMTYP, bonus
from loanevt where\
bonus > 0

Unfortunatelly running the query with or without 'not logged intially', produces essentially the same amount of log.

Concretely, running:
alter table events activate not logged initially with empty table
and then running the query results in:

Log pages written = 4000
Log write time (sec.ns) = 1.000000004
Number write log IOs = 1542
...

Running the query normally without using 'not logged initially' results in:
Log read time (sec.ns) = 0.000000004
Log pages written = 3995
Number write log IOs = 1582

Less pages are written than the with the 'not logged initially' option!

By the way, the source table loanevt is a small table. It has 100K tuples with about 30 bytes each. After executing the query this what I see in the buffer pool counters:
...
Buffer pool data logical reads = 4667
Buffer pool data physical reads = 1057

Any ideas would be helpfull.
Thanks a lot in advance.

paulo.

Make sure you execute ALTER TABLE and your insert in the same UOW. Once the UOW is committed the NOT LOGGED state ends, which means in particular that if you have autocommit on the NOT LOGGED state is never in effect.

I was aware from the manual that the NOT LOGGED state is active only for the next UOW but I was forgetting to turn off AUTOCOMMIT.

Now, after executing
UPDATE COMMAND OPTIONS USING c OFF, then ALTER TABLE events ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE, then the query, everything works as expected, I get:

Log pages read = 0
Log pages written = 8

Much better now. BTW, the speed improvement is of about 5x on my Linux box (hope this information is interesting to anyone).

thanks.

If you running a script and want auto-commit off, you can +c:

db2 +c -tvf script.sql

Make sure there is an explicit commit at the end of the script.