Update Else Insert logging

[cjhardma]

During a Update Else Insert (upsert) action to a target. I am wanting to get the ratio or the actual count of the number of records that where updated and the amount that where inserted.

I have been reading though informatics documentation and I can't seem to find an answer. I have a feeling that Upserts happen database side, so there is no clear way for informatica to know the actual count. Is this feeling correct?

 

[blom0344]

Nope, this information can be found in both the logfile and in the PC monitor GUI

Ties Blom

 

[cjhardma]

In the log file it tells you what has been affected and what was attempted to be sent and the number failed. It does not saying what actually happened. Like if you send 100 records and 50 will be updates. It will have rows affected being 100 and not tell you that 50 where updates and 50 where inserts.

 

[blom0344]

Well, from memory the number of inserts and updates where somewhere in the logfile.

However, if I am mistaken then you could define pre and post SQL scripts that write the record-count of the target to a logtable. The difference in these counts would constitute the number of inserts. The total number of rows affected minus the number of inserts would then yield the number of updates.

(assuming that you only update and insert and not perform any deletes as well)

Ties Blom

 

[cjhardma]

There is an option NUMROWSAFFECTED which when set to true, will give me the number of rows that have changed. Currently in my setup it is set to false. So rows affected will be the same as rows processed. So if I change NUMROWSAFFECTED, I will mostly what I am looking for.

One problem is, I need to know how many of those affected rows where deletes or updates. Which this option doesn't give. Also I have a feeling that if we use an external loader, the number of rows affected will be either zero our the number of rows I send it. Because there is no way for the loader to tell informatica the actual number after the workflow has completed.

 

[blom0344]

You could define a task that creates a copy of the table (or just the PK if space is an issue) , then perform a delta analysis after the loading of the target (to establish the number of deletes, which I think is the only way to find out) and drop the copy afterwards.

However , 'update else insert' is poor man's ETL. Best practice is to split and perform each action on it's own. That way you get the best performance as all inserts can be handled as bulk insert instead of row by row ones..

Ties Blom

 

[cjhardma]

Thats what I was thinking would have to happen. However doing the delta compare would be useless for what I am attempting to do. Which is a data quality check.

Well thanks for everyones input