occupancy/filespace

[influx]

Does anyone know an efficient way to capture auditoccupancy per filespace.

 

[toxy13]

query occupancy?

 

[cozcol]

- Total space on a given client.

select node_name as "Node Name", decimal((physical_mb/1024),20,2) as "Total amount of data in GB" from occupancy

 

[tureem]

Is it possible to sort the list with highest size on top?

-Tur

 

[toxy13]

select node_name as "Node Name", decimal((physical_mb/1024),20,2) as "Total amount of data in GB" from occupancy order by 2 desc

 

[Dave09]

I know this thread is old but I wanted to add to it since its the same topic. So I have upper management wanting to know how much in GB each node is kept per storage pool. I can parse it out by filespace np however they want to just see a "nodename" and a "total GB of all filespaces in stgpool". My question is, How can I add up all the filespaces associated with 1 storagepool instead of having each filespace showing their storage separately...Example...

A basic SQL statement from the occupancy table would look something like this...

Server1 \\server1\c$ Onsite_pri_stgpool 100GB
Server1 \\server1\d$ Onsite_pri_stgpool 100GB
Server1 \\server1\c$ Offsite_copy_stgpool 100GB
Server1 \\server1\d$ Offsite_copy_stgpool 100GB

Id like to take the above info and customize it to look like this....

Server1 Onsite_pri_stgpool 200GB
Server1 Offsite_pri_stgpool 200GB

So I guess add up all the data based on storage pool per node.

Can anyone give me some insight or where to start on developing this custom query? Thank you in advance!

 

[Dave09]

Ok I did more searching and I guess answered my own question. I checked the accuracy as well and it seems spot on. so I came up with this...

>select NODE_NAME, STGPOOL_NAME, sum(PHYSICAL_MB) /1024 as "GB" from OCCUPANCY group by NODE_NAME,STGPOOL_NAME

Now I have been informed that they want to tell what devclass each stgpool_name is in within the same select statement above. I think it would have to be a join statement between the devclass and occupancy tables but im not that good at JOINs in SQL. Hints?

 

[Raakin]

Do you have more than one device class per storage pool?

I understand you might have one device class type to many storage pools but do you not only have one storage pool to one type of device class?

 

[Dave09]

I do not have more than one devclass per storage pool. I do have many storage pools per devclass tho. I do not have a devclass that is assigned to just 1 storage pool either. All the devclasses have multiple storage pools that use the devclass.

 

[TonyB]

Here y'are then:

select -
    char(o.node_name,16) as "Node", -
    char(o.stgpool_name,12) as "Pool", -
    char(s.devclass, 12) as "Devclass", -
    cast(sum(o.physical_mb)/1024 as decimal(8,1)) as "GiB", -
    sum(o.num_files) as "Files" -
from -
    occupancy o -
    join stgpools s on o.stgpool_name = s.stgpool_name -
where -
    o.node_name like upper('$1') -
group by -
    o.node_name, o.stgpool_name, s.devclass

 

[Dave09]

Thank you very much for posting the SQL Tony. However I cant get it to work. For 1 it seems the way you wrote it that it wants a node input for $1. I just wanted it to run once a month on all of the nodes for every TSM server in the environment. So I put it in just like you had it and its giving an error on the JOIN. Im trying to run this on a TSM 5.5 server. Is there a compatibility issue? getting...

ANR2905E Unexpected SQL identifier token - 'JOIN'

and I ran the script as...

run OCC_TEST Server1

even tried...

run OCC_TEST *

with the same result. Any ideas?

 

[TonyB]

Ahh yes - sorry, I just pasted in the contents of one of my scripts from a v6 instance (which uses more advanced SQL semantics)...

You're quite right about the $1 - its used as a script argument... if you want to see all nodes use a "%" as a wildcard. The SQL "like" statement can, under a decent SQL parser, be used multiple times in the same expression - but in v5 TSM its a bit dim, and you can only use it at the start or end of an expression (e.g. "%" works, and gets all nodes... "SPASM%" works and gets all nodes that start with "SPASM", "%_SQL" gets all nodes that end in "_SQL" and "%FRUITY%" doesn't work).

If you don't want to restrict the output to a subset of nodes just remove the "and o.node_name like upper('$1')" from the "where" clause.

Here's the same script from a v5 instance:

select -
    o.node_name, -
    o.stgpool_name, -
    devclass, -
    sum(o.physical_mb)/1024 as "GiB", -
    sum(o.num_files) as "Files" -
from -
    occupancy o, -
    stgpools -
where -
    o.stgpool_name = stgpools.stgpool_name -
    and o.node_name like upper('$1') -
group by -
    o.node_name, o.stgpool_name, devclass

 

[Dave09]

Tony thanks so much for getting that working for me. Works perfectly. I need to now figure out how to make the output horizontal instead of vertical and get it to work in an ODBC scenerio where the output dumps to an excel spreadsheet so still alot to do. You cut alot of time off the clock on this tho. Thanks so much! I may be hittin everyone up soon on what remains. this is all untravelled waters for me.

Thanks again

 

[Raakin]

Horizontal - open dsmadmc with the tab switch. I create a shortcut to dsmcad with this as the target (windows):

"C:\Program Files\Tivoli\TSM\baclient\dsmadmc.exe" -tab

then run your script and it outputs it Horizontal. Very nice for redirecting the output to a file and import it into a spreadsheet for reporting.

ODBC - let me know what you find out there

 

[TonyB]

My pleasure

Haven't used ODBC with a v5 server...but as Raakin has mentioned there are some other output options. For spreadsheet import I generally use -commadelimited instead of -tab and generate CSVs.