topic Re: How to terminate a sas sessions created by pre assign library? in SAS Programming

How to terminate a sas sessions created by pre assign library?

SSS20 — Fri, 05 May 2017 09:34:49 GMT

Hi there,

I have a problem when the services are started, connections to the oracle are created by the sessions started by the PooledWorkspaceServer and the StoredProcessServer. However when we stop these services the sessions created continue in Oracle data base, in this way, the bd is suffering problems to reach the maximum limit of connections that are configured in the db.

These ORACLE libraries are preassigned in Metadata in such a way that all SAS sessions started by the sas.servers services, in particular the Object Spawner, which starts SAS sessions from the stored process or pooled workspace session, and the OLAPServer connections to the Bb, for having pre-allocated libraries.

My question is: Is there any way to end these sessions when we stop services?

Thanks in advance.

Re: How to terminate a sas sessions created by pre assign library?

LinusH — Fri, 05 May 2017 13:21:53 GMT

It seems that you can specify SAS code to execute at "system boundaries", start and stop of the server.

Perhaps you could do a libname _all_ clear; there? (untested)

http://support.sas.com/documentation/cdl/en/biasag/63854/HTML/default/viewer.htm#n11015intelplatform00srvradm.htm

Re: How to terminate a sas sessions created by pre assign library?

Patrick — Sat, 06 May 2017 11:54:03 GMT

@SSS20

On top of what @LinusH suggests I'm normally using "defer=yes" for all pre-assigned libraries so a process only establishes a connection if it's required.

DEFER= LIBNAME Option

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p1aukl464makuln1mszw8l045xgy.htm

Re: How to terminate a sas sessions created by pre assign library?

SSS20 — Thu, 11 May 2017 08:36:15 GMT

Hi @LinusHa and Patrick,

Many thanks for your prompt response, it looks like this will work, but I still have the following doubt:

about DEFER Option, on sas support reference

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p1aukl464makuln1mszw8l045xgy.htm

it’s specified that option has to be used on the libname statement and on

http://support.sas.com/documentation/cdl/en/biasag/63854/HTML/default/viewer.htm#n11015intelplatform00srvradm.htm

It’s specified that we can add code, so on that code should appear all ORACLE libname statements with DEFER Option, but would those ORACLE libname overwrite, libnames defined on Metadata that are not defined by SAS/BASE code?.

Can you help me in more this one doubt?

Thanks a lot for your availability!!!

Re: How to terminate a sas sessions created by pre assign library?

Patrick — Thu, 11 May 2017 11:29:01 GMT

@SSS20

DEFER is a libname option which you define as part of a libname definition. You can define any library via SAS Metadata or directly in SAS code.

The second link you've posted has nothing to do with this. That's about executing code during SAS session invocation or SAS session termination.

Example for a metadata library definition with the ODBC engine that is pre-assigned and with defer=yes:

Re: How to terminate a sas sessions created by pre assign library?

SSS20 — Thu, 18 May 2017 11:35:19 GMT

Hello Guys,

Thank you a lot for your availability to help me with this question!!!

Kind Regards,