Re: IBM i in the cloud (was social media)

[Paul Holm <pholm@xxxxxxxxxxxxxxxxx>]

Nathan Andelin wrote:
> > > Would you create an intelligent connection pool manager to
> > > determine which tenant each request belongs to?
>
> > That would make a lot of sense.  I don't think the default JDBC
> > connection pool in JT400 can handle that kind of thing, but it shouldn't
> > be too hard to create your own.
>
> Could we explore that idea a little further?  And just to reiterate a little bit 
> of background to keep things in context, we're talking about using a single IBM 
> i box to handle requests for multiple tenants who each have their own separate 
> database collection (library).  So we're exploring the idea of writing a 
> connection pool manager to return a connection to a DB server instance which 
> would use the appropriate library list for the requesting tenant.
>
> What scope would you recommend for the connection manager?  Scoped to a single 
> servlet?  Scoped to a package of Servlets?  Scoped to a WAS instance?
>
> -Nathan
>
>
>
>       
Nathan,

FWIW,
We developed a cross platform solution to this issue as follows:
1. Each tenant is associated with a library(s) (or schema on other DBs) 
as part of a database driven authentication.  Library is stored with 
user session after authentication.

2.  All SQL statements include a variable replacement library.  For 
example:  "SELECT  A,B,C  from  ??!LIB_NAME/customers where x > y"
3.  All SQL statements pass thru a single component that can replace the 
library with a REAL tenant library (along with other security checks, 
etc)  At runtime, this becomes: "SELECT  A,B,C  from  ACME/customers 
where x > y"

This works for us on DB2, Oracle, SQL SERVER, and MYSQL since library 
list is a AS400 concept.
-Paul Holm