Table Load Order

[JDAEMS]

Hi,

I have an urgent question. The source for a mapping is an incremental table. Each record has an identifier saying that it's an ADD, CHANGE or DELETE.

In case of ADD or CHANGE, we use an update-strategy-transformation with DD_UPDATE and on session, we use update else insert. In case of DELETE, we use an update-strategy-transformation with DD_UPDATE.

In the beginning of our mapping, on the source qualifier, we sort our records so that all incremental records are executed in the correct order.

But Informatica does process them in the order we want, but it doesn't do the actions on the database in the order we want.

Example:

We have three records that need to be loaded:
PRODUCT1 ADD 4578
PRODUCT1 DEL 4600
PRODUCT1 ADD 5800

In the source qualifier we order by the third column in this example.

The problem is that no matter what we do, it always processes the delete last. And that's not how we want that. We want to process the first ADD, then the DELETE and then the last ADD.

How can we force this?

Thank you for all your help.
Kind regards
Jelle

 

[Sridharan]

Thats not the way it works... Something wrong somewhere... Maybe the sort order is wrong... Are you sure the record goes as ADD, DEL, ADD... Run a debug session and check the flow...

Sri

 

[JDAEMS]

We've done a debug session and the order seems to be correct like we want it to. So first the add, then the delete and then the add.

But it seems that Informatica first loads the first target and it then load the second target, no matter in what order the records come in.

For ADD and update we have the same target, but for the delete we have another target, which both point to the same physical file.

 

[Sridharan]

As told earlier (something wrong somewhere) why do you want to have the same target twice... You can as well accomplish what you want with a single Target and it should work like charm...

Sri