Transformation

[suvbin]

Hi , 

Added a new column in transformation and used header column Ar_h _operation.  If we done full load the values in target must be full_load_done and if  values got inserted  in the source , then in target  it must be described as Inserted. i have tried like this.

CASE
WHEN $AR_H_OPERATION = 'INSERT' then
"Inserted"
ELSE
"RR"
END

But everytime its doing insert. not sure how to include full load here. please let me know how to acheive this. 

[Steve_Nguyen]

notes AR_H_OPERATION. For the initial full load, the value is INSERT, whereas for ongoing replication, it's either INSERT, UPDATE, or DELETE.

so not sure what you want from the AR_H_OPERATION on full load.

[suvbin]

My requirement is ,    If the task performs full load then the value in the target must be "full_load_done" and when the values got inserted  in the source end , then in target  it must be described as  "Inserted". 

[Heinvandenheuvel]

The full_load is NOT part of a transaction. You can distinguish a full-load from an application insert looking at variabes like AR_H_TRANSACTION_ID.  Other variables may also be useful such as AR_H_USER.

The 'best' solution is Source Endpoint dependent. I encourage you to create a test task which moves ALL the $AR_H_ variables in added columns and compare the results for full-load vs application insert.

Hein.

[Michael_Litz · Accepted solution]

Hi @suvbin 

Please try this transformation as I believe it will do what you want:

CASE
WHEN $AR_H_STREAM_POSITION = '' AND $AR_H_OPERATION = 'INSERT'
THEN
"Full Load"
ELSE
"Inserted"
END

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!

Michael

[suvbin]

Hi Michael, 

Thank you for your response.  when i used a full load task then the task stopped once the full load has been done.

But in the target end, the value was "inserted" instead of "full load". .....   not sure why.

And more over i think it will not take the second condition.

[john_wang]

Hi @suvbin ,

---

Thank you for your response.  when i used a full load task then the task stopped once the full load has been done.

---

In general there are 2 possibilities: 1) it's Full Load ONLY task; 2) it's FL + CDC task however you enabled the STOPPING Task option (for example):

---

But in the target end, the value was "inserted" instead of "full load". .....   not sure why.

And more over i think it will not take the second condition.

---

@Michael_Litz 's expression works fine in my labs (Oracle to MySQL replication). What's your source and target DB types? and what's the CDC apply mode? You may open a support ticket with Diag Packages so far we may check further for you.

Regards,

John.

[suvbin]

hi @john_wang , 

it worked fine.

@Michael_Litz 

It worked . Thank you.

[john_wang]

Glad to hear that, thanks @suvbin !