DB2 Connector Commit and processing question

[JPalatianos]

Hi,
We have a few different theories on how the following would process and wanted to hear from folks on this forum:

We have a DB2 connector with a commitcount of 5000 that is running in Update then Insert mode. If we have two records with the same key in the same batch of 5000 records will the second record attempt to do an insert after the second was inserted and before the 5000 rows are committed? or will it treat the second record ss an update within that session?

Thanks - - John

[chulett]

Those multiple action modes, like Update then Insert, simply try both actions in the order noted. So it will attempt the update first and only if that fails will it attempt the insert. Guessing you already know that so just putting it out there for completeness.

As to the outcome in that case, if everything is part of the same transaction / unit of work I do not believe that it will matter if the first one is committed or not - assuming new, the first will update (and fail) then be inserted while the second one will successfully update. Unless we throw multiple nodes into the discussion where the two records go to different nodes for some reason, in that case not really sure what would happen but I can't imagine it would be good.

Short answer? I'd go with "will treat the second record as an update within that session".

[JPalatianos]

I opened a Ticket with IBM and here is the response I received...not what I expected:


"Good morning John,

I pinged one of our DB2 Connector engineers to answer your question:

When one batch has duplicate keys there are two scenarios:

1) If the table does not have the key the update will fail for both rows and insert will try to insert both eventually only succeeding on the first one and failing on the second.

2) If the table has the key, the updates will succeed, and the insert would not be done (for that key)

To make #1 work, you must eliminate duplicates in the data or run with Array size=1 (bad performance) "

[chulett]

Interesting. Simple enough to test should you feel the need / desire.

I also wonder how much (if any) of this answer is specific to DB2 and/or the Connector architecture.

[JPalatianos]

Thanks Craig!! I tested it out and it does behave the way IBM described.