Re: DataPropagator question (was Re: What's "QZSNAPV2")

On 20-May-2016 12:43 -0500, James H. H. Lampert wrote:

On 20-May-2016 11:53 -0500, CRPence wrote:

On 20-May-2016 10:48 -0500, James H. H. Lampert wrote:

<<SNIP>> Assuming a customer number reassignment happens on one
of the other boxes, and is propagated in with DataPropagator,
could it happen without tripping that code?

Yes. As I recall, changes to the keys are implemented as
DELETE+INSERT. An UPDATE TRIGGER would not be invoked on the
system where the changes are propagated\applied.

Interesting. And dismaying. I'll note that the physical file isn't
keyed at all; rather, one of the logicals is keyed on the customer
number.

IIRC, the registration [¿subscription?] for the file for change-capture and the file for the change-apply, defines some aspects regarding _how_ the data is replicated. Sorry, been so many years since I played with the feature, and that was very little, and certainly I never used any files with triggers defined; I learned more while consulting for the developers than from actual use.

OK, poked around a bit. The Add DPR Registration (ADDDPRREG) has the Update Using Delete/Insert (UPDDELINS) parameter; default is *NO. The Add DPR Subscription (ADDDPRSUB) has a related Target Key Change (TGTKEYCHG) parameter.

If the source table has a unique keyed logical, that is considered no different than the physical table having a PRIMARY KEY or UNIQUE KEY constraint; i.e. the PF need not be keyed to have the effect of the existence and enforcement of unique keys.

At any rate, if it's happening as a delete+insert (and I just got
independent confirmation), that explains how changes aren't being
processed when they're coming in via DataPropagator.

Unfortunately, that also means that we don't know the new customer
number at the time of the delete, and we don't know the old customer
number at the time of the insert, and so we don't know how to
propagate the change into our CRM product. We can take a good guess,
based on the customer name, but I don't like to depend on that, since
it, too, can change.

Or we could set something up to automatically alert a human being of
the change, so the human being could sort it out.

At this point, I'm open to suggestions, and grasping at straws.
Maybe have the trigger log deletions that come from DataPropagator
into a keyed PF, and then search that PF when an insertion comes
from DataPropagator?

Already outside my depth here :-( But I offer...

If UPDDELINS(*NO) on registration is not what can be used or is not functional for the desired effect, then perhaps add another file to be replicated. That other file could track the occurrences of the "customer number reassignment" processing, by having the update trigger populate that other file with the former and new value. An insert trigger alone could then look for and resolve the condition at the target.