Desktop productivity for business analysts and programmers

How to retrieve library names from EBI platform

Accepted Solution Solved
Reply
Respected Advisor
Posts: 3,156
Accepted Solution

How to retrieve library names from EBI platform

We manage an EBI platform including 6 app servers and supporting more than 100 SAS users. As of right now we have more than 300 libraries and counting. Some of them are built and managed directly by Admin team, the rest are built by users but managed by Admin team. The way we update those libraries is to run Proc Metalib in EG:

proc metalib;

      omr(library=”library names could be any string”);

      update_rule=(delete);

       report;

    run;

Here is screen shot of sample libraries:

metalibrary.PNG


  

Now my question: is there a way or place I can pull up all those library names (or repository id for that matter) programmatically? So that this part :library=”library names could be any string” can be Macro-variable-nized and Macro automation could be implemented. Right now we have to manually input those library names one for each Proc Metalib, and it is getting more and more difficult  to keep up. I am aware that there is a table named “wlibrary” located at metadata server, “C:\SAS\Config\Lev1\SASMeta\MetadataServer\MetadataRepositories\Foundation”, However, I know better not to mess around it (you need to stop metadata server or unlock it before you can access to those tables).

Any suggestions and comments would be highly appreciated!

Haikuo


Accepted Solutions
Solution
‎01-03-2014 01:52 PM
Super Contributor
Posts: 578

Re: How to retrieve library names from EBI platform

works for our instance:

namelibrefenginepathmdschemanameschema
MDMUP07MDMUP07ORACLEMDMUP07MDM
RevLib3REVLIB3BASE//sasuser/MichaelC/MCLib/RevLib3
StarLMKSTARLMKBASE//sasuser/MichaelC/MCLib/StarLMK
CubelibCUBELIBBASE//sasuser/Biju/Cubelib
ChnGrwCHNGRWBASE//sasuser/MichaelC/MCLib/ChnGrw
RRRLibRRRLIBBASE//sasuser/MichaelC/MCLib/RRRLib
TMDMTMDMORACLETMDMMDM
RevLib2REVLIB2BASE//sasuser/MichaelC/MCLib/RevLib2
SASApp1 - SASDATASASDATABASEData
RCLIBRCLIBBASE//sasuser/Rachel
CardAsoCARDASOBASE//sasuser/MichaelC/MCLib/CardAso
RevLibREVLIBBASE//sasuser/MichaelC/MCLib/RevLib
CCLIBCCLIBBASE//sasuser/MichaelC/MCLib/CCLib
VCCLibVCCLIBBASE//sasuser/MichaelC/MCLib/VCCLib
CORP_RPTCORP_RPTSQLSVRCORP_RPTDBO

scraped from sas support:

options metaserver="metadataserver"

    metaport=8561

    metauser="admin user"

    metapass="admin pw"

    metarepository="Foundation";

data metadata_libraries;

    length

        liburi upasnuri $256

        name $128

        type id $17

        libref engine $8

        path mdschemaname schema $256;

    keep

        name

        libref

        engine

        path

        mdschemaname

        schema;

  call missing(liburi,upasnuri,name,engine,libref);

  nlibobj=1;

  librc=metadata_getnobj("omsobjSmiley FrustratedASLibrary?@Id contains '.'",nlibobj,liburi);

  do while (librc>0);

     /* Get Library attributes */

         rc=metadata_getattr(liburi,'Name',name);

         rc=metadata_getattr(liburi,'Engine',engine);

         rc=metadata_getattr(liburi,'Libref',libref);

   

      /* The METADATA_GETNASN function specifies to get objects associated to the

library via the UsingPackages association. The n argument specifies to return the

first associated object for that association type. upasnuri is an output variable.

It will store the URI of the associated metadata object, if one is found.  */

        n=1;

        uprc=metadata_getnasn(liburi,'UsingPackages',n,upasnuri);

        /* When a UsingPackages association is found, the METADATA_RESOLVE function

is called to resolve the URI to an object on the metadata server. The CALL MISSING

routine assigns missing values to output variables.  */

        if uprc > 0 then do;

           call missing(type,id,path,mdschemaname,schema);

           rc=metadata_resolve(upasnuri,type,id);

           /* If type='Directory', the METADATA_GETATTR function is used to get its

path and output the record */

           if type='Directory' then do;

              rc=metadata_getattr(upasnuri,'DirectoryName',path);

              output;

              end;

           /* If type='DatabaseSchema', the METADATA_GETATTR function is used to get

the name and schema, and output the record */

           else if type='DatabaseSchema' then do;

               rc=metadata_getattr(upasnuri,'Name',mdschemaname);

               rc=metadata_getattr(upasnuri,'SchemaName',schema);

              output;

              end;

        /* Check to see if there are any more Directory objects */

            n+1;

            uprc=metadata_getnasn(liburi,'UsingPackages',n,upasnuri);

          end; /* if uprc > 0 */

     /* Look for another library */

     nlibobj+1;

     librc=metadata_getnobj("omsobjSmiley FrustratedASLibrary?@Id contains '.'",nlibobj,liburi);

  end; /* do while (librc>0) */

run;

/* Print the metadata_libraries data set */

proc print data=metadata_libraries; run;

View solution in original post


All Replies
PROC Star
Posts: 1,143

Re: How to retrieve library names from EBI platform

Wow! Interesting question.

When you find the answer, please post, as I'd love to see it.

Try shooting a reference to this off to the Metacoda gang...they know this stuff inside out.

I'll send it off to a friend who's really good with this stuff also.

Good luck!

  Tom

Respected Advisor
Posts: 3,156

Re: How to retrieve library names from EBI platform

Thanks, Tom! That is the spirit I like.

Haikuo

Respected Advisor
Posts: 3,156

Re: How to retrieve library names from EBI platform

Tom,

here is something back from SAS tech support, in case you are interested:

Hi Haikuo,

I have attached a program, get_all_LibrariesAndServerContext.sas, which queries the metadata server for all SASLibrary objects. It returns a SAS data set containing the Library Name and the Server Context(s) to which the library is associated, as well as other data.

You can use this data set as input to a macro to generate PROC METALIB code.

I hope this is helpful. If you have follow-up questions, or need clarification, please respond to this email.

Regards,

Attachment
Super Contributor
Posts: 578

Re: How to retrieve library names from EBI platform

is it as simple as

proc sql;

select * from sashelp.vslib;

quit;

Respected Advisor
Posts: 3,156

Re: How to retrieve library names from EBI platform

Thanks, David. I sure wish it were that simple Smiley Happy.

There are some issues remained running your code:

1. Only gets "Assigned" libraries.

2. Only gets Librefs(8 char), not library names per EBI system

3. Only gets those Librefs assigned to certain App servers.

If we have assigned all the libraries first (put aside performance overhead on the servers), then we would have already known the library names. 

Haikuo

Solution
‎01-03-2014 01:52 PM
Super Contributor
Posts: 578

Re: How to retrieve library names from EBI platform

works for our instance:

namelibrefenginepathmdschemanameschema
MDMUP07MDMUP07ORACLEMDMUP07MDM
RevLib3REVLIB3BASE//sasuser/MichaelC/MCLib/RevLib3
StarLMKSTARLMKBASE//sasuser/MichaelC/MCLib/StarLMK
CubelibCUBELIBBASE//sasuser/Biju/Cubelib
ChnGrwCHNGRWBASE//sasuser/MichaelC/MCLib/ChnGrw
RRRLibRRRLIBBASE//sasuser/MichaelC/MCLib/RRRLib
TMDMTMDMORACLETMDMMDM
RevLib2REVLIB2BASE//sasuser/MichaelC/MCLib/RevLib2
SASApp1 - SASDATASASDATABASEData
RCLIBRCLIBBASE//sasuser/Rachel
CardAsoCARDASOBASE//sasuser/MichaelC/MCLib/CardAso
RevLibREVLIBBASE//sasuser/MichaelC/MCLib/RevLib
CCLIBCCLIBBASE//sasuser/MichaelC/MCLib/CCLib
VCCLibVCCLIBBASE//sasuser/MichaelC/MCLib/VCCLib
CORP_RPTCORP_RPTSQLSVRCORP_RPTDBO

scraped from sas support:

options metaserver="metadataserver"

    metaport=8561

    metauser="admin user"

    metapass="admin pw"

    metarepository="Foundation";

data metadata_libraries;

    length

        liburi upasnuri $256

        name $128

        type id $17

        libref engine $8

        path mdschemaname schema $256;

    keep

        name

        libref

        engine

        path

        mdschemaname

        schema;

  call missing(liburi,upasnuri,name,engine,libref);

  nlibobj=1;

  librc=metadata_getnobj("omsobjSmiley FrustratedASLibrary?@Id contains '.'",nlibobj,liburi);

  do while (librc>0);

     /* Get Library attributes */

         rc=metadata_getattr(liburi,'Name',name);

         rc=metadata_getattr(liburi,'Engine',engine);

         rc=metadata_getattr(liburi,'Libref',libref);

   

      /* The METADATA_GETNASN function specifies to get objects associated to the

library via the UsingPackages association. The n argument specifies to return the

first associated object for that association type. upasnuri is an output variable.

It will store the URI of the associated metadata object, if one is found.  */

        n=1;

        uprc=metadata_getnasn(liburi,'UsingPackages',n,upasnuri);

        /* When a UsingPackages association is found, the METADATA_RESOLVE function

is called to resolve the URI to an object on the metadata server. The CALL MISSING

routine assigns missing values to output variables.  */

        if uprc > 0 then do;

           call missing(type,id,path,mdschemaname,schema);

           rc=metadata_resolve(upasnuri,type,id);

           /* If type='Directory', the METADATA_GETATTR function is used to get its

path and output the record */

           if type='Directory' then do;

              rc=metadata_getattr(upasnuri,'DirectoryName',path);

              output;

              end;

           /* If type='DatabaseSchema', the METADATA_GETATTR function is used to get

the name and schema, and output the record */

           else if type='DatabaseSchema' then do;

               rc=metadata_getattr(upasnuri,'Name',mdschemaname);

               rc=metadata_getattr(upasnuri,'SchemaName',schema);

              output;

              end;

        /* Check to see if there are any more Directory objects */

            n+1;

            uprc=metadata_getnasn(liburi,'UsingPackages',n,upasnuri);

          end; /* if uprc > 0 */

     /* Look for another library */

     nlibobj+1;

     librc=metadata_getnobj("omsobjSmiley FrustratedASLibrary?@Id contains '.'",nlibobj,liburi);

  end; /* do while (librc>0) */

run;

/* Print the metadata_libraries data set */

proc print data=metadata_libraries; run;

Respected Advisor
Posts: 3,156

Re: How to retrieve library names from EBI platform

Nice. I am not very familiar with those metadata functions, but this looks overwhelmingly promising to me. Will report back and mark correct answer after I try it out.

Thanks again, David

Haikuo

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 519 views
  • 0 likes
  • 3 in conversation