On 29 okt 2009, at 22:06, Fred Johanson wrote:

Management would like to know how many tapes are being used by each
domain(=administrative unit).  Logically, I think the query should
look like this:

select count(volume_name),node_name from volumeusage where
copy_type='BACKUP' and not stgpool_name='OFFSITEPOOL' and node_name
in (select node_name from domains where domain_name='SYSSERV')

if you don't collocate along the same lines as your domains, the count
is more or less unreliable. I guess you want something like

select count(*), domain_name from volumeusage, nodes where
volumeusage.node_name = nodes.node_name and  copy_type='BACKUP' and
not stgpool_name='OFFSITEPOOL'  group by domain_name

But that produces this message

ANR2942E The column reference 'NODE_NAME' is not allowed in this
context because it is part of an outer SQL query expression.

Any thoughts?


Fred Johanson
TSM Administrator
University of Chicago

773-702-8464

--
Met vriendelijke groeten,

Remco Post
r.post AT plcs DOT nl
+31 6 248 21 622