Emily quesion: How to reduce a big backlog using reorg discard to delete the records in children tables and update the status in parent table right after the discard

Emily Zhang

Emily quesion: How to reduce a big backlog using reorg discard to delete the records in children tables and update the status in parent table right after the discard

Thanks first.

We have a situation needs to be solved as soon as possible: Currently we use reorg discard to purge the children tables and update the status of the parent table after purging. Because the lock escalation is not allowed inside the organization, we can only purge the children 1000 a time in a limited time window each day. Our organization is a very big, busy and important organization. The 1000 is the page lock escalation limit allowed to avoid concurrency.  Since all the children will update the same parent, we can't run the job parallel. So there are millions of records needs to be processed each year and the current strategy caused millions of records not be able to purged on time.

Basically the current process like this:

1) One job at a time, inside each job, select the primary key of the 1000 records ( lock escalation limit) to be delete by joining the parent and the one child by some condition;

3) reorg discard those records  from the child;

4) update the status of those records in the parent;

5)run another job till all jobs finish or till the time windows is due for that day;

Please anybody with more experience to give us some idea about whether we can improve the process?  

Emily,

DB2 DBA


DB2 DBA

Philip Sevetson

Emily quesion: How to reduce a big backlog using reorg discard to delete the records in children tab
(in response to Emily Zhang)
Emily,

The first question which occurs to me to ask is:

Why are you using REORG DISCARD to remove small amounts of data? Would a program, running a small unit of work processing a set of DELETE statements for the children-and-parent, followed by a COMMIT, do the same?Wouldn’t it be faster?

The other reason to use a program is that you can then run the program for _the entire length of your batch window_, by checking the system clock at the end of every unit of work. If you’re within a few minutes (seconds?) of the end of the window, stop DELETEing, report the work done, and end the run. (Caution: you would want to pessimistically estimate the amount of time between COMMITs, and make sure that you end the run _more_ than that amount of time _before_ the end of the batch window. That way, your last unit of work doesn’t overrun the end of the window.)

Hope this is helpful.

-phil (sevetson)

From: Emily Zhang [mailto:[login to unmask email]
Sent: Friday, April 26, 2019 2:28 PM
To: [login to unmask email]
Subject: [DB2-L] - Emily quesion: How to reduce a big backlog using reorg discard to delete the records in children tables and update the status in parent table right after the discard


Thanks first.

We have a situation needs to be solved as soon as possible: Currently we use reorg discard to purge the children tables and update the status of the parent table after purging. Because the lock escalation is not allowed inside the organization, we can only purge the children 1000 a time in a limited time window each day. Our organization is a very big, busy and important organization. The 1000 is the page lock escalation limit allowed to avoid concurrency. Since all the children will update the same parent, we can't run the job parallel. So there are millions of records needs to be processed each year and the current strategy caused millions of records not be able to purged on time.

Basically the current process like this:

1) One job at a time, inside each job, select the primary key of the 1000 records ( lock escalation limit) to be delete by joining the parent and the one child by some condition;

3) reorg discard those records from the child;

4) update the status of those records in the parent;

5)run another job till all jobs finish or till the time windows is due for that day;

Please anybody with more experience to give us some idea about whether we can improve the process?

Emily, https://www.google.com/search?q=lock_escalation&spell=1&sa=X&ved=0ahUKEwiE8O-FgO7hAhUMwlkKHaNvC-kQBQgqKAE

DB2 DBA



DB2 DBA

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Philip Sevetson

Emily quesion: How to reduce a big backlog using reorg discard to delete the records in children tab
(in response to Philip Sevetson)
Second thought:

Create a cursor (or more than one) containing everything, or a big chunk of everything, which you want to delete. Bind option Cursor Stability or Uncommitted Read. Issue keyed DELETE statements, one row at a time (or more, if you can be certain you won’t accidentally escalate locks). Issue COMMIT WITH HOLD one row before lock escalation (COMMIT WITH HOLD preserves cursors). Repeat until out of candidate keys or out of time.

