Connection pooling to external database in sas 9.4

[thesasuser]

I have a scenario where while connecting to an external databases (for example) every user would create a connection to the database using his own libname statement. This create one connection for every user.
I was wondering if Connection Pooling  is a feasible option.
I was wondering if we could have a pool of database connections created may be in the metadata server. Whenever a user process needs to connect to an database, it takes a connection form the pool and then it is returned after use.
No new connection for every user.
Wondering if creating a library in the metadata server server serves this purpose?

[Tom]

No idea.  Did you try it?

Do you have SAS/Share licensed? Perhaps you could setup a SAS/Share server to make the database connection and share the library to other SAS sessions.

[SASKiwi]

The way most users interact with SAS 9.4 is to start a SAS Workspace Server session dedicated to each user. You can't pool database connections across each user's sessions. To be able to pool external database connections between users you would need a shared SAS server that runs continuously and as @Tom  has mentioned SAS/SHARE is probably your only option, plus use the options CONNECTION = GLOBAL on your LIBNAME statements. That obviously means you would need to both install and license it. IMHO that seems like overkill just to support connection pooling. 

[Patrick]

My first question would be: Why? What problem are you trying to solve?
As @SASKiwi already explained SAS sessions are user specific and so are the connection to the DB.

connection=global can certainly help that at least on user level a connection gets reused and not for every query a new one gets created. And for this reason pre-defining the library in SAS metadata with connection=global set would help that users use a libname definition with this option set.

The one scenario where multiple users could use the same connection would be stored processes that use pooled workspaces. I believe that if these workspace instances get started with a pre-assigned library that got connection=global then subsequent requests by any user would use the same db connection. Not 100% sure though and certainly something you would have to test - plus it only applies to use case for stp. 

... and about SAS/Share: imho rather outdated technology. I wouldn't go down this path. And afaik SAS/Share no longer exists under SAS Viya.

[thesasuser]

Thanks @Tom , @SASKiwi  and @Patrick . for the quick response.
I have a question about SAS/SHARE. I have the impression that it can provide shared connection to SAS Datasets. Does it work with RDBMS say for example Oracle, MS SQL Server etc.?

[AhmedAl_Attar]

@thesasuser 

SAS/SHARE was made specifically for SAS data sets! Your RDBMS has concurrent read/write feature out of  the box, and you don't need any external tool/software for that. All you need to grant your users the Read, Write, Update, Delete  privileges.  Discuss this with your Database Admin, he/she should know how to do that.

Hope this helps 

[SASKiwi]

@thesasuser - Why the need to share external database connections as @Patrick has already asked? Personally I've never encountered any recent issues with every SAS user having their own connections, especially with the high performance of modern networks.

[Sajid01 · Accepted solution]

Hello
Connection pooling is something I saw in the java world.
Creating library definitions in the metadata server and pre-assigning would serve a similar purpose, though they are not the same.