topic Re: Query ( Oracle / DB2 / ...) library definition ? in SAS Enterprise Guide

Query ( Oracle / DB2 / ...) library definition ?

deleted_user — Tue, 18 Dec 2007 15:42:58 GMT

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

Re: Query ( Oracle / DB2 / ...) library definition ?

deleted_user — Tue, 18 Dec 2007 16:38:07 GMT

have a look at &sysdbmsg
straight after the libname connection is made

It may reveal some info.

PeterC Message was edited by: Peter_c

Re: Query ( Oracle / DB2 / ...) library definition ?

deleted_user — Tue, 18 Dec 2007 17:00:18 GMT

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

Re: Query ( Oracle / DB2 / ...) library definition ?

deleted_user — Tue, 18 Dec 2007 17:10:26 GMT

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

Re: Query ( Oracle / DB2 / ...) library definition ?

deleted_user — Tue, 18 Dec 2007 18:25:44 GMT

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

Re: Query ( Oracle / DB2 / ...) library definition ?

ChrisHemedinger — Wed, 19 Dec 2007 21:55:05 GMT

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

Re: Query ( Oracle / DB2 / ...) library definition ?

deleted_user — Wed, 19 Dec 2007 22:23:28 GMT

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

Re: Query ( Oracle / DB2 / ...) library definition ?

deleted_user — Thu, 20 Dec 2007 11:12:16 GMT

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.