Again, after COMMIT WITH HOLD, check the time to be sure you’re not hitting the end of your window. Repeat at the beginning of every batch window.

-phil (sevetson)

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, April 26, 2019 2:40 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Emily quesion: How to reduce a big backlog using reorg discard to delete the records in children tables and update the status in parent table right after the discard

Emily,

The first question which occurs to me to ask is:

Why are you using REORG DISCARD to remove small amounts of data? Would a program, running a small unit of work processing a set of DELETE statements for the children-and-parent, followed by a COMMIT, do the same?Wouldn’t it be faster?

The other reason to use a program is that you can then run the program for _the entire length of your batch window_, by checking the system clock at the end of every unit of work. If you’re within a few minutes (seconds?) of the end of the window, stop DELETEing, report the work done, and end the run. (Caution: you would want to pessimistically estimate the amount of time between COMMITs, and make sure that you end the run _more_ than that amount of time _before_ the end of the batch window. That way, your last unit of work doesn’t overrun the end of the window.)

Hope this is helpful.

-phil (sevetson)

From: Emily Zhang [mailto:[login to unmask email]
Sent: Friday, April 26, 2019 2:28 PM
To: [login to unmask email]
Subject: [DB2-L] - Emily quesion: How to reduce a big backlog using reorg discard to delete the records in children tables and update the status in parent table right after the discard


Thanks first.

We have a situation needs to be solved as soon as possible: Currently we use reorg discard to purge the children tables and update the status of the parent table after purging. Because the lock escalation is not allowed inside the organization, we can only purge the children 1000 a time in a limited time window each day. Our organization is a very big, busy and important organization. The 1000 is the page lock escalation limit allowed to avoid concurrency. Since all the children will update the same parent, we can't run the job parallel. So there are millions of records needs to be processed each year and the current strategy caused millions of records not be able to purged on time.

Basically the current process like this:

1) One job at a time, inside each job, select the primary key of the 1000 records ( lock escalation limit) to be delete by joining the parent and the one child by some condition;

3) reorg discard those records from the child;

4) update the status of those records in the parent;

5)run another job till all jobs finish or till the time windows is due for that day;

Please anybody with more experience to give us some idea about whether we can improve the process?

Emily, https://www.google.com/search?q=lock_escalation&spell=1&sa=X&ved=0ahUKEwiE8O-FgO7hAhUMwlkKHaNvC-kQBQgqKAE

DB2 DBA



DB2 DBA

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Michael Hannan

RE: Emily quesion: How to reduce a big backlog using reorg discard to delete the records in children tables and update the status in parent table right after the discard
(in response to Emily Zhang)

Emily,

Your question is a little hard to follow for me. You say you can't run parallel because all children have same parent? Same parent table or same parent row? I assume you mean same table, but row level locking on the parent table might help.

I also can't understand why would use Reorg for such a small number of rows to be discarded (1000). Do you mean child are updated with indicator that they will be deleted?

What makes using a real  DELETE not good for you? Is it too many random indexes on your child table? Is it Referential Integrity checks for some other related table?

Desired total row numbers to be deleted per day or per run, and how many indexes?

Does the Reorg actually happen first and parent rows updated later?

I do like the Reorg with Discard technique, but only to do the entire set of Deletes in one go, and only for large tables with too many random indexes, and where any lower level children were already cleaned up.

Real Deletes with regular commit seems viable unless the scale is just too many rows times too many indexes to have entries removed at random.

If you update a Delete Indicator, you can have application access via a View with predicate on the Delete indicator in order that rows appear to be deleted already before the reorg with Discard happens.

I worry that I just don't fully understand where the real problems are. Sounds like just trying to do too many updates with a single SQL statement, when Lock escalate could happen. So Phil's ideas will probably be relevant. Regular commit of updates is needed, every 2 to 5 seconds as a guide.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Apr 28, 2019 - 02:01 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 28, 2019 - 02:04 PM (Europe/Berlin)