Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2012
    Posts
    6

    Unanswered: Script to Purge contact data

    Hello,
    I am new to DB2 environment.
    I have a parent table(UCC_Envelope) and 2 dependant tables(UCC_Contact&UCC_ContactAttr) in the DB2 database.
    UCC_Envelope: PKs(EnvelopeID, ContainerID)
    UCC_Contact :PK(Urlindex) ; FKs(EnvelopeID,ContainerID)
    UCC_ContactAttr :FKs(EnvelopeID,ContainerID, Urlindex)

    What is the best method I can follow to satisfy the business requirement to delete the rows in parent and corresponding dependant tables by using the column ContactDateTime to search and delete the rows that are more than 15months old ?

    Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You work backwards in the deletes:

    Code:
    DELETE from UCC_ContactAttr where (EnvelopeID,ContainerID) in (select EnvelopeID,ContainerID from UCC_Envelope where ContactDateTime < current timestamp - 15 months);
    
    DELETE from UCC_Contact  where (EnvelopeID,ContainerID) in (select EnvelopeID,ContainerID from UCC_Envelope where ContactDateTime < current timestamp - 15 months);
    
    DELETE from UCC_Envelope where ContactDateTime < current timestamp - 15 months;
    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •