Extracting database library information from metadata

Attention : This code requires administrator privileges.

The program connects to a SAS^© 9 metadata server to identify all database-type libraries (`IsDBMSLibname = '1'`). For each library found, it retrieves associated information such as the library name, default login user, application server, schema, database server name, data source, and authentication domain. The results are stored in a `work.libinfo` table. This code is specific to SAS^© 9 and is not compatible with SAS^© Viya^™ because it uses `metadata_*` functions that interact with the old metadata server.

Data Analysis

Type : CREATION_INTERNE

Data is dynamically generated by querying the SAS server metadata using specific functions `metadata_resolve`, `metadata_getnobj`, `metadata_getattr`, and `metadata_getnasn`. The script does not read any external data or from SASHELP.

1 Code Block

OPTIONS

Explanation :
This block configures the global SAS session options to establish a connection to a SAS 9 metadata server. It specifies the server address, port, credentials, repository name, and connection protocol. These parameters are essential for using the `metadata_*` functions in the following steps.

Copied!

1	options
2	metaserver="<hostname>"
3	metaport=8561
4	metauser="sasadm @saspw"
5	metapass="<password>"
6	metarepository=Foundation
7	metaprotocol=bridge;

2 Code Block

DATA STEP Data

Explanation :
This DATA STEP creates the `work.libinfo` table. It begins by searching SAS metadata for all DBMS-type libraries via `metadata_resolve`. Then, it iterates over each found library to extract specific attributes (library name, user, schema, database server, etc.) using a series of `metadata_get...` functions. The information collected for each library is added as a new row to the output table.

Copied!

1	DATA work.libinfo;
2
3	/declare and initialize variables /
4	LENGTH
5	type user schema $ 20
6	lib_uri lib_name app_uri app_name schema_uri login_uri dom_uri
7	dbms_uri dbms_name conn_uri prop_uri datasrc authdomain $ 50
8	id $ 17;
9	keep lib_name app_name user schema dbms_name datasrc authdomain;
10	call missing(of _character_);
11
12	obj="omsobj:SASLibrary? @IsDBMSLibname = '1'";
13
14	/* Search Metadata for libraries */
15
16	libcount=metadata_resolve(obj,type,id);
17	put "INFO: Found " libcount "database libraries.";
18	/* for each library found, extract name and associated properties */
19	/*
20	default login,
21	first associated application server,
22	schema,
23	database server
24	*/
25	IF libcount > 0 THEN DO n=1 to libcount;
26
27	rc=metadata_getnobj(obj,n,lib_uri);
28	rc=metadata_getattr(lib_uri,"Name",lib_name);
29	rc=metadata_getnasn(lib_uri,"DefaultLogin",1,login_uri);
30	rc=metadata_getattr(login_uri,"UserID",user);
31	rc=metadata_getnasn(lib_uri,"DeployedComponents",1,app_uri);
32	rc=metadata_getattr(app_uri,"Name",app_name);
33	rc=metadata_getnasn(lib_uri,"UsingPackages",1,schema_uri);
34	rc=metadata_getattr(schema_uri,"SchemaName",schema);
35	rc=metadata_getnasn(schema_uri,"DeployedComponents",1,dbms_uri);
36	rc=metadata_getattr(dbms_uri,"Name",dbms_name);
37	rc=metadata_getnasn(dbms_uri,"SourceConnections",1,conn_uri);
38	rc=metadata_getnasn(conn_uri,"Properties",1,prop_uri);
39	rc=metadata_getattr(prop_uri,"DefaultValue",datasrc);
40	rc=metadata_getnasn(conn_uri,"Domain",1,dom_uri);
41	rc=metadata_getattr(dom_uri,"Name",authdomain);
42	OUTPUT; /* Push results to table */
43
44	END;
45	ELSE put "INFO: No libraries to resolve.";
46	RUN;

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Retour à la liste