Hello Gary, Just guessing the actual reason, it might be that they want to know the amount of TSM storage compared with the amount of storage on the clients. While counting the number of objects doesn't give you much information, maybe it's best to compare the filespaces with occupancy. That is what I call the "abuse factor"
NODE FS FS_GB OCC_GB ABUSE ------------------------------- ------------------------------------------------------------- ----------------- --------------- ----------------- XXXXXXXXXXXXXXXXXXXXXX /csminstall/AIX/images 35.06 149.36 4.2 XXXXXXXXXXXXXXXXXXX /nim/aix54 39.13 39.15 1.0 XXXXXXXXXXXXXXXX /csminstall/AIX/aix610 37.07 36.99 0.9 XXXXXXXXXXXXXXXX /home/dvpt 14.07 35.39 2.5 XXXXXXXXXXXXX /build 24.28 33.24 1.3 XXXXXXXXXXXXXXXX /db2/dvptdb 1.57 30.48 19.3 XXXXXXXXXXXXXXXXXX /home/dvpt 30.57 30.42 0.9 XXXXXXXXXXXXXXXXX /csminstall/AIX/products 25.65 25.77 1.0 XXXXXXXXXXXXXX /build 22.53 21.89 0.9 XXXXXXXXXXXX /build 22.53 21.89 0.9 Copy/paste this in mono-type font (notepad) to get it better readable. I use this to hunt for missing excludes (mssql databases not excluded) but I can also use it to calculate how much space active and inactive I roughly have. Of course filespaces with excludes gives some mismatch. SQL used for above output: select cast(substr(f.NODE_NAME,1,30) as char(30)) as NODE,cast(substr(f.FILESPACE_NAME,1,30) as char(30)) as FS,dec(f.CAPACITY*f.PCT_UTIL/100/1024,14,2) as FS_GB,dec(sum(o.PHYSICAL_MB)/1024,12,2) as OCC_GB,dec(dec(sum(o.PHYSICAL_MB),14,1)/dec(f.CAPACITY*f.PCT_UTIL/100,16,1),14,1) as ABUSE from filespaces as f,occupancy as o where f.NODE_NAME=o.NODE_NAME and f.FILESPACE_NAME=o.FILESPACE_NAME and f.CAPACITY>0 and f.PCT_UTIL>0 and o.STGPOOL_NAME in (select stgpool_name from stgpools where pooltype='PRIMARY') and o.TYPE='Bkup' group by o.NODE_NAME,o.FILESPACE_NAME,f.NODE_NAME,f.FILESPACE_NAME,f.CAPACITY,f.PCT_UTIL order by 4 desc fetch first 10 rows only For all nodes ordered by nodename: select cast(substr(f.NODE_NAME,1,30) as char(30)) as NODE,cast(substr(f.FILESPACE_NAME,1,30) as char(30)) as FS,dec(f.CAPACITY*f.PCT_UTIL/100/1024,14,2) as FS_GB,dec(sum(o.PHYSICAL_MB)/1024,12,2) as OCC_GB,dec(dec(sum(o.PHYSICAL_MB),14,1)/dec(f.CAPACITY*f.PCT_UTIL/100,16,1),14,1) as ABUSE from filespaces as f,occupancy as o where f.NODE_NAME=o.NODE_NAME and f.FILESPACE_NAME=o.FILESPACE_NAME and f.CAPACITY>0 and f.PCT_UTIL>0 and o.STGPOOL_NAME in (select stgpool_name from stgpools where pooltype='PRIMARY') and o.TYPE='Bkup' group by o.NODE_NAME,o.FILESPACE_NAME,f.NODE_NAME,f.FILESPACE_NAME,f.CAPACITY,f.PCT_UTIL order by 1 Regards, Maurice van 't Loo http://mvantloo.nl/maupack.php Personal pack of selects (in scripts) 2016-04-22 16:35 GMT+02:00 Schneider, Jim <jschnei...@essendant.com>: > You can also start the session with -virtualnodename. It works with dsmc > or dsmj, and avoids the need for the proxy setting. > > Jim Schneider > Essendant > > -----Original Message----- > From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of > Skylar Thompson > Sent: Friday, April 22, 2016 9:30 AM > To: ADSM-L@VM.MARIST.EDU > Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA > > You can also GRANT PROXY and then use -ASNODE from one of your own nodes, > using your node's password. I think the general node type has to match > (i.e. any UNIX can proxy to any UNIX, but not Windows). > > On Fri, Apr 22, 2016 at 02:20:38PM +0000, Schneider, Jim wrote: > > Use a server you can access and modify the nodename in the options file, > assuming you know the password. > > > > Jim Schneider > > Essendant > > > > -----Original Message----- > > From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf > > Of Lee, Gary > > Sent: Friday, April 22, 2016 9:11 AM > > To: ADSM-L@VM.MARIST.EDU > > Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA > > > > Wish I could do that. This comes from three levels above me in > management. > > Trying to buy more storage to sell to departments. > > Don't ask me, I have no clue what they are doing. > > > > I'll look into the q backup on client side, but don't have access to all > of them. > > > > > > -----Original Message----- > > From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf > > Of Skylar Thompson > > Sent: Friday, April 22, 2016 10:00 AM > > To: ADSM-L@VM.MARIST.EDU > > Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA > > > > If you have access to the clients, you can use QUERY BACKUP and parse > the A/I column. > > > > Honestly, though, when we've gotten this query, I've managed to push > this back on the customers; it's not TSM's problem what's active or > inactive, it's the customers' applications that are actually responsible > for it. > > Obviously you need a pretty good relationship with your customers to > make that case, but in the end it's caused our customers to think more > carefully about workflow in general. > > > > On Fri, Apr 22, 2016 at 01:51:20PM +0000, Lee, Gary wrote: > > > Just got a request for the amount of active versus inactive data on > our tsm servers. > > > > > > Is there a better way than traversing the backups table and summing? > > > That would be a mighty long query. > > > > > > We have three servers, and approximately 300 clients about 200 tB > total data. > > > > > > Thanks for any suggestions. > > > > -- > > -- Skylar Thompson (skyl...@u.washington.edu) > > -- Genome Sciences Department, System Administrator > > -- Foege Building S046, (206)-685-7354 > > -- University of Washington School of Medicine > > > > ********************************************************************** > > Information contained in this e-mail message and in any attachments > thereto is confidential. If you are not the intended recipient, please > destroy this message, delete any copies held on your systems, notify the > sender immediately, and refrain from using or disclosing all or any part of > its content to any other person. > > -- > -- Skylar Thompson (skyl...@u.washington.edu) > -- Genome Sciences Department, System Administrator > -- Foege Building S046, (206)-685-7354 > -- University of Washington School of Medicine >
