Results 1 to 3 of 3

Thread: Can we perform a look up on a Target table?

  1. #1
    mkurup is offline Junior Member
    Join Date
    Aug 2019
    Posts
    11
    Rep Power
    0

    Exclamation Can we perform a look up on a Target table?

    Hello,

    We do not have a way to prevent records that are getting removed from the source db as a retention process because of not having delete flag or AR_H_USER. Can we do a look up on a target date field to distinguish if rows are removed based on the date specified in the target table? The dates are captured when records are insert/updated.

    As shown below, can we perform a look up on a Target table and then apply the operation given below?

    (($AR_H_OPERATION = 'DELETE) AND (**TARGET TABLE FIELD***) CDATE < date('now','-10 day'))
    then then OPERATION_INDICATOR("A",NULL,NULL)

    appreciate your help!

    Thanks,
    mk

  2. #2
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    165
    Rep Power
    13
    I'm not sure I managed to completely follow you question.

    Yes Replicate can do target lookups with the function (insert drumroll...) target_lookup
    From the documentation: target_lookup(TTL,'SCHM','TBL','EXP','COND',COND_P ARAMS)
    Please note that this will request a lookup per row, therefore
    - there had better be a good index (easy: PK)
    - the target DB better be nearby (speed of light) and respond quickly
    It may be better to just mark the rows targeted as delete as delete requested and do a periodic (hourly, daily) scan on the target table to decide based on the dates whether the delete was based on retention or not.

    Please note that with a call to operation_indicator will change a Delete to an update... to be able to set an (soft_delete) indicator and/or delete timestamp.

    Without actually studying the code proposed, to integrate with target_lookup it need to looks like -

    (($AR_H_OPERATION = 'DELETE) AND (target_lookup (1000,'your_target_schema,'target_table_name','dat e_column_name','pk=?', $pk) < date('now','-10 day'))
    then OPERATION_INDICATOR("A",NULL,NULL)

    You can potentially integrate the date compare into the CONDition clause.

    Good luck,
    Hein


  3. #3
    mkurup is offline Junior Member
    Join Date
    Aug 2019
    Posts
    11
    Rep Power
    0

    Target date (calculated) fields are getting overwritten with NULL

    Hein,

    Thank you for the explanation on Target lookups. After running through few testings we realized applying target lookup is not plausible on global transformation because of target tables being different or different primary key fields or pk missing to add to the below logic.

    Without actually studying the code proposed, to integrate with target_lookup it need to looks like -

    (($AR_H_OPERATION = 'DELETE) AND (target_lookup (1000,'your_target_schema,'target_table_name','dat e_column_name','pk=?', $pk) < date('now','-10 day'))
    then OPERATION_INDICATOR("A",NULL,NULL)

    So we are going with what you suggested to flag delete in the target and intercept deleted records from source.

    Now issue is when inserted row gets updated, the default date that are set on 'insert_date' is getting overwritten with 'NULL'. How to make the task to ignore a target insert_ date field when rows get updated and only update the update_field?
    There are two date fields in the target table such as insert_date and update_date that does not exists in source and nor there is a date field available in the source database.
    Following are the steps we have on a task:

    for update date:
    CASE WHEN $AR_H_OPERATION ='UPDATE' THEN $AR_H_COMMIT_TIMESTAMP
    END

    For inserted date are set by default value on a date in the target table now ( this is what is getting updated with 'NULL' when row gets updated).

    Can you please provide a solution for this?

    Thank you

Posting Permissions

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