Re: Possible SQL Activation Group Issues

Dave,

I know that RPG's cycle file open code doesn't re-resolve the name.

So if you had RPG code that
open myfile;
<do something>;
close myfile;
<change the LIBL>
open myfile;

The second opening of myfile would open the same as the first, even if
you'd change the library list so that you're expecting it to be opened from
a different library.

I wonder if there's something in the SQL code that works similar...

If you ran the RPG code in a *NEW activation group, you wouldn't have to
reclaim it manually and it should take care of the issue.

Charles





On Fri, Oct 14, 2016 at 11:52 AM, <dlclark@xxxxxxxxxxxxxxxx> wrote:

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
--
int.ext: 91078
direct: (937) 531-6378
home: (937) 751-3300

Winsupply Group Services
3110 Kettering Boulevard
Dayton, Ohio 45439 USA
(937) 294-5331



************************************************************
*********************************
This email message and any attachments is for use only by the named
addressee(s) and may contain confidential, privileged and/or proprietary
information. If you have received this message in error, please
immediately notify the sender and delete and destroy the message and all
copies. All unauthorized direct or indirect use or disclosure of this
message is strictly prohibited. No right to confidentiality or privilege
is waived or lost by any error in transmission.
************************************************************
*********************************
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.