>> On Fri, 30 Mar 2007 23:26:39 -0500, David Bronder <[EMAIL PROTECTED]> 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
