Re: multi-tenant issues with stored procedures

I also recently attacked this problem completely differently.

Our company has environments. Thirteen different environments with their
own library list. What we wanted is for one environment to run in one job
only so you do not have the problem of opening and closing files and
switching environments which sounds like what you are trying to do.

My solution. I wrote a multi-threaded socket in C. Each connection opened
another thread. I have C# and Java code that connects to the socket server.
Each request coming in goes to a module that does scheduling.

If a job is running for that environment, it determines if the job is
active for that environment. If no, it starts one and adds to list. If one
is active, it checks to determine if it busy (processing), If yes, looks at
the next one. If nothing available, it starts a new one and to the list. In
other words, a scheduler.

The environment jobs receive work via socket messages, process the work and
send back messages with results.

Anyway, the whole idea is to process one environment(Tenent) in one job.
Trying to process multiple environments (tenents) in the same job is a
nightmare.

Contact me if you have further interest.

On Mon, Nov 27, 2017 at 2:43 PM, <dlclark@xxxxxxxxxxxxxxxx> wrote:

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 11/27/2017
05:22:46 PM:

However, I've been told that we've had problems in the past with

external

apps calling (external) stored procedures when the library list needs to
change between calls.

This is my first experience with multi-tenancy, but I know so of you on

the

list have the same architecture.


Key points, the library list change affects not just the data library

but

the program library also (as our customers may be on different versions
)

I think in the past, what we've tried is
my_sp ext stored proc for myrpgpgm

Where my_sp is defined in a global library and myrpgpgm is supposed to

be

called from a version specific library (using the library list)

I'm curious as to how others deal with multi-tenancy.

Also I suppose, I'd like to find out more about when external stored
procedures are resolved and more importantly re-resolved?

We deal with it by first making sure that the off-platform call is
made with Naming Convention *SYS in place. This insures that both the job
and SQL will honor the library list that is in place (for both files and
programs). To accomplish this the off-platform connection string must
contain 'naming=system' (sans quotes). If using an ODBC DSN, make sure
the Server tab displays "System naming convention (*SYS)" in the Naming
convention drop-down box.

For the second step, we have a method in place which allows us to
switch between "tenants" without having to log out and back in. We
extended this to our stored procedures by passing a "tenant" id as the
first parameter and call QCMDEXC to execute the same switching process
that would be used for an interactive session. What this switching
process does is to replace the current job's library list based on the
initial library list found in a job description that corresponds to the
"tenant" id. We use the QWDRJOBD API to extract the library list from the
job description and the QLICHGLL API to apply that library list to the
current job.

The only issue that might remain is files left open from the
previous "tenant".

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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD