Re: Embedded Exec SQL versus SQL-CLI Performance (Link?)

[Reeve <rfritchman@xxxxxxxxx>]

An easy solution to the multi-tenant problem is to duplicate the object
library, or to have a library with critical SQL programs duplicated from
the base library.  There would be a slight impact on program maintenance
(change a program and have to distribute it to multiple libraries) and
possibly storage usage (multiple copies of the same program).  I don't like
duplicated code, source or object, but this approach seems like a
reasonable tradeoff.

I'm surprised to learn access plans are stored with the program...but then
one high-use table could end of with thousands of access paths tied to it
and that would make the table object very large.  Perhaps an "access plan
object" is a solution.

This is not a welcome revelation.

On Mon, Aug 2, 2021 at 8:02 AM D*B <dieter.bender@xxxxxxxxxxxx> wrote:

> <Alan>
> And if we do what you are suggesting that means fixing virtually every
> program in the system to not set the library list and we have mix of RLA
> and SQL. Thousands of programs.
> </Alan>
>
> very most of all programms would stay untouched.
>
> - The new table with all records is moved to another lib for all clients
> and
> not used dirctly by any programm.
> - your RLA programms are accessing the data by DDS logicals (or access to
> the PF)
> - just replace the existing DDS LFs by new ones, based on the new table
> with
> select omit specs, these are looking like the old ones for the existing
> programms and all read access would work as before.
> - the SQL programms today are using the PF or some views or LFs, wich could
> be recreated, based on the new table, lokking and working as today, for all
> read access.
> Today the client table is searched by libl, after the changes they would
> use
> a View(or dds LF), searched by libl as before, redirected to the new table
> by the view/LF.
>
> The only remaining problem would be to provide the client key for the
> inserts, but this would only touch very few programms.
>
> D*B
>
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com