BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
umct
Fluorite | Level 6

hello fellow SAS-users,

 

we are trying to "check" for an "active" ACCESS to oracle connection (which is set via libname at the start of the process).

The connection is working most of the times, but as the oracle instance is setup in a way that there is a limited number of "licenses" that have concurrent access to the database, we are running into some problems during heavy load times.

 

Is there an intelligent way to check for "active" connection to the oracle instance, besides trying to "load data" (data step, pass through) and checking for error codings? Is there some meta-information about the current status of the library/connection?

 

thanks in advance for any helpful insights!

1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
SAS Super FREQ

Run this bit of code to test:

/* Input the libref for the library you want to test here */

%let LibrefToTest=%upcase(ORION);
/* test if Libref is actually assigned */
%let LibrefAssigned=%eval(NOT(%sysfunc(libref(%superq(LibrefToTest)))));

/* test if Libref can access any tables */
proc sql noprint;
   select count(*)>0
   into: TablesAccessible trimmed
   from dictionary.tables
   where libname = "%superq(LibrefToTest)"
  ;
quit;

/* If both of these are 1, all is well. Otherwise, reconnect */
%put &=LibrefAssigned &=TablesAccessible ;

When the connection is good, both variable values will be 1 (true)

When the libref is still assigned, but no tables can be accessed, LIBREFASSIGNED=1 TABLESACCESSIBLE=0

 

Hope this helps.

Mark

Check out my Jedi SAS Tricks for SAS Users

View solution in original post

5 REPLIES 5
andreas_lds
Jade | Level 19

Haven't done this, yet, but using the options "connection" and "connection_group", see http://documentation.sas.com/?docsetId=acreldb&docsetTarget=p1qft7bzdij79zn1bxh59mc3w8xj.htm&docsetV... for details, seems to be a possibility to limit the number of connections by using the same group name.

umct
Fluorite | Level 6
actually, we are trying to live with the (externally given) limitations, not install those.
I will have a look at the proposed item, maybe there are also parts to check the current status/connection.
SASJedi
SAS Super FREQ

Run this bit of code to test:

/* Input the libref for the library you want to test here */

%let LibrefToTest=%upcase(ORION);
/* test if Libref is actually assigned */
%let LibrefAssigned=%eval(NOT(%sysfunc(libref(%superq(LibrefToTest)))));

/* test if Libref can access any tables */
proc sql noprint;
   select count(*)>0
   into: TablesAccessible trimmed
   from dictionary.tables
   where libname = "%superq(LibrefToTest)"
  ;
quit;

/* If both of these are 1, all is well. Otherwise, reconnect */
%put &=LibrefAssigned &=TablesAccessible ;

When the connection is good, both variable values will be 1 (true)

When the libref is still assigned, but no tables can be accessed, LIBREFASSIGNED=1 TABLESACCESSIBLE=0

 

Hope this helps.

Mark

Check out my Jedi SAS Tricks for SAS Users
umct
Fluorite | Level 6

Hi Mark,

 

thanks a lot, that was just the strategy I was looking for.

We will check for library assignment and the access via dictionary.tables!

 

Thanks for your quick help!

SASJedi
SAS Super FREQ
My pleasure - you are most welcome!
Mark
Check out my Jedi SAS Tricks for SAS Users

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2077 views
  • 2 likes
  • 3 in conversation