Topic
  • 1 reply
  • Latest Post - ‏2017-10-11T17:53:51Z by sgarcia0033
Robert Burgess
Robert Burgess
6 Posts

Pinned topic Cleanup of large amount of records

‏2017-09-13T13:26:32Z |

In one of our environments, we have a large amount of records that have been transitioned to the null state.  When the cleanup agent runs, it runs out of DB2 transaction log space executing this:

 

DELETE FROM IBS_SPEC_ASSIGNMENTS WHERE EXISTS (SELECT 'X' FROM IBS_SPEC_CA_DELETE WHERE IBS_SPEC_CA_DELETE.SPEC_ID=IBS_SPEC_ASSIGNMENTS.SPEC_ID)

 

For Workflow Instance saves, the cleanup agent now seems to remove the data in small chunks (of 1000 rows each) but for the record data cleanup - it still seems to (try to) remove all data in one huge SQL statement/transaction. Is it possible to get the cleanup agent to remove record data in chunks like it does for workflow instance saves?

 

Otherwise, I'm thinking of writing a small util that would run the statement above but in smaller chunks as it seems we still have the list of record ids that it tries to remove in the IBS_SPEC_CA_DELETE table. Any obvious issues with that?

  • sgarcia0033
    sgarcia0033
    27 Posts

    Re: Cleanup of large amount of records

    ‏2017-10-11T17:53:51Z  

    As of 3.5.3 there have been no changes for the data to be deleted in chunks for IBS_SPEC_ASSIGNMENTS.  This sounds like a good PMR. 

    It is rarely recommended to delete records directly from the database, but in this circumstance you might be ok; however the  IBS_SPEC_CA_DELETE  only exists during the time the cleanup agent is running, it is dropped when the agent goes back to sleep.