Db2 (On Premises and Cloud)

Expand all | Collapse all

release LOGfiles

  • 1.  release LOGfiles

    Posted Thu May 06, 2021 03:31 AM
    db2 11.1 linux
    we are using a stored procedure with some cursors declared with hold
    reading some data and deleting some data
    at predefined interval - we commit in the procedure to avoid large tx
    we keep track of this in a logtable and happens as requested
    the locks are released, but the logfiles are only released when the SP ends.
    as we started with cleanup in new system, we had many rows to delete - we did with small interval - and anyhow receiving logfull because of this behaviour
    do you know how we could handle this situation to release the logfiles with intermediate operation

    Thanks for all answers
    Best Regards,
    Guy Przytula

  • 2.  RE: release LOGfiles

    Posted Fri May 07, 2021 02:11 AM
    Hi Guy,
    somehow this sounds to me like Db2 is still keeping some pointer in the logs during the full runtime of your SP. How do you create your log files and keep them open?
    So if the database is busy during the runtime of the SP, it could fill the logs.
    You could check this with a "db2 get snapshot for database on xxxx" and check for the oldest application holding the logs. This could be your SP.
    If so, have a look at Adavance Log Management, which is a new feature in Db2 11.5, which is just helping you in such a situation.
    Otherwise you might have to change the way you are logging your data to prevent it from keeping locks.

    Kind regards

    Roland Schock
    Distinguished Engineer
    ARS Computer und Consulting GmbH

  • 3.  RE: release LOGfiles

    Posted Fri May 07, 2021 05:05 AM
    thanks for the update
    as indicated db2 v11.1
    we do not open or keep open the logfiles explicitely.
    we just start the SP - search rows - commit after 1000 rows deleted
    the SP is running for about half an hour and deleting in total 10M rows and we see many logfiles are locked/used by this SP, although the commit.
    only when the SP ends, we see all logfiles released.  the intermediate commit only acts on locks....
    best regards, Guy

    Thanks for all answers
    Best Regards,
    Guy Przytula