Page 1 of 1

Db2 delete performance

PostPosted: Wed Sep 29, 2021 10:49 pm
by sreenath4131
Cobol-Db2 question:
We have a cleanup job which runs once a week to delete the rows(nearly 20 million evryweek) from our tables based on timeperiod(lets say all rows older than 1 year). All the tabes are having a common column Transaction_Id(Unique indexed) but for some reasons we will not use foreign keys concept.And index is not created on Timestamp column because we thought it might impact negatively while doing the insertions.

Problem: As the delete queries use timestamp(which is not indexed column) in where clause to delete the older rows, it is taking lots of time to execute the jobs.
So I thought an alternative like, just take the transaction_id from the main(1) table for data older than a year and for rest of the tables I will use Trransaction_Id in where clause and delete the rows. As the transaction_Id is clustered index column in all tables, my guess is performance will be better in this case.
But again I see the I-O opertaions in alternate method are more than the existing method. Could someone help me which is better option?