Db2

 View Only
  • 1.  Not Logged Initially is running things differently?

    Posted Fri March 29, 2024 01:33 AM
    Hi,
    Activate not logged initially takes 22 minutes for completion of update query with 500 thousand index writes.
    If I run as a simple update query provides sufficient log space it takes 60 minutes with 2 million index writes.
     
    So the culprit here is the number of index writes for the same query execution under two different scenarios.
     
    Why is the question?
    The performance gain of NLI is not from less transaction logging but rather over head in index writes or the way the query is executing itself internally.
    I am yet to generate explain from section and compare the results.
    Please confirm if someone has faced similar issues.


    ------------------------------
    Harishkumar Pathangay
    ------------------------------


  • 2.  RE: Not Logged Initially is running things differently?

    IBM Champion
    Posted Fri March 29, 2024 03:33 PM
    Edited by Jan Nelken Fri March 29, 2024 03:45 PM

    I do not expect NLI insert behave differently - main difference is in COMMIT processing.

    In logged scenario each COMMIT - explicit or implicit (auto commit) - will cause inserted row (data or index) to be logged and externalized to log files;

    In NLI during COMMIT - explicit or implicit - processing all modified pages are flushed to disk.



    ------------------------------
    Jan Nelken
    ------------------------------



  • 3.  RE: Not Logged Initially is running things differently?

    Posted Sat March 30, 2024 04:11 AM
    I am definitely Missing something here. I have to properly track the time taken for transactional log writing.
    Is there any metrics in db2 which will tell this connection or application or database has spent so much time in pushing log records into transactional log files.?
    Can I monitor it at query level or application or agent level?

    Please let me know.

    Thanks
    Harish Kumar 





  • 4.  RE: Not Logged Initially is running things differently?

    IBM Champion
    Posted Sat April 06, 2024 08:50 AM

    Hi Harish,

    I think you found MON_GET_CONNECTION by now.

    https://www.ibm.com/docs/en/db2/11.5?topic=functions-mon-get-connection-get-connection-metrics

    Cheers
    Roland



    ------------------------------
    Roland Schock
    IBM Champion and IBM Gold Consultant
    ------------------------------



  • 5.  RE: Not Logged Initially is running things differently?

    Posted Thu April 11, 2024 10:08 AM

    that link contains 2 pdf files one with NLI ON and other with NLI OFF.

    summary:

    NLI ON average run time is 20 minutes; index writes 380 thousand

    NLI OFF average run time is 30 minutes; index writes 700 thousand

    how can it execute sample plan but different index writes and the claim that not logging is improving performance is not acceptable if double the amount of index writes are happening.

    conceptually it does not make sense. any clarifications inputs are welcome.

    https://drive.google.com/drive/folders/1guc_7NO_4b5n28pH0EPiRSaq9HoOKedC?usp=sharing

    thanks,

    harish pathnagay



    ------------------------------
    Harishkumar Pathangay
    ------------------------------



  • 6.  RE: Not Logged Initially is running things differently?

    Posted Tue April 16, 2024 12:51 PM

    There is not enough factual information in the question to make any sensible comment. You could append the db2exfmt (detailed plan) output for the query in both cases, and give the section-actuals information in both cases, along with the table and index DDL in full, and some details of how you are running the update (#transactions, isolation, concurrency etc).  Sometimes things are not what they seem.



    ------------------------------
    Mike O'Reilly
    ------------------------------



  • 7.  RE: Not Logged Initially is running things differently?

    Posted Sat April 20, 2024 10:33 AM
    Here is all the information i can give you.
    It contains files with commands i executed.
    Db cfg output
    Section Actuals Captured for both cases

    Please let me know if i need to summarize my activity so far.

    --
    Thanks,
    Harish P