Re: multi-tenant issues with stored procedures

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

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

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".

​Dave,

So the RPG program/procedure used as an external stored proc is calling

a

process to change the library list?

IBM supplies a stored procedure definition called QCMDEXC which
simply invokes QCMDEXC. Thus, we can call QCMDEXC from an SQL stored
procedure as a front-end to any subsequent processes that need to be
executed after the library list has been changed. For example:

Declare Str1 VarChar(5000);
Declare StrLen Decimal(15,5);

Set Str1 = 'CALL UTSETLLCL (''' || CompNumb || ''' *FIRST)';
Set StrLen = Length(Str1);
Call QCMDEXC (Str1, StrLen);

Are you using programs, procedures​, or both as external stored procs?

We write both SQL as well as external stored procedures. Our
external stored procedures only invoke either CL or RPG programs. It
appears there is an issue with trying to use service programs from stored
procedures as is seems you have to hard-code the library for the service
program. We can't do that as it would mess with our ability to have
different versions of the same service program in the library list. If
this is not so then someone can correct me on that.

Does your library list change only change the data librar? (So every
tenant runs the same version of programs/procedures?)

Our library list affects both data libraries as well as program
libraries. When we initially promote a program to pilot, the pilot
library gets added to the beginning of the library list for those
"tenants" that will be piloting the new version. When a program goes full
production it goes into the production staging library which is ahead of
the production master library and the pilot library gets removed. Ninety
days later, the program moves from the staging library to the master
library. Our "tenant" switching process insures that all this works as
desired for multi-versioning.

Didn't you have an issue with data being returned incorrectly from an
existing ODP that you worked around by moving to dynamic SQL?

IBM told us that we should only use static SQL in the case that
there is only one file object on the system by a given name. They then
said that we should use dynamic SQL when there is more than one file
object with the same name -- the difference being in which library the
object resides. They said with our library list switching that dynamic
SQL will figure out that an ODP is for a library that is not in the
current libary list and will therefore ignore that ODP and create another.
I have no hard proof that this works -- but that is what IBM told us. ;-)


Sincerely,

Dave Clark