Possible SQL Activation Group Issues

First note that our environment is such that we have upwards of
600 different data libraries with the same data file/table/view names in
them where each data library represents a different "company." Thus, we
can run the same program using different job description-based initial
library lists and achieve processing for a single "company." We can also
run a CL which loops through a master company list and dynamically changes
the library list before calling an RPG program to run for that particular
company. This prevents having to have 600 jobs to do what a single job
can also do.

We also hired IBM for SQL training and consulting and among other
things specifically discussed this environment issue with them -- because
of the issue of soft-closing open data paths so that they can be reused.
The advice from IBM was that we would have to (1) use dynamic SQL, (2)
make sure everything runs in the same activation group (whether that is
QILE or our own named activation group), and (3) use NAMING = *SYS, and
CLOSQLCSR = *ENDACTGRP in our SQL programs. Note that we went with our
own named activation group.

That said... I have a new process I've created for my current
project that loops through an entire data view while processing every
inventory item (looking for item exceptions) and which can either be run
for a single "company" or it can be run across all companies but, as
described, the CL treats each company as a "separate" run (meaning, the
library list is manipulated between companies and the associated RPG
program is called only once for each company).

When I run this process for a single company it always works
successfully. I have now been trying to run it across all companies and I
am getting sporadic failures (meaning, in different companies on each
attempt) for duplicate key issues for the item exception table that I
maintain as part of this process. However, these duplicate keys are not
logically possible because at the beginning of processing each inventory
item I do a mass delete of any previous exceptions for that one item.
Hence, it seems that SQL is sometimes reusing an open data path for a
previous company when doing the delete and thus the exceptions for the
current company are not getting deleted -- which then causes a duplicate
key when trying to reinsert the same exception for that same item.

Suggestions on how to prove this is what is happening? ...or what
else I should be doing? ...or whether I should try to work around this
problem by forcing library (schema) names into all my SQL statements or by
forcing an end of the activation group between "companies"?

Sincerely,

Dave Clark