Forum Stats

  • 3,758,239 Users
  • 2,251,358 Discussions
  • 7,870,124 Comments

Discussions

Soft deletes at target

User_WA691
User_WA691 Member Posts: 111 Employee
edited Dec 12, 2019 1:47AM in GoldenGate

Hello forum members,

My query is on soft delete operations.

For certain tables, when my source operation is: delete then, target should set one of flag column to : Y(sayings its deleted) instead deleting it physicall from target.

For this i tried to ues: UPDATEDELETES parameter.

Its working as expected but when same record(which is deleted before) is inserted at source, replicat throwing error saying: record already exists. So key violation issue.

If i use HANDLECOLLISIONS its working without error, but its not best practices to use HANDLECOLLISIONS parameter in production because of few downsides of it rite.

So, how we normally tackle this requirement in Goldenagate. Any otherways, please let me know

--

Regards,

KK.

«1

Answers

  • ORASCN
    ORASCN Member Posts: 1,816 Gold Trophy
    edited Dec 3, 2019 9:25AM

    Hi KK,

    Obviously you will hit this issue as you are trying to insert a duplicate record. Since the table has PK, it is throwing the error as "row already exists". This is an expected behavior.

    Using HANDLECOLLISIONS, you can overcome this, because, when the HANDLECOLLISIONS parameter is set, data is processed as follows : -

    Missing updates are ignored.

    Missing deletes are ignored.

    Duplicate inserts are turned into updates.

    PK updates will be inserted

    Can you try using a composite PK. For example,

    Primary Key = col1 and col2

    where col1 = not null column -----------> This will not change until you update it

    col2 = sysdate      -----------------> this will change for each and every INSERTS

    This will break the uniqueness and allows the new INSERT in the target. So, you will have both deleted row and newly inserted row.

    But for this, you need to alter the table and this is one of the critical one if you are handling major applications.

    Regards,

    Veera

    User_WA691
  • User_WA691
    User_WA691 Member Posts: 111 Employee
    edited Dec 6, 2019 5:27AM

    Hi Veera,

    Thanks for the reply.

    If we make this combination of composite key; and whenever there is update at source, it goes as new insert rite instead updating existing row at target and update flag to 'Y"

    below is delete flag (another extra column at target)

    ,DELETED_FLAG=@CASE(@GETENV('GGHEADER','OPTYPE'), 'DELETE', 'Y', 'N')

    --

    Regards,

    KK,

  • ORASCN
    ORASCN Member Posts: 1,816 Gold Trophy
    edited Dec 9, 2019 3:05AM

    Hi KK,

    Yes, that is your requirement right?

    If No, then, please explain with an example, will prepare the parameters for you and share.

    Regards,

    Veera

  • User_WA691
    User_WA691 Member Posts: 111 Employee
    edited Dec 9, 2019 5:55AM

    Hi Veera, thank you for the reply..

    Below is the example.

    1)  There is insert at source

    2)  Same transaction been inserted at target with deleted_flg='N" (with the logic there in replicat parameter file)

    3)  Now, the record is deleted from source

    4)  Same replicated to target, but because of UPDATEDELETES parameter, it did not delete the record physically from target, but just updated DELETED_FLG='Y'

    5)  After few days, same record with same values is inserted at source,

    6)  Now, at target (if I use HANDLECOLLISIONS), it just updates the deleted_flg to "N" and some other timestamp columns to current timestamp.

    Above is requirement.

    If I use new composite key, after 5th step, I will get one more record with delete_flg='N but there is also record with delete_flg='Y' rite? So would like to maintain only one record with updating flags(deleted_flg). Should be like how its doing when we use HANDLECOLLISIONS parameter.

    --

    Regards,
    KK.

  • ORASCN
    ORASCN Member Posts: 1,816 Gold Trophy
    edited Dec 10, 2019 3:50AM

    Hi KK,

    I need to perform a test case on this. Will get back to you on this.

    Regards,

    Veera

  • User_WA691
    User_WA691 Member Posts: 111 Employee
    edited Dec 10, 2019 3:54AM

    Thanks veera for your time and help.

    Also, sorry to ask but do you have any pointers on my another thread:

    Audit Column logics for softdelete

  • K.Gan
    K.Gan Member Posts: 2,707 Bronze Crown
    edited Dec 11, 2019 9:18PM

    Your logic is not correct.

    What you want to do is

    insert

    delete (mark as delete as you have done)

    insert (same key)

    You should insert this record again because this is a new record. You should not mess with the previous one.

    So how can you do this, you can make both source PK and the flag column as a composite key.

    So when you get an insert, the key is PK+null which will not be there.

    When you get a delete, update as PK+Y.

    The next insert with the same PK will be PK+null which will not be found.

    Ok at the target if you want to find out how many times this row was inserted and deleted you simply do a count on the PK and leave out the flag and you can list other columns to do with this PK.

    If you do what you are originally doing you get a mess up single row.

    Also, you did not say what happens when you get an update? Assuming that you have updates you should look for PK+null as the row to update.

    Cheers

    Kee

  • User_WA691
    User_WA691 Member Posts: 111 Employee
    edited Dec 11, 2019 11:34PM

    Hello Kee,

    Thanks for your reply.

    When transaction is update/insert, the DELETED_FLG is: N

    Yes, the update transaction should do as normal update on (PK+N) combination.

    Forgot to specify thing that: on source side, trandata on table is enabled for all cols. In my current design(and when same record inserted at source), the record at target has delete flg as "Y" is turning to "N" along with other column values (part of new row) are getting updated. I tested this and working fine. Please let me know, if it has any other adverse affects which I could not concentrate if I use same record.

    Our target PDB has size constraint as well, where it should not be exceeding certain size. And, reason why we thought to touch the same record at target instead new record.

    --

    Regards,

    KK.

  • K.Gan
    K.Gan Member Posts: 2,707 Bronze Crown
    edited Dec 12, 2019 12:12AM

    You can do that but your requirements does not make sense if you are just marking it as deleted and you have another insert that overrides it with all columns of the new insert. Then why bother marking the previous row which is now gone.

    What do want out of this on the target?

  • User_WA691
    User_WA691 Member Posts: 111 Employee
    edited Dec 12, 2019 1:47AM

    Understand your point. When overriding, what is the point in having soft deletes(storing them at target). I think this is what your question.

    Let me check with business, why do they need this way. will get back to you. thanks lot.