MVSFORUMS.com

[manojvoona]

Hi,

This is regarding the delete jobs.
Our database host data of 45 days. so every day a job runs and deletes the datawhich ever is more than 45 days.

This job is takign more than 2 hours a day and we want to reduce the execution time.

what we thought of is to run the job once in a week. Here the data will be accumulated till 52 days and our job runs on the week end deleting all the data which ever is more than 45 days

Here our question if the job takes 2 hours daily
7 days => 2 * 7 = 14 hours.
Once in a week will it take 14 hour again or is it going to be a considerable saves here.

Thanks in advance. we appreciate all your help

Thanks
Manoj Voona

[kolusu]

manojvoona,

You need to show the query you are using for delete. How are you deleting the rows? using an utility or a pgm with checkpoint restart feature?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[manojvoona]

Hi Kolusu,

Here is the query we are using

DELETE
FROM MUS.TUZ41 A
WHERE RCPT_DT < CURRENT DATE - 45 DAYS
AND NOT EXISTS
(SELECT 1
FROM MUS.TUZ02_MONTH_END B
WHERE B.MONTH_END_DT = A.RCPT_DT
)

This is done in the program with just above query.

thanks
Manoj Voona

[dbzTHEdinosauer]

If you have any kind of volume,
and I assume you have because of the time required to do the mass delete,
I suggest you delete over a cursor. The subselect for the EXISTS could be transfered to a COBOL Table - of 365 items - if necessary. And include periodic COMMITs in your cursor processing logic.

To have complete restart capability (so that you can report on the number of rows deleted) you would need a separate item (row in another table, row in same table with unique key, ...) that you would update with your statistics each time before a commit.
_________________
Dick Brenholtz
American living in Varel, Germany

[manojvoona]

thanks a lot dbzTHEdinosauer,

Can you please elaborate and explain us. I am pretty new to DB2

Thanks in Advance
Manoj Voona

[dbzTHEdinosauer]

The first WHERE phrase I understand. would you please explain the business logic behind the subselect: