Updating multiple records

[PhilHibbs]

Is it possible to use a DB2 database stage to update multiple records nfor a single row? I have a stream with a unique key, and I want to update all records with that key in a DB2 table.

[chulett]

Sure, that will happen automatically based on the "grain" of the key values sent. It will update however many records have that key value, be that one or more than one, no different than any other SQL tool.

[PhilHibbs]

I'm having difficulty with this - it doesn't work if the record doesn't exist, and I have set the "Fail on row error" to "No". Is there any other way to do an update only if the row exists, and to just continue if it does not?

I have put in a sparse lookup, but performance is terrible.

[datisaq]

You can do by modifying your job design a bit....
Use a look up stage to match your source to target before loading.
If the records are existing only pass those records for updating to target.
Like change capture stage.

This will work try it.

[chulett]

Sorry, I haven't worked with DB2... what does "it doesn't work if the record doesn't exist" mean? Do you get an error? Oracle wouldn't care and would happily update zero records, I seem to recall that DB2 cares.

Really seems to be unrelated to the original topic.

[PhilHibbs]

It was falling over... so I put in a sparse lookup which slowed it down tremendously. Then I noticed that I hadn't set the "Drop" setting on the Lookup stage, and the job was working just fine even though it was updating rows that didn't exist! I took out the sparse lookup, and now it's working fine. I don't know what I was doing wrong to make it fail earlier. So now it's fine.

[Palci]

@ PhilHibbs - I want to achieve exactly opposite of this. If I get a multiple match after a doing a lookup then I want my job to abort. How do I do that? Any Idea?

[PhilHibbs]

Post another thread. As this thread is "resolved", you might miss out on valuable input from other people. By specifically asking me, you run the risk that I don't know and no-one else has ben asked.

[chulett]

Post another thread.

Exactly. As a matter of fact you've already got your own post on that subject, Palci, no need to run around and jump in other people's posts.

[Palci]

Sorry