Filespace on nodes

[cwilloug]

Is there a way to query the filespace dates on all my nodes? I few months ago I started going through all the nodes, one at a time, to see if there were any old (2008, 2006 etc.. ) filespaces that could be deleted from my nodes to free up some space. Today I was deleting a node and found some filespaces from 2005 (I didn't make it down that far on my list).

Thanks.

[TonyB]

Try this - its formatted as a script with a single parameter ($1) which is the number of days since a backup completed. Note that a partial backup of a filespace doesn't update this timestamp, so the data you get back isn't completely authoritative.

select -
     filespaces.node_name as "Node", -
     contact as "Team", -
     filespace_name as "Filespace", -
     filespace_type as "Type", -
     date(backup_end) as "Last Backup" -
from -
    filespaces, nodes -
where -
    backup_end<current_timestamp-$1 days and -
    filespaces.node_name = nodes.node_name -
order by 2,1,5

I've a v6 version if thats more useful:

select -
    char(n.node_name, 16) as "Node", -
    char(contact, 32) as "Team", -
    char(filespace_name, 16) as "Filespace Name", -
    char(filespace_type, 12) as "Type", -
    date(backup_end) as "Last Backup" -
from -
    filespaces f -
right outer join nodes n on f.node_name=n.node_name -
where -
    (backup_end<current_timestamp-0 days  or backup_end is null) -
order by -
    2,1,5

hth,

Tony

[cwilloug]

Thanks Tony.

[Fattire]

I tried the v5 code on my 5.5..0.3 server and come up with error, "unexpected SQL literal token - '5.'. when i ran it at the command line.
also got this error when I ran it as a script:
ANR1464E RUN: Command script FILESPACE, line 1, parameter 1 was not specified

I'm not sql person so any help would be helpful. would love to get this query to work and add to my tool kit.

[TonyB]

When you invoke the script you need to specify an argument (parameter), e.g.

 run blah 7