Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Lisbon, Portugal

    Unanswered: Reducing logging activity with NOT LOGGED INITALLY


    I'm trying to use ALTER TABLE ACTIVATE NOT LOGGED INITIALLY to reduce logging activity (as exposed in the manual 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.


  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Feb 2004
    Lisbon, Portugal

    Setting autocommit to off solves the problem

    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).


  4. #4
    Join Date
    May 2003
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts