DB2 - L

  • 1.  Not Logged for Tablespace

    Posted Sep 29, 2021 12:34 PM
    Hi all

    I am wondering to know if anyone has experience with NOT LOGGED attribute for tablespace.

    I have a situation as a long running job . It is in loop to do the same calculations per employee.
    In each iteration it is using temp tables for calculating then Deleting Data from those temp tables.
    Due to slowness for each iteration I am thinking to set NOT LOGGED for temp tables.

    I appreciate if you could share any thoughts, idea or experience.

    Thanks for your help 
    Regards 
    Leila 


  • 2.  RE: Not Logged for Tablespace

    Posted Sep 29, 2021 01:20 PM
    Hi
    Recently I defined a Tablespace with that option, and APPEND YES, trying to make a particular process with better performance.
    The problem is that, you can't issue a ROLLBACK, because as the Tablespace isn't logged, there is no point to rollback to and you'll get an SQL error (don't remenber which)
    I had to alter to LOG YES

    António Barata
     
    Phone: 00351 937402410







  • 3.  RE: Not Logged for Tablespace

    Posted Sep 29, 2021 01:30 PM
    I normally don't recommend NOT LOGGED since the performance benefits are minimal.
    The reason being, if you run into a SQL-error or so and Db2 has to backout uncommitted updates, you end up in RECP which can defeat the purpose of saving CPU/Elapsed.

    Steen





  • 4.  RE: Not Logged for Tablespace

    Posted Sep 29, 2021 03:34 PM

     

    Steen – you had done an IDUG presentation on this many years ago, right?  The CPU savings were tiny as I recall.  Can you quote those, please?

     

    Thx

    Suresh






  • 5.  RE: Not Logged for Tablespace

    Posted Sep 29, 2021 03:39 PM
    That’s correct Suresh - MANY years ago so I will have to dig my archives

    Steen

    Sent from my iPhone




  • 6.  RE: Not Logged for Tablespace

    Posted Sep 29, 2021 04:55 PM
    I am not really a horder, but I did find my IDUG presentation from 2009.

    The IBM recommendation is (was) not to use NOT LOGGED for performance reasons.

    I can send the PDF Leila if needed - just ping me at steen.rasmussen@broadcom.com

    Inline image