Steve, Remco, Thanks for the help. I had to tweak some, but between Friday afternoon and Monday morning, 2 of us came up with some reasonable appearing numbers. They were delivered to management with the caveat that al such counts become obsolete within hours due to Migration and Reclamation. This basic fact made no impression on management, which is, I suppose, why they're management. So such counts will become a regular task, maybe monthly, maybe quarterly.
Fred Johanson TSM Administrator University of Chicago 773-702-8464 -----Original Message----- From: ADSM: Dist Stor Manager [mailto:[email protected]] On Behalf Of Remco Post Sent: Thursday, October 29, 2009 4:37 PM To: [email protected] Subject: Re: [ADSM-L] SQL question On 29 okt 2009, at 22:24, Steven Harris wrote: > Try this > > select count(volume_name), vu.node_name from volume_usage vu, nodes n > where vu.node_name=n.node_name and copy_type='BACKUP' and stgpool_name > != 'OFFSITEPPOOL' and n.domain_name='SYSSERV' > ...group by vu.node_name > Regards > > Steve Harris > Unemployed and Impoverished TSM Admin > Sydney Australia > > > 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') >>> >> >> 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 >> ------------------------------------------------------------------------ >> >> >> No virus found in this incoming message. >> Checked by AVG - www.avg.com >> Version: 8.5.423 / Virus Database: 270.14.38/2467 - Release Date: >> 10/29/09 07:38:00 >> >> -- Met vriendelijke groeten, Remco Post [email protected] +31 6 248 21 622
