>> On Fri, 30 Mar 2007 23:26:39 -0500, David Bronder <david-bronder AT UIOWA 
>> DOT EDU> said:

> David E Ehresman wrote:
>>
>> I've never quite figured out cross table queries.  Could someone give
>> me a select statement that would list node_name,filespace_name entries
>> that exist in the filespaces table but not in the occupancy table, i.e.
>> filespaces that have no data stored.

> I was beating my head against this one last month when trying to clean
> up empty TDPO filespaces.

> [...]


That looked intriguing, so I did it.  Have at.

I've got a read-only ID defined, query/query, for simple reporting
work, so I do this all on the command line.


# Generate list of "machine-fsid-" for everything in occupancy.
dsmadmc -id=query -password=query -se=ext2 -dataonly=yes  -tab " select 
distinct node_name||'-'||cast(filespace_id as character)||'-' from occupancy "  
> occ

# Generate list of "machine-fsid-" for every filespace we know.
dsmadmc -id=query -password=query -se=ext2 -dataonly=yes  -tab " select 
distinct node_name||'-'||cast(filespace_id as character)||'-' from filespaces " 
> files

# Glom them together
#     sort the glom
#          Count how many times each unique line happens
#                Discard those which happen twice.
cat files occ | sort | uniq -c  | grep -v " 2 "


In my case, it yielded the following list:

   1 BACKUP1.SBAC.EDU-1-
   1 BACKUP1.SBAC.EDU-2-
   1 CISE-RESEARCH10-1-
   1 CISE-RESEARCH30-1-
   1 STEELERS.UAA.UFL.EDU-1-

Nice.


- Allen S. Rout