Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Update Else Insert logging

Update Else Insert logging

Update Else Insert logging

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?

RE: Update Else Insert logging

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

Ties Blom

RE: Update Else Insert logging

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.

RE: Update Else Insert logging

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

RE: Update Else Insert logging

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.

RE: Update Else Insert logging

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

RE: Update Else Insert logging

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close