Re: Possible SQL Activation Group Issues

On 14-Oct-2016 13:10 -0500, dlclark wrote:

On 14-Oct-2016 13:03 -0500, CRPence wrote:

On 14-Oct-2016 12:47 -0500, dlclark wrote:

On 14-Oct-2016 12:25 -0500, CRPence wrote:

The duplicate key exception logged by the database feature
[i.e. to be clear, separate from the effective equivalent SQL
/message/ sqlcode in response to the DB messaging] states what
file; just review that prior message for the message data that
would conclusively reveal that the wrong file was referenced
with an implication almost surely an existing but incorrect
query Open Data Path (ODP) was accessed.

Insufficient. Because, the message only indicates the file/table
name. It does not indicate the library. The file/table name
shown is as expected but this same name exists in every data
library.

Then that would most likely not be the message to which I refer.
What message? Maybe I can suggest a means to /correct/ that.

Can you produce an example of DDL and a program doing [apparently
an INSERT?] that forces the duplicate-key problem; to be sure,
that need not be a re-create of the actual presumed-to-be
wrong-ODP scenario, just the identical issue/messaging of
duplicate-key. Then I could look into how to get the info.

I wonder if perhaps the error is diagnosed for a CONSTRAINT,
PRIMARY or UNIQUE rather than an INDEX or unique keyed Logical
File? Likely that they all have the /same name/ across the
libraries, so their name alone is not helpful. That may be part of
the difference from what I am expecting, but still, with a
re-create I could investigate for possibilities.

Yes, it is a UNIQUE CONSTRAINT that is being diagnosed.

----------------------------------------
-- Create Table and Columns <<SNIP>>

My test inserting a duplicate key, in violation of the UNIQUE CONSTRAINT [from the given <snipped> DDL], showed the expected msg CPF5009 "Duplicate record key in member IMLFXRLT." in which the second-level text includes the library name, the msg CPF5034 "Duplicate key on access path." [both notify and sender copies] in which the second-level text includes the library name, and most notably, the msg SQL0803 [sqlcode -803] "Duplicate key value specified." in which the second-level text includes the library name; oddly, the name of the constraint and the constraint library did not appear for my test on IBM i 7.3. Thus for the latter, implied is that the GET DIAGNOSTICS [or similar work] has available, for retrieval, that same library name; though I expect the messages and thus the second-level text would be visible in the [spooled] joblog of the job in which the problem occurs, given LOG(4 0 *SECLVL) filtering is in effect [for spooling], as evidence that the insert was performed against a file in a library other than what was expected per the established/changed library list.