topic sum the size of members for each library in SPDserver environment in SAS Procedures

sum the size of members for each library in SPDserver environment

Ksharp — Wed, 08 Dec 2010 08:54:17 GMT

Hi.
Everyone!
I want to know the size of each library in SAS SPDServer environment.
Dictionary table 'tables' does not work for SPDServer,but work for Base.
So I want to know how to get the size of each library for SPDServer.
Can somebody give me some clue?

I am using UNIX command through SAS PIPE to achieve it.

Ksharp

Re: sum the size of members for each library in SPDserver environment

LinusH — Wed, 26 Jan 2011 10:27:40 GMT

One way to do it is to use the du command, together with the path information in the libnames.parm file.

If you can settle with less precise values, you could use the formulas in the FAQ section of the Users Guide.
Should work pretty well for uncompressed data.
If you have compressed data, you have to find out the compression ratio for each table, and add it to the formula.

/Linus

Re: sum the size of members for each library in SPDserver environment

Peter_C — Wed, 26 Jan 2011 11:42:38 GMT

du
is platform dependant
You might find the finfo() function offers a way of collecting the filesize in a non-"platform dependant" way. I have tested it with the spde engine[pre]
libname sp1 spde "%sysget(temp)\spdtemp" partsize=128;
data sp1.test1( index=(row)) ;[/pre]creating some data[pre] row+1;
set sashelp.shoes ;
run;[/pre]now access that folder and the physical files in it[pre]filename sp2 "%sysget(temp)\spdtemp" ;
data diropts ;
length optname Doptval Moptval $140 memname $255 ;
did=dopen("sp2");
numopts=doptnum(did);
memcount=dnum(did);
do i=1 to memcount ;
memname = dread( did, i ) ;
mid = mopen( did, memname ) ;
if not mid then continue ;
numopts = foptnum( mid ) ;
do j=1 to numopts ;
optname=foptname(mid,j);
Moptval=finfo(mid,optname);
output ;
end ;
end ;
run;[/pre]On my SAS9.2 windows vista platform, the option names/values are[pre]The SAS System 11:35 Saturday, January 22, 2011 1
optname Moptval

Filename C:\Users\PETERC~1\AppData\Local\Temp\spdtemp\test1.dpf.649166ba.0.1.spds9
RECFM V
LRECL 256
File Size (bytes) 37920
Last Modified 26 January 2011 10:51:01 o'clo
Create Time 26 January 2011 10:50:38 o'clo
Filename C:\Users\PETERC~1\AppData\Local\Temp\spdtemp\test1.hbxrow.649166ba.0.1.spds9
RECFM V
LRECL 256
File Size (bytes) 49152
Last Modified 26 January 2011 10:51:01 o'clo
Create Time 26 January 2011 10:50:38 o'clo
Filename C:\Users\PETERC~1\AppData\Local\Temp\spdtemp\test1.idxrow.649166ba.0.1.spds9
RECFM V
LRECL 256
File Size (bytes) 8192
Last Modified 26 January 2011 10:51:01 o'clo
Create Time 26 January 2011 10:50:38 o'clo
Filename C:\Users\PETERC~1\AppData\Local\Temp\spdtemp\test1.mdf.0.0.0.spds9
RECFM V
LRECL 256
File Size (bytes) 35544
Last Modified 26 January 2011 10:51:01 o'clo
Create Time 26 January 2011 10:50:38 o'clo

Re: sum the size of members for each library in SPDserver environment

Ksharp — Thu, 27 Jan 2011 01:31:23 GMT

Hi.
I am so surprised that my post will has some answer for the sake of a long time ago.
Linus.
Actually I do what you have said (i.e. filename + pipe + 'du &pathename').
I firstly distil the data path of each library from distionary table(i.e. dictionary.libnames) ,
then stack them into a marco variable ( &pathename) by proc sql ( separated by ' '), because command 'du' can support multi path name
, now the following thing is more simple to do.

Thanks your reply.

Ksharp

Re: sum the size of members for each library in SPDserver environment

Ksharp — Thu, 27 Jan 2011 01:44:40 GMT

Hi.
Peter.C
I think your code is right.
But we have hundreds of librarys ,It is hard for me to apply the code to the library separately.Fortunately I have solution just as I said in the last post.
I also talk to SAS Technology ,they gave me two methods, one of two is very useful which use SPDS API in data step to sum the size of each library.
And I post these ,hope to help somebody.
[pre]
/***
/* This program uses the SAS Open Metadata API data step functions to query
/* the metadata repository and return a list of all libraries and their
/* associated directory or database schema. The results are returned to a
/* SAS data set in the WORK library.
/*
/* Modify the code to generate the results in a form useful to you, such as
/* a simple listing via PROC PRINT.
/*
/* Modify the Meta* options to supply correct connection parameters to the
/* metadata server that contains the user information. The METAUSER should
/* be a user who has read permission to the metadata objects being queried,
/* such as the Unrestricted (e.g. sasadm) user.
***/

