Emily question: Can anyone give a sugguestion on how to purge the chidren and in the same time update the parents in large amount without backlog?

Emily Zhang

Emily question: Can anyone give a sugguestion on how to purge the chidren and in the same time update the parents in large amount without backlog?

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

Phil Grainger

Emily question: Can anyone give a sugguestion on how to purge the chidren and in the same time updat
(in response to Emily Zhang)
Not answering your question quite yet, but if “… lock escalation is not allowed inside the organization” WHY have they set an escalation limit so low that you can hit it!

If the lock escalation was set higher, your problems may be solved

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Emily Zhang [mailto:[login to unmask email]
Sent: 26 April 2019 16:00
To: [login to unmask email]
Subject: [EXTERNAL] [IDUGEMEA] - Emily question: Can anyone give a sugguestion on how to purge the chidren and in the same time update the parents in large amount without backlog?


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. 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://urldefense.proofpoint.com/v2/url?u=https-3A__www.google.com_search-3Fq-3Dlock-5Fescalation-26spell-3D1-26sa-3DX-26ved-3D0ahUKEwiE8O-2DFgO7hAhUMwlkKHaNvC-2DkQBQgqKAE&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=Bx1kCnVjHOn2wqFlp74id5D8WxFMW1m5V9jmyyJHQsU&s=UGGaUhcNInhEoApKKiMF2guEPcULqsXUhYpi5ttgZRQ&e=

DB2 DBA

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (49.7k)
  • image002.png (6.7k)
  • image003.png (3.7k)
  • image004.png (<1k)

Emily Zhang

RE: Emily question: Can anyone give a sugguestion on how to purge the chidren and in the same time updat
(in response to Phil Grainger)

Thank you for paying attention to the question. The lock escalation number is on the page level. We can't change that. We are a very busy and big organization, it has its reason to set that way to avoid concurrency.

Emily

DB2 DBA

Pete Suhner

RE: Emily question: Can anyone give a sugguestion on how to purge the chidren and in the same time updat
(in response to Emily Zhang)

Hi Emily,
you've posted this question to the IDUG EMEA Conference Forum, which is related to discussions around IDUG's European Conferences.

You will reach a far larger audience (and thus can expect more feedback) if you re-post it to IDUG's main Db2 forum "DB2-L" (https://www.idug.org/db2-l).

Best regards,

Pete Suhner
IIBM Champion for Analytics

Emily Zhang

RE: Emily question: Can anyone give a sugguestion on how to purge the chidren and in the same time updat
(in response to Pete Suhner)

Very much appreciated. The question has been posted in DB2-L now.

DB2 DBA