BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I would like to query the value of existing Libname Statements parameters.

for example : this Libname definition :
libname mydblib oracle user=xxxx pass=xxxxx
connection=xxxxxxx dblibterm=xxxxxx;


Is this possible to query the value of
connection = ?
dblibterm = ?
user = ?
.....


Any function or metatable exist to query by libname ?

The "libname mydblib list ; " - not write this kind of information.

Thanks,
Imre
7 REPLIES 7
deleted_user
Not applicable
have a look at &sysdbmsg
straight after the libname connection is made

It may reveal some info.

PeterC Message was edited by: Peter_c
deleted_user
Not applicable
Peter_c - Thanks your answer,

From the documentation - this macro variable contains only the error messages:
SYSDBMSG
contains DBMS-specific error messages that are generated when you use
SAS/ACCESS software to access your DBMS data.

it is not solve my problem. 😞
And this macro is empty - because no ERROR .

---------------
For example I have a libname definition like this:
Libname O_WORK ORACLE user="&actualuser"
password ="&defaultpassword"
path ="TESTU"
schema =&actualuser.
dbindex =YES
read_lock_type=nolock
readbuff =5000
insertbuff=2000
;

I would like to write a macro for example ..
%get_lib_param(O_WORK,dbindex); --> return: YES
%get_lib_param(O_WORK,readbuff); --> return: 5000
%get_lib_param(O_WORK,read_lock_type); --> return: nolock

is this possible? Any function/meta table - to query this values ?


The Libname .. LIST - only write this:
libname O_WORK LIST;
NOTE: Libref= O_WORK
Scope= Program
Engine= ORACLE
Physical Name= TESTU
Schema/User= codwadm


thanks in advance,
Imre
deleted_user
Not applicable
since almost all installations are different, yours will be unique too, but this feature is documented as holding more than just messages about errors.

Extracted from SAS Access to DB/2
"PROMPT=<’> CLI-connection-string<’>
specifies connection information for your data source or database for PCs only. Separate multiple options with a semicolon. When a successful connection is made, the complete connection string is returned in the SYSDBMSG macro variable."

so I thought you might consider it worth the trouble to try.

Merry Christmas


PeterC
deleted_user
Not applicable
Hi PeterC !

Thanks for Your Answer ...
Sorry , I have not expressed myself precisely. 😞 ..
and Merry Cristmas for You 🙂

I'll go ahead and clarify for everyone else out there:

I have lot of libname definition
Libname O_WORK ..... ;
Libname O_META ..... ;
Libname O_SYS ..... ;

I would like query selected parameters from libname definition
( except - password 🙂 )
for example:
%get_lib_param(O_WORK,dbindex); --> return: YES
%get_lib_param(O_META,readbuff); --> return: 5000
%get_lib_param(O_SYS,read_lock_type); --> return: nolock
%get_lib_param(O_SYS,shema );

( system: HP-UX , ver: SAS9.1.3 , Access2Oracle )

Thanks in advance,
Imre
ChrisHemedinger
Community Manager
Some information is available via the DICTIONARY tables. For example:

proc sql;
create table work.info as select *
from dictionary.libnames
where libname = upcase("sashelp");
quit;

But not the level of DBMS-specific detail that you are asking for. Even LIBNAME LIST doesn't always give you what you would want.

Chris
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
deleted_user
Not applicable
I don't have "Access to Oracle" here or in my office, but I have worked with Access to ODBC here.

Stepping through the standard Dictionary views gives you some data, but was a little short of what was needed for consistency of ODBC installations. I found that using Windows APIs it was possible to query and surface definitions from the ODBC administrator that could be matched by ODBC definition name to the library data.

In fact, there was sufficient data to create new ODBC definitions where a client was malconfigured. I'm not sure if this would be enough to start me on getting the Oracle definition since it appears that ODBC holds the definition locally on the client, while Oracle passes it off to the Oracle server.

Kind regards

David
deleted_user
Not applicable
Hi Chris,

Yes, dictionary.libnames - contains very minimal info ...
probably this is impossible to get this kind of information.
thanks, for your answer
Imre



proc sql;
create table work.info as select *
from dictionary.libnames
where libname = upcase("stage");
quit;
data _null_ ;
set work.info ;
put (_all_) (= /);
run;

libname=STAGE
engine=ORACLE
path=TESTU
level=0
fileformat=
readonly=no
sequential=no
sysdesc=ORACLE ACCESS ENGINE LIB INFO
sysname=Schema/User
sysvalue=stage

NOTE: There were 1 observations read from the data set WORK.INFO.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

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

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 7 replies
  • 880 views
  • 0 likes
  • 2 in conversation