/*Connect to the metadata server*/
options metaserver="your.metadata.server"
metaport=8561
metauser="sasadm@saspw"
metapass="sasadmpw"
metarepository="Foundation"
metaprotocol=BRIDGE;

/* Begin query to metadata server for SAS Library objects */
data metadata_libraries;
length liburi upasnuri $256 name Description $128 type id $17 libref engine $8 path mdschemaname schema $256;
keep name libref engine path mdschemaname schema Description;
call missing(liburi,upasnuri,name,engine,libref,Description);

/* Get each Library object */
nlibobj=1;
librc=metadata_getnobj("omsobj:SASLibrary?@Id contains '.'",nlibobj,liburi);

/* For each library, retrieve the libref, engine, path */
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);
rc=metadata_getattr(liburi,'Desc',Description);

/* Get the Directory and DatabaseSchema object
associated with this library via a
UsingPackages association*/
n=1;
uprc=metadata_getnasn(liburi,'UsingPackages',n,upasnuri);
/* We found a UsingPackages association */
if uprc > 0 then do;
/* Determine object type */
call missing(type,id,path,mdschemaname,schema);
rc=metadata_resolve(upasnuri,type,id);
if type='Directory' then do;
/* Get the path and output the record */
rc=metadata_getattr(upasnuri,'DirectoryName',path);
output;
end; /*if type='Directory'*/
else if type='DatabaseSchema' then do;
/* Get the schema and output the record */
rc=metadata_getattr(upasnuri,'Name',mdschemaname);
rc=metadata_getattr(upasnuri,'SchemaName',schema);
output;
end; /*if type='DatabaseSchema'*/

/*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("omsobj:SASLibrary?@Id contains '.'",nlibobj,liburi);
end; /*do while (librc>0)*/
run;
proc print data=metadata_libraries;
var name description;
run;
[/pre]

Or another way ,but it is only suited for a single library.

[pre]
libname spdslib sasspds 'class' server=chloeyu.5400 user='chloe' password="xxxxxxx" aclspecial=yes;

proc spdo lib=spdslib ;
spdscmd 'spdsls -info -s -verbose D:\test\spds\meta';
quit;

58 proc spdo lib=spdslib ;
NOTE: Enter "?;" to get info on all the SPDO commands.
Enter "SPDSMAC;" to get list and setting of all SPD macros.
59 spdscmd 'spdsls -info -s -verbose D:\test\spds\meta ';
DPF_SIZE IDX_SIZE MDF_SIZE NUMOBS OBSLEN SEGSIZE PARTSIZE CMP ENC CLM TABLE
760 0 26780 19 40 8192 268435200 NO NO NO AA
8 0 26476 1 8 8192 33554432 NO NO NO NEW
260000000 0 26552 5000000 52 8192 33552896 NO NO NO PP60
quit;

NOTE: “PROCEDURE SPDO”所用时间（总处理时间）:
实际时间 0.85 秒
CPU 时间 0.01 秒

DPF_SIZE is the size of all the dpf files, IDX_SIZE is the index size, MDF_SIZE is the metadata file size.

on spds server host, you can run following code to get a file of the table size list:
spdsls -info -s -verbose D:\test\spds\meta > d:\1.txt

then you can read the data into SAS and summary to get the library size.
[/pre]

BTW. I will have holidays for the sake of Trandition Spring Festival in China.
Maybe I will not appear in this forum for a several days.

And Happy New Year to Peter.C.

Ksharp

Re: sum the size of members for each library in SPDserver environment

LinusH — Thu, 27 Jan 2011 09:47:37 GMT

I didn't suspect that SPDS path information was stored in metadata, since they are not specified when defining a SPDS library. I've done something similar, but with other intentions.

Gather the path information from DICTIONARY.LIBANMES is similar as looking in the libnnames.parm file, but seems simpler, which is nice. 🙂

I had overlooked the spdsls command. But I guess that there would be no problem of looping over all your SPDS librefs to get a total result?

As a final remark I wonder why this information isn't available via some metadata interface, such as the SPDS libanme engine and/or metadata in SPDS. The methods described here I consider as work-around's.

Regards,

Linus

Re: sum the size of members for each library in SPDserver environment

Ksharp — Fri, 28 Jan 2011 02:46:32 GMT

Agree !
Yes. There are need some marcros to hold these which need more code.

Ksharp