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

#Db2

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

------------------------------
Roland Schock
Distinguished Engineer
ARS Computer und Consulting GmbH
Muenchen
------------------------------

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

Original Message:
Sent: Fri May 07, 2021 02:11 AM
From: Roland Schock
Subject: release LOGfiles

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

------------------------------
Roland Schock
Distinguished Engineer
ARS Computer und Consulting GmbH
Muenchen
------------------------------