Mload - upsert scenario when Target row updated by multiple source rows


Mload - upsert scenario when Target row updated by multiple source rows

Greetings Experts,

Does a multiload job fail ever due to data issue?  If so under what circumstances does it fail before completing the APPLICATION phase?

For instance, one of our job (that does not use multiload) fails occassionally in MERGE statement with issue "Target row updated by multiple source rows" due to the duplicates from the source.

If we use Mload in this scenario (the target table has NUPI) will the upsert logic fail (as it finds multiple source rows to update a single target row) or runs to success?

If it fails so, does it imply that Mload ignores the duplicates on the PI even though it is NUPI which is not the case?

If it completes successfully, will the qualified rows in the target table be updated twice as there are 2 rows with same PI value in source or just once as  "mark duplicate update rows" is the default error treatment option there by it marks the duplicate update rows from source to UV table?

How does Mload distinguish entire duplicate records that are present in source and the records that have came after restart (reading the same rows after checkpoint that has already been sent to worktable)

Tags (1)
Junior Contributor

Re: Mload - upsert scenario when Target row updated by multiple source rows

Hi Cheeli,

MLoad will simply apply all INSERTs/UPDATEs to the target row in the correct order. If the target table is SET and a duplicate row is inserted or an update results in a duplicate row it might be logged to the error table base on MARK/IGNORE, but it will never fail.

MLoad adds a unique "match tag" to each record, based on this it can distinguish between duplicate rows in the input file and those as a result of sending the same rows a 2nbd time after a restart.

Depending on your needs you might also try to create an error table for the target and the add LOGGING ALL ERRORS to the MERGE, but then the row causing the "Target row updated by multiple source rows" error will not be updated.

Or you apply GROUP BY to your USING select to pre-aggregate.



Re: Mload - upsert scenario when Target row updated by multiple source rows

Hi Dieter,

If the target table (doesn't have unique constraint) is Multiset and has NUPI and the

source has duplicates on the PI (but not the entire record is duplicate i.e. for each

combination of PI, there are say 2 rows), then the multiload will update the one qualifying

row in target 2 times.

As upsert is equivalent of Merge statement (atleast according to me) which is

deterministic, the Merge statement fails in this case.  So, doesn't the upsert fail in this

case?  If the upsert succeeds is this the difference between upsert and merge?

If the default values specified for one of the column say col1 for the target table and

when not specified in the INSERT statement of DML clause of the multiload, will the

worktable consists these default values for the rows for the target table or will the

default values be applied only when inserting into the target table?