topic Re: Connection pooling to external database in sas 9.4 in Administration and Deployment

Connection pooling to external database in sas 9.4

thesasuser — Tue, 20 May 2025 14:38:07 GMT

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?

Re: Connection pooling to external database in sas 9.4

Tom — Tue, 20 May 2025 20:57:21 GMT

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.

Re: Connection pooling to external database in sas 9.4

SASKiwi — Tue, 20 May 2025 21:40:21 GMT

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.

Re: Connection pooling to external database in sas 9.4

Patrick — Wed, 21 May 2025 09:15:13 GMT

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.

Re: Connection pooling to external database in sas 9.4

thesasuser — Wed, 21 May 2025 13:10:39 GMT

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

Re: Connection pooling to external database in sas 9.4

AhmedAl_Attar — Wed, 21 May 2025 13:15:23 GMT

@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

Re: Connection pooling to external database in sas 9.4

SASKiwi — Wed, 21 May 2025 20:08:13 GMT

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

Re: Connection pooling to external database in sas 9.4

Sajid01 — Wed, 04 Jun 2025 13:18:25 GMT

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.